OPTIMIZERINFORMATION
The system table SYSINFO.OPTIMIZERINFORMATION
contains information about value distributions that is determined by the UPDATE STATISTICS
statement or read from system table FILES
. The information in
system table OPTIMIZERINFORMATION
is used by the SQL Optimizer.
Structure
|
CHAR(32) |
Name of the schema of the table |
|
CHAR(32) |
Name of the table |
|
CHAR(32) |
Name of a single, non-indexed column |
|
CHAR(32) |
Name of the index |
|
FIXED(10) |
Number of distinct values in column, index, or primary key |
|
FIXED(10) |
Memory requirements of (primary key) index calculated by last UPDATE STATISTICS (KB) |
|
FIXED(5) |
Identification of the partition; NULL for whole table |
For tables and columns the value DISTINCTVALUECOUNT
is determined or updated executing the UPDATE STATISTICS
statement. For indexes the value DISTINCTVALUECOUNT
is read from system table FILES
.
The database system administrator is allowed to get information from the system table OPTIMIZERINFORMATION
for all database users.
More Information
SQL Reference Manual, UPDATE STATISTICS Statement (update_statistics_statement), EXPLAIN Statement (explain_statement)
Concepts of the Database System, SQL Optimizer
Database Administration in CCMS, SQL Optimizer in CCMS, Scheduling Updates of the SQL Optimizer Statistics