| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> How does 9.2i pick a path in rule mode
I am new to Oracle, with DB2 experience.
A table X has more than one index,
index1 being A,B,C
index2 being A,C
other indices
I do a SELECT COUNT(b) FROM x WHERE a = value;
Explain shows that index2 is used and a table scan is performed. And the query takes a long time.
SELECT COUNT(b) FROM x WHERE a = value and b > 0 (all b's are positive numbers)
Explain shows index1 is used and it can get all the information from an index range scan. And the query is relatively quick.
I also tried SELECT COUNT(b) FROM x WHERE (a = value and b > 0) OR
(a = value and b < 1)
but Oracle "optimized" it to the first case.
Why would Oracle choose index2 over index1 for the first form of the query?
Are there some specific commands that examine whether indices are extremely unbalanced? Might that cause it?
I presume cost based indices will help, but that is not an option at the present time.
--
Binyamin Dissen <bdissen_at_dissensoftware.com>
http://www.dissensoftware.com
Director, Dissen Software, Bar & Grill - Israel
 |
 |