ASA SQL User's Guide
Monitoring and Improving Performance
Fragmentation
Indexes are designed to speed up searches on particular columns, but they can become fragmented if many DELETEs are performed on the indexed table. This may result in reduced performance if the index is accessed frequently and the cache is not large enough to hold all of the index.
The sa_index_density stored procedure provides information about the degree of fragmentation in a database's indexes. You must have DBA authority to run this procedure. The following statement calls the sa_index_density stored procedure:
CALL sa_index_density (['table_name'[,'owner_name']])
If your index is highly fragmented, you can run REORGANIZE TABLE. You can also drop the index and recreate it. However, if the index is a primary key, you will also have to drop and recreate the foreign key indexes.
For more information, see REORGANIZE TABLE statement.
For more information about dropping an index, see Dropping indexes.