Index Hints - do they limit the number of indexes used?
Posted on 2004-09-07
I have a query something like this:
from a with (index=a.bid),
where a.bid = b.bid
and b.cid = c.cid
and a.datecol between '1/1/04' and '2/1/04'
a.datecol is indexed
a.bid is indexed
b.bid is indexed
I observe the following behavior:
When run without the hint shown, it uses two indexes from table a: the datecol and then the BID. It appears to decide that the index is a better place to find the IDs it needs for the joins than the table (which is clustered on its unused primary key, aid).
We used a hint on a.BID to get it to go from C to B to A, theoretically bringing better performance. However, in that case it does NOT use the index on the a.datecol.
I am tuning this database and know what I need to do to improve performance. However, I am curious about the hint on a.BID - will that hint PREVENT the query from using the index on a.datecol?
It seems clear that they do, in which case is it possible to hint more than one index per table (without doing rigamaroles with SQL syntax?
(By the way, I do know how to tune this query with a concatenated index (which the prior DBA did not believe in) but that is not the question).