OICA and Oracle choosing the wrong index

  • From: <ryan.gaffuri@xxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 11 May 2004 8:47:04 -0400

This is in reference to a new article by Jonathan Lewis about the OICA:


It states that if you give Oracle a bad OICA it will choose the wrong index. 
Does anyone know why? I have run across this. I have cases where 95% of the 
time Oracle chooses the right index with a given OICA and for say 5% or less of 
queries Oracle chooses the wrong index. 

I've noticed Oracle picking bad join orders depending on how I set there. 
Especially with joins of 3 tables or more. 

Anyone have any comments or experiences? How much does CPU_COSTING eliminate 
this? We are still in development and have only delivered a small quantity of 
our application, so its not prudent to start messing with CPU_COSTING until I 
get a larger user sample. 

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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Other related posts: