OraFAQ Forum: Performance Tuning » Oracle does not use a conditional index

Home » RDBMS Server » Performance Tuning » Oracle does not use a conditional index (oracle 11g)

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

Oracle does not use a conditional index [message #638244]

Mon, 08 June 2015 05:29

iblazquez
Messages: 26
Registered: June 2007

Junior Member

Hello, I have a table with this structure

Name Type
----------- --------------
ID NUMBER(5)
DATE DATE
TEXT VARCHAR2(500)
AUTHOR NUMBER(6)
THEME NUMBER(4)
LATEST VARCHAR2(1)

The field LATEST only can have two values 'S' or 'N'.

There are only a few records with the 'S' so I have created a index for those records

create index TABLE_IDX on TABLE (CASE LATEST WHEN 'S' THEN LATEST ELSE NULL END)

Why oracle does a full scan if I only want the records with the 'S' value

SELECT ID FROM TABLE WHERE LATEST='S'

Report message to a moderator

Re: Oracle does not use a conditional index [message #638247 is a reply to message #638244]

Mon, 08 June 2015 05:49

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Oracle will only use a function based index if the query uses the same function in the same way.

Report message to a moderator

Re: Oracle does not use a conditional index [message #638249 is a reply to message #638247]

Mon, 08 June 2015 05:54

iblazquez
Messages: 26
Registered: June 2007

Junior Member

Thanks

Report message to a moderator

Re: Oracle does not use a conditional index [message #638522 is a reply to message #638249]

Fri, 12 June 2015 16:18

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

You do not understand how indexes work. There is no need to do anything special in your case. What you should do is create the index on the column, and then collect histograms on that column. In this way, the database will learn that S has only a few rows tied to it but N has many. Thus when it sees S it will use the index and when it sees N it will not. If you use a bind variable instead of a constant, then things get way more complicated so I won't talk about that right now.

The field LATEST only can have two values 'S' or 'N'. 

There are only a few records with the 'S'

create index TABLE_IDX on TABLE (LATEST)

SELECT ID FROM TABLE WHERE LATEST='S'  should use the index (if the table is large enough)

SELECT ID FROM TABLE WHERE LATEST='N'  will do a table scan

Read up on collecting histograms.
Additionally also read up on DYNAMIC SAMPLING as if the table is sampled, the CBO will also figure out there are few S and lots of N from the sampling and so still use or not use the index correctly.

Kevin

Report message to a moderator

Re: Oracle does not use a conditional index [message #638525 is a reply to message #638522]

Fri, 12 June 2015 23:59

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

But maybe in this case there are couple of S and million of N.
The purpose of using CASE expression is then to drop down the space used by the index.

I should say in this case only S should exist in the column and NULL should be the "value" for N (unless NULL has already a meaning for the application/model).

Report message to a moderator

Re: Oracle does not use a conditional index [message #639221 is a reply to message #638525]

Fri, 03 July 2015 02:07

mfarooq216
Messages: 9
Registered: May 2006
Location: Karachi (Pakistan)

Junior Member

Null values in a column which involve in idex will mark the index as unusable.

Always the column should be filled with data if part of index.

Regards,

Report message to a moderator

Re: Oracle does not use a conditional index [message #639222 is a reply to message #639221]

Fri, 03 July 2015 02:15

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

mfarooq216 wrote on Fri, 03 July 2015 08:07

Null values in a column which involve in idex will mark the index as unusable.

Always the column should be filled with data if part of index.

Regards,

This isn't the case.

A NULL value in all columns will not be stored in a B-Tree. It WILL be stored in a bitmap index.

Null values do not invalidate the index, they just are not listed, as others have said this can be done for space reasons or performance reasons.

Unless you meant in the query itself, in which case...it wont use a btree because the row isnt there, but a bitmap, or a composite b-tree where not all the columns are null is still a candidate.

Report message to a moderator

Re: Oracle does not use a conditional index [message #639396 is a reply to message #639222]

Mon, 06 July 2015 13:46

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

Senior Member

Topic split to here
http://www.orafaq.com/forum/mv/msg/197822/639224/#msg_639224
it seemed to be getting on to a very different topic.

Report message to a moderator

Previous Topic:	flashback
Next Topic:	Histograms - good or bad? (split from http://www.orafaq.com/forum/mv/msg/197539/638244/#msg_638244)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 13:24:27 CST 2025