Reducing index fragmentation and skew

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.

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' );

TableName

TableId

IndexName

IndexID

IndexType

LeafPages

Density

Skew

Customers

686

CustKey

0

PKEY

1

0.645992

1.002772

Customers

686

IX_cust_name

1

NUI

1

0.789795

1.432239

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.