Re: hash join

llei_at_ros.com wrote in message <7eg13f$ahs$1_at_nnrp1.dejanews.com>...
>We are running choose base optimizer and analyze tables and indexes everyday.
>A sql statement is running very very slow(more than 8 hours). The execution
>plan shows that it uses hash join instead of the indexes to join 2 big
>tables(full table scan on those two tables). After we delete the statistics,
>it use the indexes and it runs only 20 minutes. Does anyone know how to
>improve the hash join?

There are several instance/session parameters which allows tunning hash joins, especially hash_area_size and hash_multiblock_io_count. Refer to Server Reference and Tunning Manual. But maybe using HJ for particular query is not the best execution path. If querey performance was improved after you deleted statistics, using /*+rule */ or /*+use_nl */ hint would eliminate the need of deleting statistics. If you are not able to change the query, the only choice is disabling HJ at the instance level.

--
Piotr Kolodziej pkol_at_otago.gda.pl
Just my private opinion. Received on Thu Apr 08 1999 - 02:19:45 CDT