Any suggestion ?
Buy my latest book. See second URL below.
I'll get back to the private email you sent me in a few days.
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 29th Nov 2005
So, if I've understood properly:
1.With apparently correct row source estimate, the CBO chooses not optimal access path.
2. Setting the OIC to 10, makes it choose the NL access, but not with the "right" driving table.
3. With the hint USE_NL, without ORDERED, it chooses the right driving table. I was trying to trace (event 10053) this execution, even if a hint was present, I was hoping that the reason for choosing the right driving table would appear in the trace file(some optimization has been done, even if there was a hint).
Any suggestion that can help me understand this behaviour and eventually correct it, would be appreciated!
----- Original Message ----- From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
To: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>; "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
Sent: Friday, December 02, 2005 7:57 PM
Subject: Re: CBO - hash join vs nested loops
So, this is the result:
SQL> select count(*) 2 from filtro_dati_catalogo where id_subcatalogo in (0); 3
COUNT(*) ---------- 423077
SQL> select count(*) 2 from opt_vp where codice_opt = 29; 3
COUNT(*) ---------- 1533