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);
@gather t1
@gather t2
---------------------------------------------------------------------------
--테이블 t2에 대하여 full table scan하는 실행계획으로 유도하고 하려 한다면
---------------------------------------------------------------------------
woong:WOONG >
t2 explain plan for
2 select
3 t1.c1, v.c2
4 from
5 t1,
6 (select
7 c1, c2
8 from t2
9 where c1 between :b1 and :b2) v
10 where
11 t1.c1 = v.c1
12 ;
해석되었습니다.
경 과: 00:00:00.00
woong:WOONG >
t2 @plan
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 3949601177
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 0 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 39 | 0 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_N1 | 1 | 13 | 0 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_N1 | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 26 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
4 - access("T1"."C1">=TO_NUMBER(:B1) AND "T1"."C1"<=TO_NUMBER(:B2))
5 - access("T1"."C1"="C1")
filter("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))
21 개의 행이 선택되었습니다.
경 과: 00:00:00.01
---------------------------------------------------------------------------
--해당 sub 쿼리에 힌트를 추가하여 유도할 수 있다.
---------------------------------------------------------------------------
woong:WOONG >
t2 explain plan for
2 select
3 t1.c1, v.c2
4 from
5 t1,
6 (select /*+ full(t2) */
7 c1, c2
8 from t2
9 where c1 between :b1 and :b2) v
10 where
11 t1.c1 = v.c1
12 ;
해석되었습니다.
경 과: 00:00:00.00
woong:WOONG >
t2 @plan
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 3826069298
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_N1 | 1 | 13 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
3 - filter("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))
4 - access("T1"."C1"="C1")
filter("T1"."C1">=TO_NUMBER(:B1) AND "T1"."C1"<=TO_NUMBER(:B2))
19 개의 행이 선택되었습니다.
경 과: 00:00:00.03
------------------------------------------------------------------------------------
--하지만 인라인뷰가 아니라 공용으로 쓰는 view라면 힌트를 고정할 수 없다.
--그래서 아래와 같이 최상위 쿼리블록에서 지정해려 했으나 예상대로 침투되지 않는것을 볼 수 있다.
-----------------------------------------------------------------------------------
woong:WOONG >
t2 explain plan for
2 select /*+ full(t2) */
3 t1.c1, v.c2
4 from
5 t1,
6 (select
7 c1, c2
8 from t2
9 where c1 between :b1 and :b2) v
10 where
11 t1.c1 = v.c1
12 ;
해석되었습니다.
경 과: 00:00:00.00
woong:WOONG >
t2
woong:WOONG >
t2 @plan_all
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3949601177
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 0 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 39 | 0 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_N1 | 1 | 13 | 0 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_N1 | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 26 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
4 - SEL$F5BB74E1 / T1@SEL$1
5 - SEL$F5BB74E1 / T2@SEL$2
6 - SEL$F5BB74E1 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
4 - access("T1"."C1">=TO_NUMBER(:B1) AND "T1"."C1"<=TO_NUMBER(:B2))
5 - access("T1"."C1"="C1")
filter("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T1"."C1"[NUMBER,22], "C2"[NUMBER,22]
2 - (#keys=0) "T1"."C1"[NUMBER,22], "C2"[NUMBER,22]
3 - (#keys=0) "T1"."C1"[NUMBER,22], "T2".ROWID[ROWID,10]
4 - "T1"."C1"[NUMBER,22]
5 - "T2".ROWID[ROWID,10]
6 - "C2"[NUMBER,22]
39 개의 행이 선택되었습니다.
경 과: 00:00:00.03
------------------------------------------------------------------------
dbms_xplan.display format 'ALL'로 plan을 확인하여
Query Block Name / Object Alias (identified by operation id):을 보면
쿼리블록이 'SEL$F5BB74E1' 이고 오브젝트가 't2'인것이 힌트를 적요하고 싶은 대상임을 알 수 있다.
이 정보를 이용하여 global hint를 적용할 수 있다.
------------------------------------------------------------------------
woong:WOONG >
t2 explain plan for
2 select /*+ full(@SEL$F5BB74E1 t2) */
3 t1.c1, v.c2
4 from
5 t1,
6 (select
7 c1, c2
8 from t2
9 where c1 between :b1 and :b2) v
10 where
11 t1.c1 = v.c1
12 ;
해석되었습니다.
경 과: 00:00:00.00
woong:WOONG >
t2
woong:WOONG >
t2 @plan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3826069298
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_N1 | 1 | 13 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
3 - filter("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))
4 - access("T1"."C1"="C1")
filter("T1"."C1">=TO_NUMBER(:B1) AND "T1"."C1"<=TO_NUMBER(:B2))
19 개의 행이 선택되었습니다.
경 과: 00:00:00.03
-----------------------------------------------------------------------------------------
직점 QB_NAME힌트를 적용할 수도 있다.
-----------------------------------------------------------------------------------------
woong:WOONG >
t2 explain plan for
2 select /*+ full(@view1 t2) */
3 t1.c1, v.c2
4 from
5 t1,
6 (select /*+ qb_name(view1) */
7 c1, c2
8 from t2
9 where c1 between :b1 and :b2) v
10 where
11 t1.c1 = v.c1
12 ;
해석되었습니다.
경 과: 00:00:00.00
woong:WOONG >
t2
woong:WOONG >
t2 @plan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3826069298
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_N1 | 1 | 13 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
3 - filter("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))
4 - access("T1"."C1"="C1")
filter("T1"."C1">=TO_NUMBER(:B1) AND "T1"."C1"<=TO_NUMBER(:B2))
19 개의 행이 선택되었습니다.
경 과: 00:00:00.01
-----------------------------------------------------------------------------------------
일반적인 global hint를 적용할 수도 있다.
-----------------------------------------------------------------------------------------
woong:WOONG >
t2 explain plan for
2 select /*+ full(v.t2) */
3 t1.c1, v.c2
4 from t1,
5 (select c1, c2
6 from t2
7 where c1 between :b1 and :b2) v
8 where t1.c1 = v.c1 ;
해석되었습니다.
경 과: 00:00:00.00
woong:WOONG >
t2 @plan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3826069298
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_N1 | 1 | 13 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
3 - filter("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))
4 - access("T1"."C1"="C1")
filter("T1"."C1">=TO_NUMBER(:B1) AND "T1"."C1"<=TO_NUMBER(:B2))
19 개의 행이 선택되었습니다.
경 과: 00:00:00.03