CBO and hash_area_size

I ran a fairly complex self-join query on a ~10e6 table on our 9.2.0.1.0 instance, SunOS 5.8.

It originally executed using a hash join, taking over 2 hours.

One of the things I tried was changing hash_area_size from default (128K) to 24M, and the same query took under a minute.

The execution plans were identical, including the estimated cost.

The statistics are up to date, and the cardinality estimates made by the CBO are fairly close to the actual number rows at each point, as far as I can tell.

So, why doesn't the CBO realize that you can't cram a 20M hash table into 100K, and then account for it by adding the massive multi-pass overhead into the estimated cost?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB