| Index with OR predicates [message #411819] |
Tue, 07 July 2009 00:48  |
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 #412043 is a reply to message #411819] |
Tue, 07 July 2009 09:29  |
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 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
