FreeLists / oracle-l / RE: CBO - hash join vs nested loops

["Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>]

Dimitre

>Checked the CDN and NDV for the tables/columns and they seem OK, 
>but I have to check the indexes stats too.

Even if the object statistics are ok the CBO could do/apply a wrong 
estimation...

>The only think I noticed is that the CBO expects 2076 rows before the final
>distinct and they are 1401.

And what do the following statements return?

select count(*)
from filtro_dati_catalogo
where id_subcatalogo in (0);

select count(*)
from opt_vp 
where codice_opt = 29;

The idea is to know if it's wrong in computing the cardinality for the tables' 
access or for the join. If the problem is at table level, a histogram could be 
useful...


HTH
Chris

PS: even if my mother tongue is Italian, and I live in the Italian part of 
Switzerland, this is the first time I see a schema with Italian names... Where 
does such a schema come from?
--
http://www.freelists.org/webpage/oracle-l