RE: CBO - hash join vs nested loops

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