Oracle Performance and Engineering

In standard Oracle Hash joins the smaller table is 'hashed' in memory (PGA) - if overflows then to TEMP segments - then the larger table(probe table) is read and probed against the hash partitions.

However, in hash outer joins the implementation was not so perfect as above. Till Oracle 10g that is.

Tests performed on Oracle 10.1.0.4.

So, a query like -

SELECT a.ename,b.dname
FROM scott.emp a, scott.dept b
WHERE a.deptno=b.deptno(+);

Shows an explain plan like -

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=14 Bytes=308)
1 0 HASH JOIN (OUTER) (Cost=7 Card=14 Bytes=308)
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=126)
3 1 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=4 Bytes=52)

But -

SELECT /*+ SWAP_JOIN_INPUTs(b) */ a.ename,b.dname
FROM scott.emp a, scott.dept b
WHERE a.deptno=b.deptno(+);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=14 Bytes=308)
1 0 HASH JOIN (RIGHT OUTER) (Cost=7 Card=14 Bytes=308)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=4 Bytes=52)
3 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=126)

Notice the change in the order in which tables are joined.

As Jonathan Lewis shows in this article -

"Now in 10g oracle can switch the driving table for outer joins and semi joins based on the sizes of the row source. The hash table and probe table was fixed in outer join and semi joins till 9i. Now in 10g the hash table and probe table an be switched".

I used little known "swap_join_inputs" hint to change the hash table from EMP to DEPT. Typically, in the production applications I've seen Oracle picking the smaller tables as the hash table (correctly!). For very few cases where Oracle cannot the above hint can possibly used for the smaller table to become the hash table (as it would have happened for an equi join).

I could not, obviously, produce the HASH JOIN RIGHT OUTER with Oracle 9i.

However, with Oracle 9i this hint works to change the order of the table for an equi hash join.

Tests performed with Oracle 9.2.0.6 -

SELECT /*+ use_hash(a) */ a.ename,b.dname
FROM scott.emp a, scott.dept b
WHERE a.deptno=b.deptno;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=409 Bytes=17178)
1 0 HASH JOIN (Cost=5 Card=409 Bytes=17178)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=409 Bytes=8180)
3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=409 Bytes=8998)

Consistent Gets - 15

SELECT /*+ use_hash(a) SWAP_JOIN_INPUTs(b) */ a.ename,b.dname
FROM scott.emp a, scott.dept b
WHERE a.deptno=b.deptno;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=409 Bytes=17178)
1 0 HASH JOIN (Cost=5 Card=409 Bytes=17178)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=409 Bytes=8998)
3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=409 Bytes=8180)

Consistent Gets - 17!