Home » RDBMS Server » Performance Tuning » Oracle does not use a conditional index (oracle 11g)
Oracle does not use a conditional index [message #638244] Mon, 08 June 2015 05:29 Go to next message
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'


Re: Oracle does not use a conditional index [message #638247 is a reply to message #638244] Mon, 08 June 2015 05:49 Go to previous messageGo to next message
cookiemonster
Messages: 12988
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.
Re: Oracle does not use a conditional index [message #638249 is a reply to message #638247] Mon, 08 June 2015 05:54 Go to previous messageGo to next message
iblazquez
Messages: 26
Registered: June 2007
Junior Member
Thanks
Re: Oracle does not use a conditional index [message #638522 is a reply to message #638249] Fri, 12 June 2015 16:18 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
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
Re: Oracle does not use a conditional index [message #638525 is a reply to message #638522] Fri, 12 June 2015 23:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
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).

Re: Oracle does not use a conditional index [message #639221 is a reply to message #638525] Fri, 03 July 2015 02:07 Go to previous messageGo to next message
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,
Re: Oracle does not use a conditional index [message #639222 is a reply to message #639221] Fri, 03 July 2015 02:15 Go to previous messageGo to next message
Roachcoach
Messages: 1508
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.
Re: Oracle does not use a conditional index [message #639396 is a reply to message #639222] Mon, 06 July 2015 13:46 Go to previous message
John Watson
Messages: 7212
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.


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


Current Time: Tue Jan 16 20:14:39 CST 2018

Total time taken to generate the page: 0.02838 seconds