create table t1(c1 int, c2 int);
create table t2(c1 int, c2 int);
create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
exec dbms_stats.gather_table_stats ('HYUN','t1',CASCADE=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE REPEAT');
exec dbms_stats.gather_table_stats ('HYUN','t2',CASCADE=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE REPEAT');
select
t1.c1, v.c2
from
t1,
(select
c1, c2
from t2
where c1 between 1 and 5) v
where
t1.c1 = v.c1
;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 26 | 0 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 39 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 1 | 13 | 0 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_N1 | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
select
t1.c1, v.c2
from
t1,
(select /*+ FULL(T2) */
c1, c2
from t2
where c1 between 1 and 5) v
where
t1.c1 = v.c1
;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 1 | 13 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1">=1 AND "C1"<=5)
3 - access("T1"."C1"="C1")
filter("T1"."C1">=1 AND "T1"."C1"<=5)
select /*+ full(t2) */
t1.c1, v.c2
from
t1,
(select
c1, c2
from t2
where c1 between 1 and 5) v
where
t1.c1 = v.c1
;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 26 | 0 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 39 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 1 | 13 | 0 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_N1 | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1">=1 AND "T1"."C1"<=5)
4 - access("T1"."C1"="C1")
filter("C1">=1 AND "C1"<=5)
힌트가 제대로 반영되지 않은 것을 볼 수 있다.
***************************************************************************************
* GLOBAL힌트를 사용하여 해당 쿼리블럭에 힌트를 적용시킬 수 있음
***************************************************************************************
select /*+ full(v.t2) */
t1.c1, v.c2
from
t1,
(select
c1, c2
from t2
where c1 between 1 and 5) v
where
t1.c1 = v.c1
;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 1 | 13 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1">=1 AND "C1"<=5)
3 - access("T1"."C1"="C1")
filter("T1"."C1">=1 AND "T1"."C1"<=5)
DBMS_XPLAN 패키지를 사용하여 Query Block Name 을 찾는 방법은 생략함
***************************************************************************************
* QB_NAME 힌트로 해당 블럭의 이름을 지정할 수 있음
***************************************************************************************
select /*+ full(@view1 t2) */
t1.c1, v.c2
from
t1,
(select /*+ qb_name(view1) */
c1, c2
from t2
where c1 between 1 and 5) v
where
t1.c1 = v.c1
;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 1 | 13 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1">=1 AND "C1"<=5)
3 - access("T1"."C1"="C1")
filter("T1"."C1">=1 AND "T1"."C1"<=5)
- 강좌 URL : http://www.gurubee.net/lecture/3968
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.