OraFAQ Forum: SQL & PL/SQL » Query not using Index

Home » SQL & PL/SQL » SQL & PL/SQL » Query not using Index (Oracle 10g.)

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

Query not using Index [message #607322]

Tue, 04 February 2014 22:55

gurcharan.singh
Messages: 4
Registered: September 2012

Junior Member

Hi Team,

I have a Table (Table_NM) with two (COL1, COL2) columns and I already created the Index for each column.

My Query is:

SELECT COUNT (*)
FROM Table_NM
WHERE (CASE WHEN '1' = '2' THEN col1 ELSE col2 END) = 'ABC';

Problem statement : Above query not using the index it shown the FULL Table Access in Explain plan.

But if I Modified the query as below , Then it is using the Index.

SELECT COUNT (*)
FROM Table_NM
WHERE (CASE WHEN 1 = 2 THEN col1 ELSE col2 END) = 'ABC';

Why this behaviors I am getting ?

Report message to a moderator

Re: Query not using Index [message #607323 is a reply to message #607322]

Tue, 04 February 2014 22:59

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Hi,

Please post DDL statements to create table and indexes along with insert statements to insert some sample data.

Not sure, what is the purpose of 1=2 in your query. Are col1 and col2 of number type?

Manu

[Updated on: Tue, 04 February 2014 23:01]

Report message to a moderator

Re: Query not using Index [message #607324 is a reply to message #607323]

Tue, 04 February 2014 23:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

The use of a function on any column precludes index on that column from being used.

Report message to a moderator

Re: Query not using Index [message #607325 is a reply to message #607324]

Tue, 04 February 2014 23:12

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

I Agree BlackSwan

But in below case:

(CASE WHEN 1 = 2 THEN col1 ELSE col2 END)

Why its using index then?

Manu

Report message to a moderator

Re: Query not using Index [message #607326 is a reply to message #607325]

Tue, 04 February 2014 23:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

implicit datatype conversion requires a function like TO_NUMBER('1')

Report message to a moderator

Re: Query not using Index [message #607328 is a reply to message #607326]

Tue, 04 February 2014 23:23

gurcharan.singh
Messages: 4
Registered: September 2012

Junior Member

Thanks BlackSwan

What can I do in the below case ?

(CASE WHEN 'A' = 'B' THEN col1 ELSE col2 END)

[Updated on: Tue, 04 February 2014 23:31]

Report message to a moderator

Re: Query not using Index [message #607330 is a reply to message #607328]

Tue, 04 February 2014 23:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

I do not understand what question the posted SQL is supposed to answer; since WHEN 'A' = 'B' will ALWAYS be false!

Report message to a moderator