Hints for Optimization Approaches and Goals

ALL_ROWS

SELECT /*+ ALL_ROWS */ * FROM T1 A, T2 B WHERE A.N4 = B.N4 AND A.N4 = 0;

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |  1000K|    26M|    13  (77)| 00:00:01 |
|*  1 |  HASH JOIN                   |      |  1000K|    26M|    13  (77)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |  1000 | 14000 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1N4 |  1000 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2   |  1000 | 14000 |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T2N4 |  1000 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2008  consistent gets
          0  physical reads
          0  redo size
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

-- HASH 조인, 읽은 블록수 작다
-- http://ukja.tistory.com/172
-- http://scidb.tistory.com/entry/NO-Costing-in-CBO

FIRST_ROWS(N)

SELECT /*+ FIRST_ROWS(20) */ * FROM T1 A, T2 B WHERE A.N4 = B.N4 AND A.N4 = 0;

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |    29 |   812 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |      |       |       |            |          |
|   2 |   NESTED LOOPS                |      |    29 |   812 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1   |    20 |   280 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T1N4 |  1000 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | T2N4 |    20 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2   |    20 |   280 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     140341  consistent gets
          0  physical reads
          0  redo size
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

-- NL 조인, 읽은 블록수 크다