Optimizing Oracle Optimizer (2009년)
Forgotten Hints
Index
- 실제 정의된 인덱스 컬럼을 사용하여 가독성 향상
index의 변화
- Syntax
기본적인 사용법
/*+ index(table_name index_name */
추가된 사용법
/*+index(table_name table_name(column) */
/*+ index(table_name) */
Index Test
create table t1(c1 int, c2 int);
create index t1_n1 on t1(c1);
create index t1_n2 on t1(c1, c2);
create index t1_n3 on t1(c2);
gather t1
-----------------------------------------------------------------------------------
기본적으로 사용하던 예
-----------------------------------------------------------------------------------
woong:WOONG >
t1 explain plan for
2 select /*+ index(t1 t1_n1) */
3 *
4 from t1
5 where c1 = 1 and c2 = 1;
해석되었습니다.
경 과: 00:00:00.01
woong:WOONG >
t1 @plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 1420382924
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 26 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"=1)
2 - access("C1"=1)
15 개의 행이 선택되었습니다.
경 과: 00:00:00.01
-----------------------------------------------------------
10g 에서 컬럼을 직접 지정하여 사용하는 예
힌트에 하나의 인덱스 컬럼을 사용할 때 단일컬럼 인덱스를 사용
-----------------------------------------------------------
woong:WOONG >
t1 explain plan for
2 select /*+ index(t1 t1(c1)) */
3 *
4 from t1
5 where c1 = 1 and c2 = 1;
해석되었습니다.
경 과: 00:00:00.00
woong:WOONG >
t1 @plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 1420382924
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 26 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"=1)
2 - access("C1"=1)
15 개의 행이 선택되었습니다.
경 과: 00:00:00.03
-----------------------------------------------------------
10g 에서 컬럼을 직접 지정하여 사용하는 예
힌트에 두개의 인덱스 컬럼을 사용할 때 결합인덱스를 사용
-----------------------------------------------------------
woong:WOONG >
t1 explain plan for
2 select /*+ index(x t1(c1, c2)) */
3 *
4 from t1 x
5 where c1 = 1 and c2 = 1;
해석되었습니다.
경 과: 00:00:00.00
woong:WOONG >
t1 @plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 677322570
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T1_N2 | 1 | 26 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=1 AND "C2"=1)
13 개의 행이 선택되었습니다.
경 과: 00:00:00.01
Index_RS의 추가
- Index Full Scan의 비효율적인 실행계획을 세울 때 Index Range Scan으로 유도할 수 있다.
QB_NAME
- 10g에서 추가
- 복잡한 쿼리의 가독성을 높이고 제어를 쉽게 해준다.
View밖에서 뷰안의 쿼리를 제어하기 위한 방법
- Global Hint 사용
- QBNAME 사용
QB_NAME, GLOBAL HINT Test
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
- CBQT에 의해 쿼리블럭이 사라지는 경우는 어떻게 처리하나?
- Query Block Name에는 보이지 않아도 Object Alias에는 표시되니 문제없다
QB_NAME확인
woong:WOONG >
t2 explain plan for
2 select
3 c1, c2
4 from t1
5 where c1 in (select
6 c1
7 from t2
8 where c1 between :b1 and :b2);
woong:WOONG >
t2 @plan_all
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 14161060
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 0 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS SEMI | | 1 | 39 | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 26 | 0 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_N1 | 1 | | 0 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_N1 | 1 | 13 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$0C6FB14C
3 - SEL$0C6FB14C / T1@SEL$1
4 - SEL$0C6FB14C / T1@SEL$1
5 - SEL$0C6FB14C / T2@SUB
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
4 - access("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))
5 - access("C1"="C1")
filter("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "C1"[NUMBER,22], "C2"[NUMBER,22]
2 - (#keys=0) "C1"[NUMBER,22], "C2"[NUMBER,22]
3 - "C1"[NUMBER,22], "C2"[NUMBER,22]
4 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]
36 개의 행이 선택되었습니다.
경 과: 00:00:00.04
woong:WOONG >
t2
woong:WOONG >
t2 explain plan for
2 select
3 c1, c2
4 from t1
5 where c1 in (select /*+ no_unnest qb_name(sub) */
6 c1
7 from t2
8 where c1 between :b1 and :b2);
해석되었습니다.
경 과: 00:00:00.03
woong:WOONG >
t2
woong:WOONG >
t2 @plan_all
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2638085224
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | INDEX RANGE SCAN| T2_N1 | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
3 - SUB
4 - SUB / T2@SUB
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ QB_NAME ("SUB") NO_UNNEST */ 0 FROM
"T2" "T2" WHERE TO_NUMBER(:B1)<=TO_NUMBER(:B2) AND :B1<=TO_NUMBER(:B2)
AND :B2>=TO_NUMBER(:B1) AND "C1"=:B3 AND "C1">=TO_NUMBER(:B1) AND
"C1"<=TO_NUMBER(:B2)))
3 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2) AND :B1<=TO_NUMBER(:B2)
AND :B2>=TO_NUMBER(:B1))
4 - access("C1"=:B1)
filter("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "C1"[NUMBER,22], "C2"[NUMBER,22]
2 - "C1"[NUMBER,22], "C2"[NUMBER,22]
37 개의 행이 선택되었습니다.
경 과: 00:00:00.04
FIRST_ROWS(K)
- First_Row는 항상 Rule Base로 동작하며 oracle9i는 Deprecated되었다.
- Oracle 10g R1까지는 100% cost base였다
- Oracle 10g R2부터는 Rule Base logic이 포함되어 Cost가 높더라도 Index를 선호하는 방식으로 동작한다.
- 오라클의 업그래이드나 패치 시 고민할 부분이 하나 더 추가된 것이다.
Full Hint Naming Convention
- 공식적인 명칭은 아니며 global hint의 범주에 포함된다.
- 확인방법 : dbms_xplan.display format'OUTLINE'으로 확인
Full Hint Naming Convention
create table t1(c1 int, c2 int);
create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);
insert into t1
select level, level
from dual
connect by level <= 10000;
@gather t1
--------------------------------------------------------------
--USE_CONCAT Hint를 이용해 OR 조건을 Union All처럼 변환하는 것
--c2 = :b1 조건에 대해서는 Full Table Scan을
--c1 = :b1 조건에 대해서는 Index를 경유하고자 한다.
--------------------------------------------------------------
woong:WOONG >
t1 explain plan for
2 select /*+ use_concat */ *
3 from t1
4 where c1 = :b1 or c2 = :b2;
해석되었습니다.
경 과: 00:00:00.01
woong:WOONG >
t1 @plan
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 82564388
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N2 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C2"=TO_NUMBER(:B2))
4 - filter(LNNVL("C2"=TO_NUMBER(:B2)))
5 - access("C1"=TO_NUMBER(:B1))
19 개의 행이 선택되었습니다.
경 과: 00:00:00.01
woong:WOONG >
t1 explain plan for
2 select /*+ use_concat full(t1) */ *
3 from t1
4 where c1 = :b1 or c2 = :b2;
해석되었습니다.
경 과: 00:00:00.00
-------------------------------------------------------------------------
outline에서 어떻게 내부적인 힌트가 어떻게 사용되었는지 확인한다.
-------------------------------------------------------------------------
woong:WOONG >
t1 select * from table(dbms_xplan.display(null,null,'outline'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 130649462
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 14 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 7 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 7 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1_2" "T1"@"SEL$1_2")
FULL(@"SEL$1_1" "T1"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE_LEAF(@"SEL$1_2")
USE_CONCAT(@"SEL$1" 8)
OUTLINE_LEAF(@"SEL$1_1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.1.0.6')
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C2"=TO_NUMBER(:B2))
3 - filter("C1"=TO_NUMBER(:B1) AND LNNVL("C2"=TO_NUMBER(:B2)))
35 개의 행이 선택되었습니다.
경 과: 00:00:00.06
----------------------------------------------------------------------
Outline Data에서 ACCESS hint가 나오는 부분의 Query Block Name / Object Alias부분을 힌트에 적용한다.
----------------------------------------------------------------------
woong:WOONG >
t1 explain plan for
2 select /*+ use_concat
3 FULL(@"SEL$1_1" "T1"@"SEL$1")
4 INDEX(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."C1")) */
5 *
6 from t1
7 where c1 = :b1 or c2 = :b2;
해석되었습니다.
경 과: 00:00:00.00
woong:WOONG >
t1 @plan
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 647657254
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 9 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | TABLE ACCESS FULL | T1 | 1 | 7 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C2"=TO_NUMBER(:B2))
3 - filter(LNNVL("C2"=TO_NUMBER(:B2)))
4 - access("C1"=TO_NUMBER(:B1))
18 개의 행이 선택되었습니다.
경 과: 00:00:00.01
Oracle은 Hint를 무시하지 않는다
- 힌트를 사용할 수 없는 환경이었거나
- index fast full scan test
- Oracle의 구현 상의 한계에 의해
- sub query unnesting 시 ORDERED HINT
- non unique index의 direct insert
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3894
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.