sa_index_density system procedure

Reports information about the amount of fragmentation and skew within 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 that provides an indication of how full each index page is (on average).
Skew NUMERIC(8,6) A number that provides an indication of the level of unbalance in an index. A value of 1 indicates a perfectly balanced index. Larger values indicate a higher degree of skew.
Remarks

Use the sa_index_density system procedure to obtain information about the degree of fragmentation and skew in indexes. For indexes with a high number of leaf pages, higher density values and lower skew values are desirable.

Index density reflects the average fullness of the index pages, as a percentage. A density of 0.7 indicates that index pages are, on average, 70% full with index data. Index skew reflects the typical deviation from the average density. The amount of skew is important to the optimizer when making selectivity estimates.

When the number of leaf pages is low, you do not need to be concerned about density and skew values. Density and skew values become important only when the number of leaf pages are high. When the number of leaf pages is high, a low density value can indicate fragmentation, and a high skew value can indicate that indexes are not well balanced. Both of these can be factors in poor performance. Executing a REORGANIZE TABLE statement addresses both of these issues. See REORGANIZE TABLE statement.

If you do not specify a table when calling this procedure, the information for all indexes on all tables in the database is returned.

You can also use the Application Profiling Wizard to determine whether index density and skew are at acceptable levels. See Application Profiling Wizard.

Permissions

DBA authority required

Side effects

None

See also
Example

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

CALL sa_index_density( );
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1