RE: Help CBO ANOMALY!!! - BUG WITH HASH_AREA_SIZE? :)

Paula

>the issue I had was not that it choose hash join but that setting the
>hash_area_size to a "smaller" size in the session worked better than
>the parameters on the database - I don't understand that at all.

As I already wrote the costing formula of the CBO depends on hash_area_size. If you increase hash_area_size the cost of the hash join decrease. This, be careful, is independent of where you set it! I.e. not because a value is set at session level it's better than a value set at database level...

That said, in your case you have better performance with a nested loop because the CBO performs a wrong estimation. Just compare the estimated cardinalities (1033/1027) and the real number of rows returned by the statement (12). In your case, I guess, the large number of predicates (which eventually contains some correlated data as well...) lead to this behavior.

HTH
Chris

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