ASA SQL User's Guide
Working with Database Objects
Working with 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:
The CREATE TABLE statement
The ALTER TABLE statement
The CREATE INDEX statement
The DECLARE LOCAL TEMPORARY TABLE statement
Several statements work in conjunction with each other to allow you to maintain and restore the clustering effect:
The UNLOAD TABLE statement allows you to unload a table in the order of the index key.
The LOAD TABLE statement inserts rows into the table in the order of the index key.
The INSERT statement attempts to put new rows on the same table page as the one containing adjacent rows as per the primary key order.
The REORGANIZE table statement can restore the clustering by rearranging the rows according to the clustering index. On tables where clustering is not specified, tables are ordered using the primary key.
The Optimizer assumes that the table rows are stored in key order and costs index scans accordingly.