MS SQL Server - Using Multipe indexes on the same table.

Author

Using Multipe indexes on the same table.

fpkeegan@hotmail.com

2006-10-24, 6:29 pm

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.

| | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([ALLOC]) WITH
PREFETCH)
| | |--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]),
RESIDUAL:([Bmk1000]=[Bmk1000]))
| | |--Index Seek(OBJECT:([ALLOC].[IX3_ALLOC]),
SEEK:([ALLOC].[PMTH] >= 'May 1 2006 12:00AM' AND [ALLOC].[PMTH] <=
'May 31 2006 12:00AM'), WHERE:([ALLOC].[AMTH]='May 1 2006 12:00AM'
AND [ALLOC].[T]=1) ORDERED FORWARD)
| | |--Index Seek(OBJECT:([ALLOC].[IX10_ALLOC]),
SEEK:([ALLOC].[T]=1 AND [ALLOC].[AMTH]='May 1 2006 12:00AM'),
WHERE:(If (Convert([ALLOC].[BILL_IND])=1) then

Kalen Delaney

2006-10-24, 6:29 pm

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.

--
HTH
Kalen Delaney, SQL Server MVP

<fpkeegan@hotmail.com> wrote in message
news:1158615294.327730.201730@h48g2000cwc.googlegroups.com...
>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.
>
>
>
>
> | | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([ALLOC]) WITH
> PREFETCH)
> | | |--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]),
> RESIDUAL:([Bmk1000]=[Bmk1000]))
> | | |--Index Seek(OBJECT:([ALLOC].[IX3_ALLOC]),
> SEEK:([ALLOC].[PMTH] >= 'May 1 2006 12:00AM' AND [ALLOC].[PMTH] <=
> 'May 31 2006 12:00AM'), WHERE:([ALLOC].[AMTH]='May 1 2006 12:00AM'
> AND [ALLOC].[T]=1) ORDERED FORWARD)
> | | |--Index Seek(OBJECT:([ALLOC].[IX10_ALLOC]),
> SEEK:([ALLOC].[T]=1 AND [ALLOC].[AMTH]='May 1 2006 12:00AM'),
> WHERE:(If (Convert([ALLOC].[BILL_IND])=1) then
>

Sponsored Links