Re: CBO - hash join vs nested loops



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.


Regards

Jonathan Lewis

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

----- Original Message ----- From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Saturday, December 03, 2005 11:47 AM
Subject: Re: CBO - hash join vs nested loops



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!


Regards, Dimitre


----- Original Message ----- From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
To: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>; "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
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

-- http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l


Other related posts: