Oracle SQL Tuning and CBO Internal Hash Joins

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

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'