Sybase Question:

System 11
In System 11, the binding of the internal ordinal value is alleviated so that instead of using the ordinal index value, the index name can be used instead:

select ... from my_table (index my_first_index)

Sybase 4.x and Sybase System 10

All indexes have an ordinal value assigned to them. For example, the following query will return the ordinal value of all the indexes on my_table:

select name, indid
from sysindexes
where id = object_id("my_table")

Assuming that we wanted to force the usuage of index numbered three:

select ... from my_table(3)

Note: using a value of zero is equivalent to forcing a table scan. Whilst this sounds like a daft thing to do, sometimes a table scan is a better solution than heavy index scanning.

It is essential that all index hints be well documented. This is good DBA practice. It is especially true for Sybase System 10 and below.

One scheme that I have used that works quite well is to implement a table similar to sysdepends in the database that contains the index hints.

create table idxdepends
(
tblname varchar(32) not null -- Table being hinted
,depname varchar(50) not null -- Proc, trigger or app that
-- contains hint.
,idxname varchar(32) not null -- Index being hinted at
--,hintcount int null -- You may want to count the
-- number of hints per proc.
)