How do I force an index to be used in Sybase?
Submitted by: AdministratorSystem 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.
)
Submitted by: Administrator
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.
)
Submitted by: Administrator
Read Online Sybase Job Interview Questions And Answers
Top Sybase Questions
☺ | What is a Coalesce? What is the equivalent of Oracle/Db2 Coalesce Function in Sybase? |
☺ | How to pad with leading zeros an int or smallint in Sybase? |
☺ | Explain about the features of Sybase IQ? |
☺ | How can I execute dynamic SQL with ASE in Sybase? |
☺ | Explain about Power Designer Data modeling software? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |