Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » System Procedures » System procedures

sa_index_density system procedure Next Page

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
Result set
Column nameData typeDescription
TableNameCHAR(128)The name of a table.
TableIdUNSIGNED INTEGERThe table ID.
IndexNameCHAR(128)The name of an index.
IndexIdUNSIGNED 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

LevelsINTEGER The number of levels in the index.
Remarks

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 small number of 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.

Permissions

DBA authority required

Side effects

None

See also
Example

The following example uses the sa_index_levels system procedure to return the number of levels in the Products index.

CALL sa_index_levels( );
TableNameTableIdIndexName...Levels
Products 436Products ... 1
...............