Implications of OR in WHERE Clause

Question

Post reply

Implications of OR in WHERE Clause

SqlServerPass

SSCommitted

Points: 1709
More actions
June 29, 2013 at 9:13 am

#276789

Hi Dears,
I have a problem that my index are not getting used because of using OR in my WHERE clause. like
select * from
Table1 JOIN Table2 ON Table1.C1 = Table2.C2
WHERE
Table1.C2 = abc OR Table2.C3 = xyz
Could you please give me some info or links with respect to this to know more about the implications of OR in WHERE Clause.
Appreciating your helps always.

Viewing 5 posts - 1 through 4 (of 4 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

Hello Deary

you believe you have a problem with your index....but don't provide any details of this index?

sadly, we cannot see what you can see...table definitions, sample data, query plans etc.

without the above it is very difficult to assist.

here is some sample data ...please amend accordingly to your problem..hopefully someone will be able to guide you

SELECT TOP 1000000

ID = IDENTITY(INT, 1, 1),

cType = CHAR(Abs(Checksum(Newid())) % 26 + 65)

+ CHAR(Abs(Checksum(Newid())) % 26 + 65)

INTO Table1

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

CROSS JOIN sys.all_columns ac3

SELECT TOP 1000000

ID = IDENTITY(INT, 1, 1),

cType = CHAR(Abs(Checksum(Newid())) % 26 + 65)

+ CHAR(Abs(Checksum(Newid())) % 26 + 65)

INTO Table2

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

CROSS JOIN sys.all_columns ac3

--== is this the type of query you are running?

SELECT Table1.ID, Table1.cType AS A, Table2.cType AS B

FROM Table1 INNER JOIN Table2

ON Table1.ID = Table2.ID

WHERE (Table1.cType = 'abc') OR (Table2.cType = 'xyz')

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

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

http://sqlinthewild.co.za/index.php/2011/05/03/indexing-for-ors/

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

SqlServerPass SSCommitted Points: 1709 More actions · Answer 3

J Livingston SQL (6/29/2013)
Hello Deary
you believe you have a problem with your index....but don't provide any details of this index?
sadly, we cannot see what you can see...table definitions, sample data, query plans etc.
without the above it is very difficult to assist.
here is some sample data ...please amend accordingly to your problem..hopefully someone will be able to guide you

Livingston, Sorry for the lack of info and Thanks for your advice. I'll do next time as

My problem is what exactly you quoted in your query.

Also, i went through the link that Gail had given. was a good one. thx Gail.

but, that dealt within a single table.

In Multiple table joins, how can i go with.

Meanwhile, UNION worked for me with two seek queries.

is there any other ways

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

Multiple tables is little different from one table.

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