Index Management on Sybase
The indexes created in the database are based on the modeled keys:
| Uniface Key | Sybase index |
|---|---|
| Primary key | Primary key constraint (unique clustered index with mandatory fields). |
| Candidate key | Unique constraint (unique non-clustered index). |
| Index | Non-clustered index (no index is created for indexes which are covered). |
Note: A Sybase key is not the same as a Sybase index. The maximum length of a key includes compound keys.
Index Creation
This happens at table creation time, either ‘on
the fly’ or via the Create Table facility. The SQL statement which does this is create
index.
Index Names
Index names are
TableNameIn where n is
the number of the index. This naming convention also applies to the primary key and the unique
constraints created for the primary and candidate keys. If a table name is too long to allow this
within the 21 character limit, Uniface truncates it accordingly.
Non-Uniface Indexes
The primary key must be on a clustered index when running the Load Definitions utility. Once the table definition has been loaded into Uniface, the definition can be modified so that the primary key is no longer on a clustered index.
Important: The Load Definitions utility is not available in this release of Uniface 10.
Covered Indexes
A covered index has the following properties:
- It contains a subset of the fields occurring in another index.
- It contains fields ordered in exactly the same way as in the original covering index.
- It uses the same sorting as the covering index; that is, if the covering index is descending, the covered index must be descending, and if the covering index is ascending, the covered index must be ascending.
The SYB connector does not create non-unique covered indexes. If a covered index is unique, however, it must be created, as the covering index does not preserve the uniqueness of the covered index.
Clustered vs. Nonclustered Indexes
A Sybase clustered index is an index which physically orders the actual data in the table. Therefore, the clustered index is the fastest index and only one is allowed for each table. A nonclustered index does not order the actual rows of the table.
