Using Multipe indexes on the same table.

Microsoft SQL Server

I am using Microsoft SQL Server 2000 SP4 8.00.2040

I am analyzing a query plan on a large table With 10+ milion rows.
Part of the query plan uses two index seeks on the same table. I have
never seen this type of plan that uses two indexes on the same table
hash matched together.

Is there a way to force this type of behavior with a hint. It would be
very useful in some of my other queries.




PREFETCH)
RESIDUAL:([Bmk1000]=[Bmk1000]))


AND [ALLOC].[T]=1) ORDERED FORWARD)

WHERE:(If (Convert([ALLOC].[BILL_IND])=1) then
Hi

This is not an uncommon plan when you have multiple nonclustered indexes
matching different conditions in your WHERE clause. It's called 'Index
Intersection'. SQL Server will use one index and retrieve the index rows
from one index which contain the bookmark indicating the actual data row,
and then get the index rows and bookmarks from another index. These two
subsets can then be 'joined' together where the bookmarks are the same. You
can see that in your plan: Hash Match(Inner Join,
HASH:([Bmk1000])=([Bmk1000])

You can try to force this behavior by requesting two separate indexes in a
hint:

SELECT <columns>
FROM <table> WITH (INDEX (index_name, index_name) )

However, don't assume index intersection will always be better than using a
single index. Make sure you test it to make sure you actually are getting
good performance.