How does 9.2i pick a path in rule mode
- From: Binyamin Dissen <bdissen@xxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 07 May 2004 12:45:31 +0300
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@xxxxxxxxxxxxxxxxxx>
http://www.dissensoftware.com
Director, Dissen Software, Bar & Grill - Israel
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
