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.