OraFAQ Forum: SQL & PL/SQL » Index is not used when additing one more selection criteria

Home » SQL & PL/SQL » SQL & PL/SQL » Index is not used when additing one more selection criteria (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Index is not used when additing one more selection criteria [message #650937]

Sat, 07 May 2016 10:31

karthik4u
Messages: 36
Registered: September 2006
Location: CHENNAI

Member

Select Command 1:
=================
SELECT column_name FROM table_name WHERE (column1=21033 or column1=21034 or column1=21036)

It is using Index and verified from explain plan

Select Command 2:
=================

SELECT column_name FROM table_name WHERE (column1=21033 or column1=21034 or column1=21036 or column1=21037)

This is using FULL TABLE ACCESS and verified from explain plan

NOTE1: Using same column1 for all selection conditions which is indexed one.
NOTE2: Only difference in SELECT commands is added one more selection condition (ie. or column1=21037)
NOTE3: Tried different combination of values and Index is not used only when selection conditions exceeds more than 3.

Please clarify.

Report message to a moderator

Re: Index is not used when additing one more selection criteria [message #650939 is a reply to message #650937]

Sat, 07 May 2016 10:56

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

THis not correct,Quote:

Only difference in SELECT commands is added one more selection condition

because there are other differences, such as the number of rows the optimizer expects to be returned, and the number of blocks it expects to access. If you generate 100053 traces for the queries you can try to reverse engineer the optimizer's decisions.

Report message to a moderator

Re: Index is not used when additing one more selection criteria [message #650941 is a reply to message #650937]

Sat, 07 May 2016 11:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

The CBO makes it decisions based upon both the actual SQL & the statistics.
It might choose differently if/when the underlying data in the table(s) change.
How will you act differently after you get clarification?

>SELECT column_name FROM table_name WHERE (column1=21033 or column1=21034 or column1=21036 or column1=21037)
if SQL is changed to below CBO picks different PLAN
SELECT column1 FROM table_name WHERE (column1=21033 or column1=21034 or column1=21036 or column1=21037)

Report message to a moderator

Previous Topic:	Preserving Blanks in External Table
Next Topic:	select based on ratio from second table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 10:26:46 CDT 2024