Records and filtering in the data grids

Toad Data Point & Toad Intelligence Central
1

When I examine the output tab while opening the Data tab in the Database Browser, I notice the TDA does a select * from the appropriate table. This brings up a couple of questions:

  1. We have a setting that allows us to limit the records retrieved via a data grid. Why is a select * from the table being done if we limit the records retrieved?

  2. When we filter the records, in the data grid, is there another hit to the database to retrieve all of the records shown in the filter list? One might suspect that we already have the records if we’re executing a select * against the table. Is the list also subject to the limits established in the options for number of records to retrieve?

2

  1. Oracle will execute a “select *” but pause the query when you reach the limit. You can see this in the background processes window. Other providers will issue statements that use the TOP or LIMIT keywords when fetching the data. ODBC has a API call that will limit the number of records retrieved. It all depends on what platform you are connected to.

  2. If you use the filter button provided by the toolbar right above the data grid, the query will re-execute. If you do not want this to happen, you can use the filter buttons that become visible when you hover the mouse over the column header. In Oracle the limit still applies when using the filtering provided by the toolbar. For other providers this may not by the case.

3

There is an option under Database general regarding how many rows to fetch initialing in the data tab. This limit can also be applied to editors if you like. It is executing a select * from table but pauses the retrieval at your selected rows. If you request more rows, then it will reopen and retrieve the rest. This is similar to how Toad for Oracle does this. Only the number of rows are brought back that you requested. The default value is 1000.

When filtering the data in the grid, there are two filters. The list that you get as a drop down list from any column does not re-query the database but truly just applies a filter to the rows in the data grid. If you use the custom filter, which is the funnel icon on the top left of the grid, this will re-query the database. This is a custom filter where you basically define your filter through a where condition.

Debbie

4

I am attracted by this title. Since I am new to this field, I never considered these things. Seems that I need to know more about the data grid records and data grid filter. Thanks to this thread, I think I found my motivation for grasping this well.