Re: hash join

Try looking up the init.ora parameters hash_join_enable, hash_area_size, and hash_multiblock_io_count. You may want to change them, but I would suggest just hinting the problem code unless several other SQL performance problems turn up that you trace to hash joins.

You mention that the tables are big so I doubt that you are doing a full analyze compute on them which leads to the question how big is your sample size, and have you tried recomputing the statistics for the tables involved in the query with a larger sample to see if the plan changes? The default sample size results in absolute disaster on our main production system, but a sample size of 50000 rows works very well.

llei_at_ros.com wrote in article <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?
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

>
Received on Wed Apr 07 1999 - 13:53:41 CDT