Index not being used with OR cluase

Question

Index not being used with OR cluase

mpeters-878100

Right there with Babe

Points: 716
More actions
April 29, 2010 at 11:10 pm

#140057

Hello I have the following table
CREATE TABLE [dbo].[LUM_A_SALFLDG](
[ACCNT_CODE] [FIN_ACNT_CODE] NOT NULL,
[PERIOD] [FIN_PERD_INT] NOT NULL,
[TRANS_DATETIME] [FIN_DATETIME] NOT NULL,
[JRNAL_NO] [FIN_JNL_NUM] NOT NULL,
[JRNAL_LINE] [FIN_JNL_LINE] NOT NULL,
[AMOUNT] [FIN_AMOUNT_BASE] NOT NULL,
[ANAL_T0] [FIN_ANL_T0] NOT NULL,
[ANAL_T1] [FIN_ANL_T1] NOT NULL,
[ANAL_T2] [FIN_ANL_T2] NOT NULL,
[ANAL_T3] [FIN_ANL_T3] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ACCNT_CODE] ASC,
[PERIOD] ASC,
[TRANS_DATETIME] ASC,
[JRNAL_NO] ASC,
[JRNAL_LINE] ASC
) ON [PRIMARY]
) ON [PRIMARY]
with the following indexes
CREATE NONCLUSTERED INDEX [XIE6LUM_A_SALFLDG] ON [dbo].[LUM_A_SALFLDG]
(
ACCNT_CODE,
[ANAL_T0] ASC,
AMOUNTasc
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [XIE7LUM_A_SALFLDG] ON [dbo].[LUM_A_SALFLDG]
(ANAL_T0 asc,
AMOUNT asc,
ACCNT_CODE asc
) ON [PRIMARY]
ALTER TABLE [dbo].[LUM_A_SALFLDG] ADD PRIMARY KEY CLUSTERED
(
[ACCNT_CODE] ASC,
[PERIOD] ASC,
[TRANS_DATETIME] ASC,
[JRNAL_NO] ASC,
[JRNAL_LINE] ASC
) ON [PRIMARY]
when I run the bottom 2 quieres the 1st query completes an index seek while the second completes an index scan. The only difference btween the 2 is that the second has an 'OR' instead of a 'AND'. How can I crate my indexes so that the second query completes a seek instead of a scan and uses the index properly
SELECT A.ACCNT_CODE,ANAL_T0,AMOUNT
FROM LUM_A_SALFLDG A
WHERE A.ACCNT_CODE BETWEEN '1100' AND 'U11001'
and A.ANAL_T0 BETWEEN 'AIL' AND 'VUA'
SELECT A.ACCNT_CODE,ANAL_T0,AMOUNT
FROM LUM_A_SALFLDG A
WHERE A.ACCNT_CODE BETWEEN '1100' AND 'U11001'
or A.ANAL_T0 BETWEEN 'AIL' AND 'VUA'

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post the current execution plan of the query with OR? I suspect it may have to do with covering and number of records as it looks like it should be able to use an index.

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mpeters-878100 Right there with Babe Points: 716 More actions · Answer 2

Hey

I've run

SET STATISTICS PROFILE ON

go

Before my query but the extra grid with the execution plan was not displayed. I have attached a screen dump of the execution plan, I hope this will suffice

Matt