Hints for Join Orders

LEADING

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

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

-- 추천, 조인 순서 만 지정, 방법은 별도 지정 필요 (USE_NL, USE_HASH...)
-- http://ukja.tistory.com/166

ORDERED

SELECT /*+ ORDERED */ *
  FROM T1, T2
 WHERE T1.N4 = T2.N4
   AND T1.N2 = 50;

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

-- 비추, 하지만 LEADING 보다 강함