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

Oracle SQL Tuning and CBO Internal

Hash Joins

 

Another feature requiring the cost-based optimizer is hash joins. Generally, hash joins outperform merge joins and nested loop joins. Because both tables are analyzed and hash_join_enabledis set to true, Oracle would perform a hash join on the following query joining the two tables EMP and DEPT. Using the ALTER SESSION command, you can also enable hash_join_enabled, as well as using the use_hashhint to force a hash join.

 

SQL> SELECT TABLE_NAME,

 2   NUM_ROWS

-- Both Tables Are Analyzed Since NUM_ROWS Is Not Null

 3   FROM   USER_TABLES

 4*  WHERE  TABLE_NAME IN('EMP','DEPT');

 

TABLE_NAME NUM_ROWS

---------- --------

      DEPT        4

       EMP       14

 

SQL> SELECT VALUE

-- Hash Joins Are Encouraged At The Instance Level

 2   FROM   V$PARAMETER

 3   WHERE  NAME = 'hash_join_enabled';

 

VALUE

-----

 TRUE

 

SQL> SET AUTOTRACE TRACEONLY EXPLAIN

SQL> SELECT ENAME, LOC

 2   FROM   EMP, DEPT

 3   WHERE  EMP.DEPTNO = DEPT.DEPTNO;

Execution Plan

------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

    (Cost=3 Card=14 Bytes=224)

1 0 HASH JOIN (Cost=3 Card=14 Bytes=224)

2 1 TABLE ACCESS (FULL) OF 'DEPT'

    (Cost=1 Card=4 Bytes=36)

3 1 TABLE ACCESS (FULL) OF 'EMP'

    (Cost=1 Card=14 Bytes=98)

 

By changing the optimizer_modeto RULE for your session, or using the RULE hint, or having your Oracle DBA change the parameter optimizer_mode = RULE, hash joins are not executed, as the following example illustrates.

 

SQL> ALTER SESSION SET OPTIMIZER_MODE = RULE;

Session altered.

 

SQL> SET AUTOTRACE TRACEONLY EXPLAIN

SQL> SELECT ENAME, LOC

 2   FROM EMP, DEPT

 3*  WHERE EMP.DEPTNO = DEPT.DEPTNO;

 

Execution Plan

---------------------------------------------

0   SELECT STATEMENT Optimizer=RULE

1 0   MERGE JOIN

2 1     SORT (JOIN)

3 2       TABLE ACCESS (FULL) OF 'DEPT'

4 1     SORT (JOIN)

5 4       TABLE ACCESS (FULL) OF 'EMP'

 


The above text is an excerpt from:

Oracle SQL Tuning & CBO Internals
ISBN 0-9745993-3-6

by Kimberly Floss


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 

  
 

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.