Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
  Oracle Tips by Burleson

Hash-Joins

The hash-join has nothing to do with hash clusters or TABLE ACCESS HASH method. A hash-join compares two tables in memory. The first table is full table scanned, and a hashing function is applied to the data in memory. Then the second table is full table scanned and the hashing function is used to compare the values. Matching values are returned to the user. The user usually has nothing to do with this process; it is completely optimizer-controlled. However, it can be used only by the cost-based optimizer. Generally, hash-joins will gain something for you only if you are using parallel query. Typically, the optimizer will use hash-joins for small tables that can be scanned quickly. To use hash-joins, the HASH_JOIN_ENABLED initialization parameter must be set to TRUE.

Several HASH parameters affect how hash-joins are used. These are:

HASH_JOIN_ENABLED. Set to TRUE to use hash-joins

HASH_AREA_SIZE. Large value reduces cost of hash-joins, so they are used more frequently (set to half the square root of the size of the smaller of the two objects, but not less than 1 megabyte). Suggested range is between 8 and 32 megabytes. Defaults to twice SORT_AREA_SIZE.

HASH_MULTIBLOCK_IO_COUNT. Large value reduces cost of hash-joins, so they are used more frequently. Suggested size is 4.


This is an excerpt by Mike Ault’s book “Oracle Administration & Management” .  If you want more current Oracle tips by Mike Ault, check out his new book “Mike Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s Oracle Scripts Download.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter