Re: optimize hash join between 2 large tables

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: Paula_Stankus@xxxxxxxxxxxxxxx
  • Date: Fri, 21 Jan 2005 08:47:57 -0700

Other than jacking up the db_file_multiblock_read_count to as high as 
the OS supports so that the tablescans are done as efficiently as possible.
Secondly, a hash join doesn't do any sorts. It needs hash_area_size - 
which by default = 2*sort_area_size. You could set the hash_area_size 
explicitly for the batch session. What Oracle version? Before 9i there 
also was a hash_multiblock_io parameter, similar to the dfmrc.

Paula_Stankus@xxxxxxxxxxxxxxx wrote:
> Other then use sort_area_size to sort in memory and parallel query what
> can be done to optimize a pull of 2 large tables joined together where
> most of the rows from both tables are selected???
> --
> //


Wolfgang Breitling
Centrex Consulting Corporation

Other related posts: