ASA SQL Reference
System Tables
Maintaining accurate statistics about the physical properties of candidate indexes facilitates the optimizer's cost based decisions about which indexes to use. SYSATTRIBUTE and SYSATTRIBUTENAME were created so that new information about database objects could be added to the system tables without changing the schema. For version 8.0.1 and later, they only contain information about the attribute PCTFREE, which applies to tables.
Each row of SYSATTRIBUTE describes one system object, such as a particular table or index. Rows are added when the attribute is specified; for example, every table with a PCTFREE setting is added to SYSATTRIBUTE. Similarly, a row for index depth appears in SYSATTRIBUTE only when the index depth increases to 2.
Statistics are maintained for all indexes, including those on catalog tables, as each index is updated. The VALIDATE statement verifies that the statistics on the specified index(es) are accurate and generates an error if they are not. This provides accurate statistics to the optimizer at virtually no performance cost. Statistics persist in SYSATTRIBUTE in the form of one row for each statistic for an index.
| object_type | object_id | attribute_id (from SYSATTRIBUTENAME | sub_object_id1 | sub_object_id2 | attribute_value | |
|---|---|---|---|---|---|---|
| PCTFREE | T | table_id | NULL | NULL | percentage of free space left in each table page | |
| Clustered Index | T | table_id | 2 | ubdex)bynber | NULL | NULL |
| Number of distinct key values | I | table_id | 3 | NULL | NULL | number of distinct key values |
| number of leaf pages | I | table_id | 4 | index_number | NULL | number of leaf pages |
| index depth | I | table_id | 5 | index_number | NULL | index depth |
Columns in the SYSATTRIBUTE table exhibit the following characteristics:
| Column name | Column type | Column constraint |
|---|---|---|
| object_type | CHAR(1) | NOT NULL |
| object_id | UNSIGNED INT | NOT NULL |
| attribute_id | UNSIGNED INT | NOT NULL |
| sub_object_id1 | UNSIGNED INT | |
| sub_object_id2 | UNSIGNED INT | |
| attribute_value | LONG VARCHAR |
object_type The type of object the attribute describes. For example, T represents a table, I an index. Object type information can be obtained by executing SELECT * FROM SYSATTRIBUTENAME.
object_id The id of the particular object. Object id information can be obtained from the table_id column after executing SELECT * FROM SYSTABLE.
attribute_id The number representing the attribute that is being described. A descriptive name for each attribute ID is stored in SYSATTRIBUTENAME.
sub_object_id1 Additional information about the attribute, or NULL if there is none.
sub_object_id2 Additional information about the attribute, or NULL if there is none.
attribute_value The value of the attribute.