Re: CBO - hash join vs nested loops

  • From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 3 Dec 2005 12:47:57 +0100

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

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


Other related posts: