A table node represents a table that is referenced in the
SQL statement. The referenced table can be a base table, a global
temporary table, or a view.
An SQL statement can reference the same table multiple
times; each
reference is represented by a table node.
A table node is labeled
with the name of the referenced table and
is, by default, displayed as a rectangle. The correlation name, creator
name, or cardinality of the table can also be displayed on the label.
If the RUNSTATS utility has not collected statistics for the table,
the table node is outlined in red. If the optimizer uses the default
value for the table cardinality, the cardinality is marked as the
default. The default cardinality value is stored in the catalog table
SYSIBM.SYSTABLES as -1; however, the optimizer uses 10 000 as the
default value.
If RUNSTATS has not collected statistics, the
optimizer does not
necessarily use the default values. If you manually update the values
in SYSIBM.SYSTABLES, the optimizer uses these values and not the default
values.
How DB2 accesses
a table
DB2® accesses
the table by using a table space scan (TBSCAN), a single- or multiple-
index access with fetch (FETCH), or a direct fetch (DFETCH). The
DB2 optimizer
determines the access method based on the table statistics that are
listed in the table below. These statistics are listed as attributes
on the Descriptor window for a table node. To provide these statistics,
either use the RUNSTATS utility to collect them or manually update
the catalog table SYSIBM.SYSTABLES. If you do not provide these statistics,
DB2 uses
the default values that are listed in the following table.
Table 1. Table
statistics that the optimizer usesAttribute |
Explanation |
Default
value |
Rows |
Total number of rows |
10 000 |
Pages |
Total number of pages |
501 |
Compressed
Row Percentage |
Percentage of compressed
rows |
0 |