iAnywhere.com - sa_index_density system procedure

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

Reports information about the amount of fragmentation within database indexes.

Syntax

sa_index_density(
[ 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 values: 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
LeafPages	UNSIGNED INTEGER	The number of leaf pages.
Density	NUMERIC(8,6)	A fraction between 0 and 1 providing an indication of how full each index page is (on average).

Remarks

Database administrators can use this procedure to obtain information about the degree of fragmentation in a database's indexes.

The procedure returns a result set containing the table name, the table ID, the index name, the index ID, the index type, the number of leaf pages, and the index's density.

If you do not specify parameters, the information for all tables appears. Otherwise, the procedure examines only the named table.

For indexes with a high number of leaf pages, higher density values are desirable.

Permissions

DBA authority required

Side effects

None

Example

The following example uses the sa_index_density system procedure to return a result set summarizing the amount of fragmentation within database indexes.

CALL sa_index_density( );

TableName	TableId	IndexName	...	Density
Products	436	Products	...	0.012451
...	...	...	...	...