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_at_gmail.com> To: "Christian Antognini" <Christian.Antognini_at_trivadis.com>; "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> 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