Oracle Manual

• FIRST_ROW 힌트는 일종의 Rule Base이며, First_Row(K) 힌트는 100% cost base이다

• Oracle 10g R1까지는 First_Row(K) 힌트는 100% cost base였다
• Oracle 10g R2부터는 Rule Base logic이 추가되었다.
• Cost가 높더라도 Index를 선호하는 방식으로 동작한다.
• First_Row 힌트는 항상 Rule Base로 동작하며, oracle9i는 Deprecated되었다.

create table t1(c1 int, c2 int);

create index t1_n1 on t1(c1);

insert into t1
select MOD(level,100)+1 C1, level c2 from DUAL
CONNECT BY LEVEL <= 10000;

=> FULL Hint를 사용해서 Table Full Scan을 유도
select /*+ full(t1) */ *
  from t1
 where c1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  2600 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |  2600 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1"=1)
   
=> INDEX Hint를 사용해서 Index Range Scan을 유도
select /*+ index(t1) */ *
  from t1
 where c1 = 1;


-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100 |  2600 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   100 |  2600 |     9   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |   100 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=1)
    
=> First_Rows Hint를 사용
select /*+ First_Rows */ *
  from t1
 where c1 = 1;
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100 |  2600 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   100 |  2600 |     9   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |   100 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=1)


=> First_Rows(10) Hint를 사용
select /*+ First_Rows(10) */ *
  from t1
 where c1 = 1;

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100 |  2600 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   100 |  2600 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |       |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=1)




(참고)아무 힌트를 주지 않았을 경우
select  *
  from t1
 where c1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  2600 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |  2600 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------


=> 테스트환경은 10g R2 이며 R1에서는 테스트하지 못함.