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에서는 테스트하지 못함.