Sybase ASE Indexes
Indexes are the most important physical design element in improving database performance:
Indexes help to avoid table scans. A few index pages and data pages can satisfy many queries without requiring reads on hundreds of data pages.
Indexes in ASE:
We can divide ASE indexes in two categories by : i) Physical Order of Data with index key ii) Uniqueness of the index column
Based on the physical order of data, Adaptive Server provides two general types of indexes that can be created at the table or at the partition level:
• Clustered indexes, where the data is physically stored in the order of the keys on the index:
• For all pages-locked tables, rows are stored in key order on pages, and pages are linked in key order.
• For data-only-locked tables, indexes are used to direct the storage of data on rows and pages, but strict key ordering is not maintained.
• Non clustered indexes, where the storage order of data in the table is not related to index keys
Based on index column uniqueness, indexes can be unique and non unique.
So following types of indexes present in ASE with permutation and combination with above two properties:
1. Unique Clustered Indexes
2. Non unique Clustered Indexes
3. Unique Non-clustered Indexes
4. Non unique Non-clustered indexes
|Clustered Index||Non Clustered Indexes|
|Unique||Unique Clustered Index||Unique Non Clustered Index|
|Non – Unique||Non Unique Clustered Index||Non Unique Non Clustered Index|
So, all the indexes come under above 4 types:
In case of more than one index column, we can add prefix composite in above types.
Means Composite indexes are those indexes, which are created on more than on one column. Above four types of index can be composite as well.
In the case of partitions, we can categories above types as local and global indexes.Local indexes get created at partition level and table level index called as Global indexes.
Global indexes with one index tree cover the whole table, or local indexes with multiple index trees, each of which covers one partition of the table.
Function-based indexes are a type of non clustered index which use one or more expressions as the index key.
Check the Sybase Wiki @ sybasewiki.com