Just ... Forcing a Specific Table Join Method

Oracle database, we can use hints to force a specific join method.

USE_NL = NESTED LOOPS join
USE_MERGE = SORT-MERGE join
USE_HASH = HASH join

Before use each of hints with table join, we should know about join method.

On 10g Performance Tuning Tips & Tech book told about a quick view of the primary join type.

Category	NESTED LOOPS join	SORT-MERGE join	HASH join
Hint	USE_NL	USE_MERGE	USE_HASH
Resource Concerns	CPU, disk I/O.	Memory, temporary segments.	Memory, temporary segments.
Features	Efficient with highly selective indexes and restrictive searches. Used to return the first row of a result quickly.	Better than NESTED LOOPS when an index is missing or the search criteria are not very selective. Can work with limited memory.	Better than NESTED LOOPS when an index is missing or the search criteria are not very selective. It is usually faster than a SORT-MERGE.
Drawbacks	Very inefficient when indexes are missing or if the index criteria are not limiting.	Requires a sort on both tables. It is built for best optimal throughput and does not return the first row until all row are found.	Can require a large amount of memory for the hash table to be built. Does not return the first rows quickly. Can be extremely slow if it must do the operation on disk.

I think that's help us determine hints before we will force a specific table join.

Example to use hints to force a specific table join method:

>>> Create Tables

SQL> create table A as select * from dba_objects;

Table created.

SQL> create table B as select * from user_objects;

Table created.

>>> Create Index

SQL> create index A_I01 on A(object_id,object_name);

Index created.

Begin Testing: Use hints to force a specific table join method.

SQL> set autot trace explain

SQL> select /*+ ordered */ * from A , B where A.object_id=B.object_id ;

Execution Plan

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

Plan hash value: 652036164

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

Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time

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

0 SELECT STATEMENT 456 150K 475 (1) 00:00:06

* 1 HASH JOIN 456 150K 6448K 475 (1) 00:00:06

2 TABLE ACCESS FULL A 34930 6037K 154 (1) 00:00:02

3 TABLE ACCESS FULL B 456 72960 3 (0) 00:00:01

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

SQL> select /*+ use_nl (A B) */ * from A , B where A.object_id=B.object_id ;

Execution Plan

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

Plan hash value: 4149619786

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

Id Operation Name Rows Bytes Cost (%CPU) Time

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

0 SELECT STATEMENT 456 150K 916 (1) 00:00:11

1 TABLE ACCESS BY INDEX ROWID A 1 177 2 (0) 00:00:01

2 NESTED LOOPS 456 150K 916 (1) 00:00:11

3 TABLE ACCESS FULL B 456 72960 3 (0) 00:00:01

* 4 INDEX RANGE SCAN A_I01 1 1 (0) 00:00:01

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

SQL> select /*+ use_merge (A B) */ * from A , B where A.object_id=B.object_id ;

Execution Plan

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

Plan hash value: 3028542103

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

Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time

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

0 SELECT STATEMENT 456 150K 1515 (1) 00:00:19

1 MERGE JOIN 456 150K 1515 (1) 00:00:19

2 SORT JOIN 456 72960 4 (25) 00:00:01

3 TABLE ACCESS FULL B 456 72960 3 (0) 00:00:01

* 4 SORT JOIN 34930 6037K 14M 1511 (1) 00:00:19

5 TABLE ACCESS FULL A 34930 6037K 154 (1) 00:00:02

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

SQL> select /*+ use_hash (A B) */ * from A , B where A.object_id=B.object_id ;

Execution Plan

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

Plan hash value: 4090908061

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

Id Operation Name Rows Bytes Cost (%CPU) Time

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

0 SELECT STATEMENT 456 150K 158 (2) 00:00:02

* 1 HASH JOIN 456 150K 158 (2) 00:00:02

2 TABLE ACCESS FULL B 456 72960 3 (0) 00:00:01

3 TABLE ACCESS FULL A 34930 6037K 154 (1) 00:00:02

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

when use table join, we should determine some initialization parameters to join + performance ;)
pga_aggregate_target : improve performance of all sorts.
db_file_multiblock_read_count : good for full table scans.