Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Usage » Designing and Creating Databases » Working with database objects » Working with indexes


Using clustered indexes

Although indexes can dramatically improve the performance of statements that search for a specific range of key values, two rows appearing sequentially in the index do not necessarily appear on the same table page in the database.

You can further improve a large index scan by declaring that the index is clustered. Using a clustered index increases the chance that two rows from adjacent index entries will appear on the same page in the database. This can lead to performance benefits by reducing the number of times a table page needs to be read into the buffer pool.

The existence of an index with a clustering property causes the database server to attempt to store table rows in approximately the same order as they appear in the clustered index. However, while the database server attempts to preserve the key order, clustering is approximate and total clustering is not guaranteed. Consequently, the database server cannot sequentially scan the table and retrieve all of the rows in clustered index key sequence. Ensuring that the rows of the table are returned in sorted order requires an access plan that either accesses the rows through the index, or performs a physical sort.

The optimizer exploits an index with a clustering property by modifying the expected cost of indexed retrieval to take into account the expected physical adjacency of table rows with matching or adjacent index key values.

The amount of clustering for a given table may degrade over time, as more and more rows are inserted or updated. The database server automatically keeps track of the amount of clustering for each clustered index in the ISYSPHYSIDX system table. If the database server detects that the rows in a table have become significantly unclustered, the optimizer will adjust its expected index retrieval costs accordingly.

The decision to make one of the indexes on a table clustered needs to take into account the expected query workload. Consequently, some experimentation is usually required. As a general rule, however, the database server can use a clustered index to improve performance when the following conditions hold for a specified query:

  • Many of the table pages required for answering the query are not already in memory. When the table pages are already in memory, the server does not need to read these pages and such clustering is irrelevant.
  • The query can be answered by performing an index retrieval that is expected to return a non-trivial number of rows. As an example, clustering is usually irrelevant for simple primary key searches.
  • The database server actually needs to read table pages, as opposed to performing an index-only retrieval.
Using SQL statements to create the clustering effect

The clustering property of an index can be added or removed at any time using SQL statements. Any primary key index, foreign key index, UNIQUE constraint index, or secondary index can be declared with the CLUSTERED property. However, you may declare at most one clustered index per table. You can do this using any of the following statements:

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 clustered index key. See UNLOAD statement.
  • The LOAD TABLE statement inserts rows into the table in the order of the clustered index key. See LOAD TABLE statement.
  • The INSERT statement attempts to put new rows on the same table page as the one containing adjacent rows, as per the clustered index key. See INSERT statement.
  • The REORGANIZE TABLE statement restores the clustering of a table by rearranging the rows according to the clustered index. If REORGANIZE TABLE is used with tables where clustering is not specified, the tables are reordered using the primary key. See REORGANIZE TABLE statement.
Create clustered indexes in Sybase Central

You can also create clustered indexes in Sybase Central using the Create Index Wizard, and selecting Create A Clustered Index when prompted. See Creating indexes.

Reorder rows to match a clustered index

To reorder the rows in a table to match a clustered index, use the REORGANIZE TABLE statement. See REORGANIZE TABLE statement.