TBSCAN

TBSCAN

Operator name: TBSCAN

Represents: A table scan (relation scan) that retrieves rows by reading all the required data directly from the data pages.

This type of scan is chosen by the optimizer over an index scan when:

• The range of values scanned occurs frequently (that is, most of the table must be accessed)
• The table is small
• Index clustering is low
• An index does not exist

For more information on table and index scans, see the Administration Guide.

Performance suggestions:

• An index scan is more efficient than a table scan if the table is large, with most of the table's rows not being accessed. To increase the possibility that an index scan will be used by the optimizer for this situation, consider adding indexes on columns for which there are selective predicates.

  For more guidelines about indexes, see Creating appropriate indexes.

• If an index already exists but was not used, check that there are selective predicates on each of its leading columns. If these predicates do exist, next check that the degree of clustering is high for the index. (To see this statistic, open the Table Statistics window for the table beneath the sort, and select its Indexes push button to bring up the the Index Statistics window.)
• Check that the prefetch size of the table space is adequate that is, it is not I/O bound. (To check this, select Statement->Show statistics->Table spaces.)

  For more information, see the section on prefetching data into the buffer pool in the Administration Guide.

• If the statistics are not current, update them using the runstats command.

  The quantile and frequent value statistics provide information on the selectivity of predicates. For example, these statistics would be used to determine when index scans are chosen over table scans. To update these values, use the runstats command on a table with the WITH DISTRIBUTION clause.