How Hash Join works?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How Hash Join works?

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Posts
    257
    I've been reading some Oracle documentation about Hash Join but I am not clear what Oracle is based on when it splits tables into partitions after full table scans. Say, if I have EMP and DEPT table, when Oracle partitions them, is it true a partition for EMP table actually corresponds to a partition for DEPT table, or it splits them simply based on the size?

    Thanks

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    Oracle scans the smaller table(u need to verify this that it does that) , creates a
    hash function based on join condition and places this table in hash_area_size
    then the second is joined to the first retrieving all matching records using the hash function(table).
    when this table does not fit into the hash_area_size , then the hash table is divided into partitions.
    the larger table is also partitioned but is not one to one but based on the match to the join hash function
    the rows which are matched are returned , rest is kept for next hash table partition.

  3. #3
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    How much size do I need to assign to
    hash_area_size (default is 0) and hash_multiblock_io_count (default is 7) ?

    My DB is oracle7.3.3 , analyze table everyday


  4. #4
    Join Date
    Feb 2001
    Posts
    389
    hash_area_size if not set defaults to twice sort_area_size and hash_...io_count
    * db_block_size should not be more than the physical i/o supported by OS.
    There is a saturaton level at beyond which if u raise this value there is not benefit.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width