Filtering and Sorting Schema Browser Data

With cheap disk space, today's databases are big. Likewise, many tables and views are also quite large. Thus great care should be taken when navigating the Schema Browser, for some of the left-hand-side (LHS) tabs (that is, tables, views, and snapshots) have a right-hand-side (RHS) tab for displaying the data as shown in Figure 2.19.

Figure 2.19. Viewing data.

And while these data grids fetch rows 25 at a time, there are user navigation scenarios that can cause problems (e.g. pressing last record button or scrolling the grid to its end). So if you select a million-row table on the LHS, choose its data tab on the RHS, and then scroll the grid to its end, TOAD will have to fetch and display a million rows. That will take some time and also chew up a lot of your PC's memory. And it's quite likely that you don't need all that data, maybe just the first 100 rows or so.

To avoid such costly queries, TOAD offers a Filter/Sort toolbar icon (that is, gray funnel) for the data tab on the RHS. Clicking the Filter/Sort icon opens the Table Sort window as shown in Figure 2.20. Using the Filter tab, you can easily select the restriction criteria upon which to filter the data, which is added to the WHERE clause used to retrieve this data from the database.

Figure 2.20. Filtering data.

Clicking the OK button (with only filtering criteria specified) will cause two things to happen as shown in Figure 2.21. First, those filter settings will be applied to the information displayed for the data grid. In this example, only those rows where the LASTNAME begins with A, B, or C are retrieved. Second, that tab's Filter toolbar icon will change in color to red, which indicates that the filter is now active. It will, of course, change back to gray if you clear the filter.

Figure 2.21. Displaying filtered data.

Of course, you can also specify to sort the data using the Sort tab as shown in Figure 2.22. This results in an ORDER BY clause being added to the SQL command to retrieve the data from the database. So you are in fact asking the Oracle database server to sort the resylt set data before returning it to the TOAD client (as opposed to having the client having to sort the possibly huge result sets).

Figure 2.22. Sorting data.

Clicking the OK button (with sort criteria specified) will cause two things to happen as shown in Figure 2.23. First, those sort settings will be applied to the information displayed for the Data tab's grid. In this example, rows are sorted ascending first by LASTNAME and then by FIRSTNAME. And second, that tab's Filter toolbar icon will display a blue triangle over the funnel. If the funnel is red, both a filter and sort are being applied. If the funnel is gray, just the sort is being done. Clearing the sort will of course remove the blue triangle.

Figure 2.23. Displaying sorted data.