TBSCAN operator

This operator represents table scans.

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
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.
  • 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.
  • Check that the prefetch size of the table space is adequate that is, it is not I/O bound.
  • If the statistics are not current, update them using the RUNSTATS command.

    The quantile and frequent value statistics provide information about 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.