(on) Hints for Join Operations

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_use_nl_hint.gif!|borderStyle=solid}
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)

|

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

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

|

||USE_MERGE||NO_USE_MERGE||
|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/use_merge_hint.gif!|borderStyle=solid}
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 |
--------------------------------------------------------------------------------------

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

|

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 |
-------------------------------------------------------------------------------------

|

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

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_use_hash_hint.gif!|borderStyle=solid}
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 |
--------------------------------------------------------------------------------------

|