Oracle Compatibility Developer’s Guide

When two tables are to be joined, there are three possible plans that may be used to perform the join.

● Nested Loop Join – The right table is scanned once for every row in the left table.

● Merge Sort Join – Each table is sorted on the join attributes before the join starts. The two tables are then scanned in parallel and the matching rows are combined to form the join rows.

● Hash Join – The right table is scanned and its join attributes are loaded into a hash table using its join attributes as hash keys. The left table is then scanned and its join attributes are used as hash keys to locate the matching rows from the right table.

The following table lists the optimizer hints that can be used to influence the planner to use one type of join plan over another.

Hint	Description
USE_HASH(table [...])	Use a hash join with a hash table created from the join attributes of table.
NO_USE_HASH(table [...])	Do not use a hash join created from the join attributes of table.
USE_MERGE(table [...])	Use a merge sort join for table.
NO_USE_MERGE(table [...])	Do not use a merge sort join for table.
USE_NL(table [...])	Use a nested loop join for table.
NO_USE_NL(table [...])	Do not use a nested loop join for table.

In the following example, a join is performed on the branches and accounts tables. The query plan shows that a hash join is used by creating a hash table from the join attribute of the branches table.

EXPLAIN SELECT b.bid, a.aid, abalance FROM branches b, accounts a WHERE b.bid = a.bid;

                                QUERY PLAN
--------------------------------------------------------------------------
 Hash Join  (cost=1.11..20092.70 rows=500488 width=12)
   Hash Cond: (a.bid = b.bid)
   ->  Seq Scan on accounts a  (cost=0.00..13209.88 rows=500488 width=12)
   ->  Hash  (cost=1.05..1.05 rows=5 width=4)
         ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
(5 rows)

By using the USE_HASH(a) hint, the planner is forced to create the hash table from the accounts join attribute instead of from the branches table. Note the use of the alias, a, for the accounts table in the USE_HASH hint.

EXPLAIN SELECT /*+ USE_HASH(a) */ b.bid, a.aid, abalance FROM branches b, accounts a WHERE b.bid = a.bid;

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Hash Join  (cost=21909.98..30011.52 rows=500488 width=12)
   Hash Cond: (b.bid = a.bid)
   ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
   ->  Hash  (cost=13209.88..13209.88 rows=500488 width=12)
         ->  Seq Scan on accounts a  (cost=0.00..13209.88 rows=500488 width=12)
(5 rows)

Next, the NO_USE_HASH(a b) hint forces the planner to use an approach other than hash tables. The result is a nested loop.

EXPLAIN SELECT /*+ NO_USE_HASH(a b) */ b.bid, a.aid, abalance FROM branches b, accounts a WHERE b.bid = a.bid;

                                QUERY PLAN
--------------------------------------------------------------------------
 Nested Loop  (cost=1.05..69515.84 rows=500488 width=12)
   Join Filter: (b.bid = a.bid)
   ->  Seq Scan on accounts a  (cost=0.00..13209.88 rows=500488 width=12)
   ->  Materialize  (cost=1.05..1.11 rows=5 width=4)
         ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
(5 rows)

EXPLAIN SELECT /*+ USE_MERGE(a) */ b.bid, a.aid, abalance FROM branches b, accounts a WHERE b.bid = a.bid;

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Merge Join  (cost=69143.62..76650.97 rows=500488 width=12)
   Merge Cond: (b.bid = a.bid)
   ->  Sort  (cost=1.11..1.12 rows=5 width=4)
         Sort Key: b.bid
         ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
   ->  Sort  (cost=69142.52..70393.74 rows=500488 width=12)
         Sort Key: a.bid
         ->  Seq Scan on accounts a  (cost=0.00..13209.88 rows=500488 width=12)
(8 rows)

In this three-table join example, the planner first performs a hash join on the branches and history tables, then finally performs a nested loop join of the result with the accounts_pkey index of the accounts table.

EXPLAIN SELECT h.mtime, h.delta, b.bid, a.aid FROM history h, branches b, accounts a WHERE h.bid = b.bid AND h.aid = a.aid;

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Nested Loop  (cost=1.11..207.95 rows=26 width=20)
   ->  Hash Join  (cost=1.11..25.40 rows=26 width=20)
         Hash Cond: (h.bid = b.bid)
         ->  Seq Scan on history h  (cost=0.00..20.20 rows=1020 width=20)
         ->  Hash  (cost=1.05..1.05 rows=5 width=4)
               ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
   ->  Index Scan using accounts_pkey on accounts a  (cost=0.00..7.01 rows=1
       width=4)
         Index Cond: (h.aid = a.aid)
(8 rows)

This plan is altered by using hints to force a combination of a merge sort join and a hash join.

EXPLAIN SELECT /*+ USE_MERGE(h b) USE_HASH(a) */ h.mtime, h.delta, b.bid, a.aid FROM history h, branches b, accounts a WHERE h.bid = b.bid AND h.aid = a.aid;

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Merge Join  (cost=23480.11..23485.60 rows=26 width=20)
   Merge Cond: (h.bid = b.bid)
   ->  Sort  (cost=23479.00..23481.55 rows=1020 width=20)
         Sort Key: h.bid
         ->  Hash Join  (cost=21421.98..23428.03 rows=1020 width=20)
               Hash Cond: (h.aid = a.aid)
               ->  Seq Scan on history h  (cost=0.00..20.20 rows=1020
                   width=20)
               ->  Hash  (cost=13209.88..13209.88 rows=500488 width=4)
                     ->  Seq Scan on accounts a  (cost=0.00..13209.88
                         rows=500488 width=4)
   ->  Sort  (cost=1.11..1.12 rows=5 width=4)
         Sort Key: b.bid
         ->  Seq Scan on branches b  (cost=0.00..1.05 rows=5 width=4)
(12 rows)