Home » RDBMS Server » Performance Tuning » Index with OR predicates (Oracle 9.2.0.3)
Index with OR predicates [message #411819] Tue, 07 July 2009 00:48 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I am creating an composite index on emp
say ,
Create index c_indx_1 on  test(a,d,c);



Now my question is if the column a,d,c are appeared in where
with or condition, do i need to create index individually?

Thanks
Re: Index with OR predicates [message #411824 is a reply to message #411819] Tue, 07 July 2009 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you shoud read Performances Tuning sticky and links related to index.

Regards
Michel
Re: Index with OR predicates [message #412043 is a reply to message #411819] Tue, 07 July 2009 09:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your index can be used on queries that specify values for a, a&d, or a&d&c

It can be used on queries that just specify values of d, by the use of an index skip scan.
Re: Index with OR predicates [message #414852 is a reply to message #412043] Thu, 23 July 2009 16:18 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
The OP may also be interested in this:
Case #1

select *
from T
where a = 1
and b = 1
and c = 1
/

vs.
Case #2a

select *
from T
where a = 1
or b = 1
or c = 1
/

vs.
Case #2b

select * from T where a = 1 UNION
select * from T where b = 1 UNION
select * from T where c = 1
/

What is best index strategy for each query?

Well, this is a bit simplified but best strategy might be the following:
Case#1   --  T(a,b,c)
(one composite btree index)

Case#2a  --  Bitmap=>T(a), Bitmap=>T(b), Bitmap=>T(c)
(three bitmap indexes one on each column)

Case#2b  --  T(a), T(b), T(c)
(three btree indexes one on each column)

I said it was a bit simplified, but it does get to the basic point at hand. Different variations of the query (Cases 2a and 2b being symatically equal (they return the same rows)), can be optimized differently using different index sets. How well each index alternative works depends of course on your code, and how you use your data. This is particularly true when considering bitmaps indexes.

I am not suggesting any specific index set for the OP's situation, nor am I promoting one coding sytle over another, only trying to give some ideas from which you can do your own research. I particularly like CASE#2b because it gets poeople talking.

Good luck, Kevin
Re: Index with OR predicates [message #414945 is a reply to message #414852] Fri, 24 July 2009 02:09 Go to previous message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Many thanks ...
Previous Topic: How to avoid Full table scan for Like '%XYZ%'
Next Topic: Funtion Tuning
Goto Forum:
  


Current Time: Sat Dec 03 10:07:19 CST 2016

Total time taken to generate the page: 0.18355 seconds