sa_index_levels system procedure

Assists in performance tuning by reporting the number of levels in an index.

Syntax

sa_index_levels( 
[ tbl_name 
[, owner_name ] ] 
)

Arguments

tbl_name Use this optional CHAR(128) parameter to specify the table name.
owner_name Use this optional CHAR(128) parameter to specify the owner name.

Result set

Column name	Data type	Description
TableName	CHAR(128)	The name of a table.
TableId	UNSIGNED INTEGER	The table ID.
IndexName	CHAR(128)	The name of an index.
IndexId	UNSIGNED INTEGER	The index ID. This column contains one of the following: 0 for primary keys SYSFKEY.foreign_key_id for foreign keys SYSIDX.index_id for all other indexes
IndexType	CHAR(4)	The index type. This column contains one of the following values: PKEY for primary keys FKEY for foreign keys UI for unique indexes UC for unique constraints NUI for non-unique indexes
Levels	INTEGER	The number of levels in the index.

The number of levels in the index tree determines the number of I/O operations needed to access a row using the index. Indexes with a few levels are more efficient than indexes with a large number of levels.

The procedure returns a result set containing the table name, the table ID, the index name, the index ID, the index type, and the number of levels in the index.

If no arguments are supplied, levels are returned for all indexes in the database. If only tbl_name is supplied, levels for all indexes on that table are supplied. If tbl_name is NULL and an owner_name is given, only levels for indexes on tables owned by that user are returned.

CALL sa_index_levels( );

TableName	TableId	IndexName	...	Levels
Products	436	Products	...	1
...	...	...	...	...

Discuss this page in DocCommentXchange.