Hints for Join Operations

USE_NL

SELECT /*+ USE_NL(T1 T2) */ *
  FROM T1, T2
 WHERE T1.N4 = T2.N4
   AND T1.N2 = 10
   AND T2.N2 = 10;

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |    10 |   280 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |      |       |       |            |          |
|   2 |   NESTED LOOPS                |      |    10 |   280 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1   |    10 |   140 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T1N2 |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | T2N2 |    10 |       |     1   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | T2   |     1 |    14 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

NO_USE_NL

SELECT /*+ NO_USE_NL(T1 T2) */ *
  FROM T1, T2
 WHERE T1.N4 = T2.N4
   AND T1.N2 = 10
   AND T2.N2 = 10;

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    10 |   280 |     3  (34)| 00:00:01 |
|*  1 |  HASH JOIN                   |      |    10 |   280 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    10 |   140 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1N2 |    10 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2   |    10 |   140 |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T2N2 |    10 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

-- 조인 순서 별도 지정 필요 (LEADING)

USE_NL_WITH_INDEX

SELECT /*+ USE_NL_WITH_INDEX(T1 T1N2) */ *
  FROM T1, T2
 WHERE T1.N4 = T2.N4
   AND T1.N2 = 0;

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |    17 |   476 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |      |       |       |            |          |
|   2 |   NESTED LOOPS                |      |    17 |   476 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1   |    10 |   140 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T1N2 |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | T2N4 |    11 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2   |    11 |   154 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

SELECT /*+ USE_NL_WITH_INDEX(T1 T1N3) */ *
  FROM T1, T2
 WHERE T1.N4 = T2.N4
   AND T1.N3 = 0;

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |    14 |   392 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |      |       |       |            |          |
|   2 |   NESTED LOOPS                |      |    14 |   392 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1   |    55 |   770 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T1N3 |   100 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | T2N4 |    11 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2   |    11 |   154 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

-- 드라이빙 테이블의 처리주관 인덱스 지정할때 사용

USE_MERGE

SELECT /*+ USE_MERGE(T1 T2) */ *
  FROM T1, T2
 WHERE T1.N4 = T2.N4
   AND T1.N2 = 10
   AND T2.N2 = 10;

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |    10 |   280 |     4  (50)| 00:00:01 |
|   1 |  MERGE JOIN                   |      |    10 |   280 |     4  (50)| 00:00:01 |
|   2 |   SORT JOIN                   |      |    10 |   140 |     2  (50)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1   |    10 |   140 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T1N2 |    10 |       |     1   (0)| 00:00:01 |
|*  5 |   SORT JOIN                   |      |    10 |   140 |     2  (50)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2   |    10 |   140 |     1   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | T2N2 |    10 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

-- 조인 순서 별도 지정 필요

NO_USE_MERGE

SELECT /*+ NO_USE_MERGE(T1 T2) */ *
  FROM T1, T2
 WHERE T1.N4 = T2.N4
   AND T1.N2 = 10
   AND T2.N2 = 10;

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    10 |   280 |     3  (34)| 00:00:01 |
|*  1 |  HASH JOIN                   |      |    10 |   280 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    10 |   140 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1N2 |    10 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2   |    10 |   140 |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T2N2 |    10 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

USE_HASH

SELECT /*+ USE_HASH(T1 T2) */ *
  FROM T1, T2
 WHERE T1.N4 = T2.N4
   AND T1.N2 = 10
   AND T2.N2 = 10;

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    10 |   280 |     3  (34)| 00:00:01 |
|*  1 |  HASH JOIN                   |      |    10 |   280 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    10 |   140 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1N2 |    10 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2   |    10 |   140 |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T2N2 |    10 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

-- 통계가 없는 경우 (인라인뷰)에 한해서 조인 순서 별도 지정 (LEADING)

NO_USE_HASH

SELECT /*+ NO_USE_HASH(T1 T2) */ *
  FROM T1, T2
 WHERE T1.N4 = T2.N4
   AND T1.N2 = 10
   AND T2.N2 = 10;

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |    10 |   280 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |      |       |       |            |          |
|   2 |   NESTED LOOPS                |      |    10 |   280 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1   |    10 |   140 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T1N2 |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | T2N2 |    10 |       |     1   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | T2   |     1 |    14 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------