Indexes are designed to speed up searches on particular columns, but they can become fragmented (less dense) and skewed (unbalanced) if many delete operations are performed on the indexed table.

Index density reflects the average fullness of the index pages. Index skew reflects the typical deviation from the average density. The amount of skew is important to the optimizer when making selectivity estimates.

To determine whether your database contains indexes that contain unacceptable levels of fragmentation or skew, use the Application Profiling Wizard. See Application Profiling Wizard.

You can also use the sa_index_fragmentation system procedure to review levels of index fragmentation and skew. For example, the following statement calls the sa_index_density system procedure to examine indexes on the Customers table.

CALL sa_index_density( 'Customers' );

SQL Anywhere creates indexes on primary keys automatically. Note that these indexes have an IndexID of 0 in the results for the sa_index_density system procedure.

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 is 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 and Using the Fragmentation tab (SQL Anywhere plug-in).

You can also use the Fragmentation tab in the SQL Anywhere plug-in to review levels of index fragmentation on indexes associated with base tables. See Using the Fragmentation tab (SQL Anywhere plug-in).

 See also