Invoking hash joins for faster Oracle SQL
 

Enabling Oracle to perform hash joins

In cases where a very small table is being joined to a large table, the Oracle hash join will often dramatically speed-up the query. Hash joins are far faster than nested loop joins in certain cases, often in cases where your SQL is joining a large table to a small table. 

However, in a production database with very large tables, it is not always easy to get your database to invoke hash joins without increasing the RAM regions that control hash joins.  For large tables, hash joins requires lots of RAM.

The 200 megabyte limit for hash joins

Oracle places limits of the amount of RAM available for hash joins so that no single session will "hog" all of the RAM.  According to this research using Oracle 10g, the hash join maximum is only to only 200 megabytes (5% of pga_aggregate_target).

The Oracle DBA controls the optimizers' propensity to invoke hash joins because the DBA must allocate the RAM resources to Oracle (using the hash_area_size and pga_aggregate_target parameters) for the optimizer to choose a hash join.  The CBO will only choose a hash join if you have allocated Oracle enough RAM area in which to perform the hash join.

Sizing your PGA for hash joins

The rules are quite different depending on your release, and you need to focus on the hash_area_size OR the pga_aggregate_target parameters.

Read more about invoking has joins for faster Oracle SQL here:

http://www.dba-oracle.com/tips_oracle_hash_joins.htm

*****************************************

Need a Health Check?

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf