Contents Index Use indexes for frequently-searched columns Creating indexes

ASA SQL User's Guide
  Working with Database Objects
    Working with indexes

Using clustered indexes


Although standard indexes can dramatically improve the performance of statements that search for a specific row or a specific subset of the rows, two rows appearing sequentially in the index do not necessarily appear on the same page in the database.

However, you can further improve the performance of indexes by creating clustered indexes. Clustered indexes in Adaptive Server Anywhere store the table rows in approximately the same order as they appear in the corresponding index.

Using the clustered index feature increases the chance that the two rows will appear on the same page in the database. This can lead to performance benefits by further reducing the number of times each page needs to be read into memory.

For example, in a case where you select two rows that appear sequentially in a clustered index, it is possible that you are retrieving two rows that appear sequentially on the same page, thus reducing the number of pages to read into memory by half.

The clustering of indexes in Adaptive Server Anywhere is approximate. While the server attempts to preserve the key order, total clustering is not guaranteed. As well, the clustering degrades over time, as more and more rows are inserted into your database.

You can implement one clustered index per table, using the following statements:

Several statements work in conjunction with each other to allow you to maintain and restore the clustering effect:

The Optimizer assumes that the table rows are stored in key order and costs index scans accordingly.


Contents Index Use indexes for frequently-searched columns Creating indexes