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_at_gmail.com> To: <oracle-l_at_freelists.org> 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_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
>
>

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