옵티마이저는 주어진 SQL을 최적화하기 위해 Query를 재 구성하는데 이 때 Rule Base 기반으로 하는
RBQT(Rule Base Query Transformation)와 CBQT(Cost Base Query Transformation)이 있다.
9i까지는 이 RBQT를 사용였고 10g에 넘어와서는 CBQT를 사용하고 있으며 이에 대한 파라미터는
'_optimizer_cost_based_transformation'(Value : on, off)가 있다.
Query Transformation Life Cycle
Query Transformation에 대한 내부 구현코드의 생명주기(Life Cycle)을 보면 일반적으로 아래와 같은
패턴을 가지고 있다.
| Close Beta | 구현된 내부 코드가 이미 존재하고, 파라미터 또는 비공식 힌트를 사용하여 Query Transformation이 일어나게끔 할 수 있음. |
| Open Beta | 내부 코드가 기본값으로 활성화되지만 Cost-Based는 아님. 따라서 항상 변환이 일어남. |
| Release | 옵티마이저가 원래 SQL과 변환된 SQL의 양쪽 비용 모두를 계산해서 더 비용이 낮은 쪽을 선택함. 힌트는 deprecatted됨. |
아래에서 오라클 버전 별 실행계획을 비교하여 어떤 차이가 있는지 살펴보자.
-- 총 20,000명의 사원이 여섯 개 부서로 나뉘도록 테이블을 생성함.
-- 각 사원은 서로 다른 식별자(EMP_NO)를 가지며, 급여(SALARY)도 서로 중복되지 않는다.
CREATE TABLE EMP(DEPT_NO NOT NULL,
SAL,
EMP_NO NOT NULL,
PADDING,
CONSTRAINT E_PK PRIMARY KEY(EMP_NO))
AS
WITH GENERATOR AS (SELECT ROWNUM ID
FROM ALL_OBJECTS
WHERE ROWNUM <= 1000)
SELECT /*+ ORDERED USE_NL(V2) */
MOD(ROWNUM, 6),
ROWNUM,
ROWNUM,
RPAD('X', 60)
FROM GENERATOR V1,
GENERATOR V2
WHERE ROWNUM <= 20000
;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'EMP',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
| 8i version | 9i version | 10g version |
|---|---|---|
| {code:SQL} ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '8.1.7'; |
EXPLAIN PLAN FOR
SELECT OUTER.*
FROM EMP OUTER
WHERE OUTER.SAL > (SELECT /*+ NO_UNNEST */
AVG(INNER.SAL)
FROM EMP INNER
WHERE INNER.DEPT_NO = OUTER.DEPT_NO);
@XPLAN3;
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | 1000 | 72000 | 35 | |
| FILTER | ||||
| 2 | TABLE ACCESS FULL | EMP | 1000 | 72000 | 35 |
| 3 | SORT AGGREGATE | 1 | 8 | ||
| TABLE ACCESS FULL | EMP | 3333 | 26664 | 35 |
Outline Data
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$2" "INNER"@"SEL$2")
FULL(@"SEL$1" "OUTER"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPTIMIZER_FEATURES_ENABLE('8.1.7')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
1 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */
AVG("INNER"."SAL")
FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
4 - filter("INNER"."DEPT_NO"=:B1)
Note
EXPLAIN PLAN FOR
SELECT OUTER.*
FROM EMP OUTER
WHERE OUTER.SAL > (SELECT /*+ NO_UNNEST */
AVG(INNER.SAL)
FROM EMP INNER
WHERE INNER.DEPT_NO = OUTER.DEPT_NO);
@XPLAN3;
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | 1000 | 72000 | 72 | |
| FILTER | ||||
| 2 | TABLE ACCESS FULL | EMP | 1000 | 72000 | 36 |
| 3 | SORT AGGREGATE | 1 | 8 | ||
| TABLE ACCESS FULL | EMP | 3333 | 26664 | 36 |
Outline Data
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$2" "INNER"@"SEL$2")
FULL(@"SEL$1" "OUTER"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPTIMIZER_FEATURES_ENABLE('9.2.0.8')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
1 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */
AVG("INNER"."SAL")
FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
4 - filter("INNER"."DEPT_NO"=:B1)
Note
EXPLAIN PLAN FOR
SELECT OUTER.*
FROM EMP OUTER
WHERE OUTER.SAL > (SELECT /*+ NO_UNNEST */
AVG(INNER.SAL)
FROM EMP INNER
WHERE INNER.DEPT_NO = OUTER.DEPT_NO);
@XPLAN3;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 167 | 12024 | 452 (1) | 00:00:06 | |
| FILTER | |||||
| 2 | TABLE ACCESS FULL | EMP | 20000 | 1406K | 65 (2) | 00:00:01 |
| 3 | SORT AGGREGATE | 1 | 8 | |||
| TABLE ACCESS FULL | EMP | 3333 | 26664 | 65 (2) | 00:00:01 |
Outline Data
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$2" "INNER"@"SEL$2")
FULL(@"SEL$1" "OUTER"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
ALL_ROWS
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
1 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */
AVG("INNER"."SAL")
FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1))
4 - filter("INNER"."DEPT_NO"=:B1)
|
1) 8i, 9i에서 Driving Table인 EMP의 Cardinality 값을 1,000으로 계산했는데 그 이유는?
- 그 이유는 아직 서브쿼리가 결과가 알려지지 않은 상태이기 때문에 'salary > :bind_variable'로
계산을 하여 5%의 룰을 적용하였고 이로 인해 '20000 / 20 = 1000'의 값을 리턴하였다.
2) 그렇다면 10g에서의 Driving Table인 EMP의 Cardinality 값이 20,000인 이유는?
- 우선 COST 값을 보면 최종 결과에서 452가 나왔고 EMP 테이블을 Driving할 때는 65값이 나왔는데
이를 나누어 보면 '452 / 65 = 6.9(약 7)'의 결과가 나오는 것을 알 수 있다. 이는 맨 처음
Driving Table을 읽은 것을 제외하면 6번을 읽었다는 얘기인데 EMP 테이블의 부서 숫자와 거의
일치한다. 즉 10g 부터는 서브쿼리의 내용을 바인드 변수로 처리하는게 아니라 서브쿼리의 결과
값을 IN-Memory 참조 테이블에 캐싱하는 메커니즘을 사용할 것을 알고 단지 여섯 번만 서브쿼리를
수행하는 보다 현실적인 로직으로 처리하는 것을 알 수 있다.
추가적으로 서브쿼리의 Cardinality 값인 3,333은 EMP 테이블 전체 로우수 '20,000 / 6'인 값과 같다.
3) 마지막으로 8i, 9i에서 최종 예측 Cardinality 값이 1,000인데 비해 10g가 167인 이유는?
- 8i, 9i는 Base Cardinality에 5%의 룰을 단순히 적용하였지만 10g는 5%의 룰에 서브쿼리의 개수를
나누어 개산하였기 때문이다. '20000 * 0.05 / 6 = 166.6(약 167)'
h1. II. 필터링
h2. 1. 개요
옵티마이저가 발전하면서 오라클은 다양한 변환기법들을 사용해서 서브쿼리를 제거하는 쪽으로
많은 진화가 이루어졌기 때문에 필터 연산응 좀처럼 사용되지 않는 추세이지만 이 Operation이
정확히 어떤 작업을 하는것인지는 알아야 하므로 아래에서 이를 확인해보고자 한다.
h3. 1) 테스트 테이블 생성
{code:SQL}
CREATE TABLE PARENT(ID1 NUMBER NOT NULL,
SMALL_VC1 VARCHAR2(10),
SMALL_VC2 VARCHAR2(10),
PADDING VARCHAR2(200),
CONSTRAINT PAR_PK PRIMARY KEY(ID1));
CREATE TABLE CHILD(ID1 NUMBER NOT NULL,
ID2 NUMBER NOT NULL,
SMALL_VC1 VARCHAR2(10),
SMALL_VC2 VARCHAR2(10),
PADDING VARCHAR2(200),
CONSTRAINT CHI_PK PRIMARY KEY (ID1,ID2));
CREATE TABLE SUBTEST (ID1 NUMBER NOT NULL,
SMALL_VC1 VARCHAR2(10),
SMALL_VC2 VARCHAR2(10),
PADDING VARCHAR2(200),
CONSTRAINT SUB_PK PRIMARY KEY(ID1));
INSERT INTO PARENT
SELECT ROWNUM,
TO_CHAR(ROWNUM),
TO_CHAR(ROWNUM),
RPAD(TO_CHAR(ROWNUM), 100)
FROM ALL_OBJECTS
WHERE ROWNUM <= 3000;
COMMIT;
BEGIN
FOR I IN 1..8 LOOP
INSERT INTO CHILD
SELECT ROWNUM,
I,
TO_CHAR(ROWNUM),
TO_CHAR(ROWNUM),
RPAD(TO_CHAR(ROWNUM), 100)
FROM PARENT;
COMMIT;
END LOOP;
END;
/
INSERT INTO SUBTEST
SELECT * FROM PARENT;
COMMIT;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,
'PARENT',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,
'CHILD',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,
'SUBTEST',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/
-- 부모-자식 관계를 갖는 두 테이블을 조인하는 쿼리문
-- 각 부모 레코드가 관련된 여덟 개의 자식 레코드를 갖도록 데이터를 구성함
-- 부모 테이블이 가진 값을 근거로 데이터 일부를 제거할 목적으로 서브쿼리를 사용
-- 액세스 순서가 PAR -> SUB -> CHI와 PAR -> CHI -> SUB일 경우 어떤 차이가 나는지 체크
-- 8i version. subquery postponed
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '8.1.7';
ALTER SESSION SET STATISTICS_LEVEL = ALL ;
SELECT PAR.SMALL_VC1,
CHI.SMALL_VC1
FROM PARENT PAR,
CHILD CHI
WHERE CHI.ID1 = PAR.ID1
AND PAR.ID1 BETWEEN 100
AND 200
AND EXISTS (SELECT NULL
FROM SUBTEST SUB
WHERE SUB.SMALL_VC1 = PAR.SMALL_VC1
AND SUB.ID1 = PAR.ID1
AND SUB.SMALL_VC2 >= '2');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('40t7w69g69dj7', NULL, 'ALLSTATS COST LAST'));
----------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------
|* 1 | FILTER | | 8 |00:00:00.01 | 1224 |
| 2 | TABLE ACCESS BY INDEX ROWID | CHILD | 808 |00:00:00.01 | 921 |
| 3 | NESTED LOOPS | | 910 |00:00:00.01 | 113 |
| 4 | TABLE ACCESS BY INDEX ROWID| PARENT | 101 |00:00:00.01 | 6 |
|* 5 | INDEX RANGE SCAN | PAR_PK | 101 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | CHI_PK | 808 |00:00:00.01 | 107 |
|* 7 | TABLE ACCESS BY INDEX ROWID | SUBTEST | 1 |00:00:00.01 | 303 |
|* 8 | INDEX UNIQUE SCAN | SUB_PK | 101 |00:00:00.01 | 202 |
----------------------------------------------------------------------------------
-- 8i version. early subquery
-- PUSH_SUBQ, NO_UNNEST 힌트를 사용해도 PAR -> SUB -> CHI 액세스 순서로 유도가
-- 안되서 ROWNUM 방법을 사용함
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '8.1.7';
ALTER SESSION SET STATISTICS_LEVEL = ALL ;
SELECT TEMP.SMALL_VC1,
CHI.SMALL_VC1
FROM (SELECT /*+ PUSH_SUBQ */
PAR.SMALL_VC1,
PAR.ID1,
ROWNUM
FROM PARENT PAR
WHERE PAR.ID1 BETWEEN 100
AND 200
AND EXISTS (SELECT /*+ NO_UNNEST */
NULL
FROM SUBTEST SUB
WHERE SUB.SMALL_VC1 = PAR.SMALL_VC1
AND SUB.ID1 = PAR.ID1
AND SUB.SMALL_VC2 >= '2')) TEMP,
CHILD CHI
WHERE TEMP.ID1 = CHI.ID1
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('8qd5asmzzzytm', NULL, 'ALLSTATS COST LAST'));
------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID | CHILD | 8 |00:00:00.01 | 320 |
| 2 | NESTED LOOPS | | 10 |00:00:00.02 | 312 |
| 3 | VIEW | | 1 |00:00:00.01 | 309 |
| 4 | COUNT | | 1 |00:00:00.01 | 309 |
|* 5 | FILTER | | 1 |00:00:00.01 | 309 |
| 6 | TABLE ACCESS BY INDEX ROWID| PARENT | 101 |00:00:00.01 | 6 |
|* 7 | INDEX RANGE SCAN | PAR_PK | 101 |00:00:00.01 | 3 |
|* 8 | TABLE ACCESS BY INDEX ROWID| SUBTEST | 1 |00:00:00.01 | 303 |
|* 9 | INDEX UNIQUE SCAN | SUB_PK | 101 |00:00:00.01 | 202 |
|* 10 | INDEX RANGE SCAN | CHI_PK | 8 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------
;
'subquery postponed'에서는 'Buffers' 값이 1,224이나 'early subquery'에서는 320으로 줄어든것을 확인할 수 있음.
-- 10g version. subquery postponed
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.3';
ALTER SESSION SET STATISTICS_LEVEL = ALL ;
SELECT /*+ LEADING(PAR CHI) USE_NL(PAR CHI) */
PAR.SMALL_VC1,
CHI.SMALL_VC1
FROM PARENT PAR,
CHILD CHI
WHERE CHI.ID1 = PAR.ID1
AND PAR.ID1 BETWEEN 100
AND 200
AND EXISTS (SELECT /*+ NO_UNNEST */
NULL
FROM SUBTEST SUB
WHERE SUB.SMALL_VC1 = PAR.SMALL_VC1
AND SUB.ID1 = PAR.ID1
AND SUB.SMALL_VC2 >= '2');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1h7kqmbwjcf2s', NULL, 'ALLSTATS COST LAST'));
----------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------
|* 1 | FILTER | | 8 |00:00:00.01 | 1224 |
| 2 | TABLE ACCESS BY INDEX ROWID | CHILD | 808 |00:00:00.01 | 921 |
| 3 | NESTED LOOPS | | 910 |00:00:00.01 | 113 |
| 4 | TABLE ACCESS BY INDEX ROWID| PARENT | 101 |00:00:00.01 | 6 |
|* 5 | INDEX RANGE SCAN | PAR_PK | 101 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | CHI_PK | 808 |00:00:00.01 | 107 |
|* 7 | TABLE ACCESS BY INDEX ROWID | SUBTEST | 1 |00:00:00.01 | 303 |
|* 8 | INDEX UNIQUE SCAN | SUB_PK | 101 |00:00:00.01 | 202 |
----------------------------------------------------------------------------------
-- 10g version. early subquery
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.3';
ALTER SESSION SET STATISTICS_LEVEL = ALL ;
SELECT /*+ LEADING(PAR) */
PAR.SMALL_VC1,
CHI.SMALL_VC1
FROM PARENT PAR,
CHILD CHI
WHERE CHI.ID1 = PAR.ID1
AND PAR.ID1 BETWEEN 100
AND 200
AND EXISTS (SELECT /*+ USE_NL(SUB) */
NULL
FROM SUBTEST SUB
WHERE SUB.SMALL_VC1 = PAR.SMALL_VC1
AND SUB.ID1 = PAR.ID1
AND SUB.SMALL_VC2 >= '2');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('4wv1jvg8nra6v', NULL, 'ALLSTATS COST LAST'));
----------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID | CHILD | 8 |00:00:00.01 | 221 |
| 2 | NESTED LOOPS | | 10 |00:00:00.01 | 213 |
| 3 | NESTED LOOPS SEMI | | 1 |00:00:00.01 | 210 |
| 4 | TABLE ACCESS BY INDEX ROWID| PARENT | 101 |00:00:00.01 | 6 |
|* 5 | INDEX RANGE SCAN | PAR_PK | 101 |00:00:00.01 | 3 |
|* 6 | TABLE ACCESS BY INDEX ROWID| SUBTEST | 1 |00:00:00.01 | 204 |
|* 7 | INDEX UNIQUE SCAN | SUB_PK | 101 |00:00:00.01 | 103 |
|* 8 | INDEX RANGE SCAN | CHI_PK | 8 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------
;
서브쿼리를 NO_UNNEST 힌트처럼 Filter가 아닌 UNNEST의 조인 형태로 처리함.
스칼라 서브쿼리를 사용할때도 FILTER 실행계획이 나올 수 있다.
DROP TABLE EMP;
DROP TABLE GENERATOR;
CREATE TABLE GENERATOR AS
SELECT ROWNUM ID
FROM ALL_OBJECTS
WHERE ROWNUM <= 1000;
CREATE TABLE EMP(DEPT_NO NOT NULL,
SAL,
EMP_NO NOT NULL,
PADDING,
CONSTRAINT E_PK PRIMARY KEY(EMP_NO)
)
AS
SELECT /*+ ORDERED USE_NL(V2) */
MOD(ROWNUM, 6),
ROWNUM,
ROWNUM,
RPAD('X', 60)
FROM GENERATOR V1,
GENERATOR V2
WHERE ROWNUM <= 20000
;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'EMP',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
EXPLAIN PLAN FOR
SELECT COUNT(AV_SAL)
FROM (SELECT /*+ NO_MERGE */
OUTER.DEPT_NO,
OUTER.SAL,
OUTER.EMP_NO,
OUTER.PADDING,
(SELECT AVG(INNER.SAL)
FROM EMP
INNER WHERE INNER.DEPT_NO = OUTER.DEPT_NO) AV_SAL
FROM EMP OUTER)
WHERE SAL > AV_SAL
;
@XPLAN;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 129 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | VIEW | | 20000 | 507K| 129 (1)| 00:00:02 |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS FULL | EMP | 20000 | 156K| 65 (2)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 8 | | |
|* 6 | TABLE ACCESS FULL| EMP | 3333 | 26664 | 65 (2)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OUTER"."SAL"> (SELECT AVG("INNER"."SAL") FROM "EMP"
"INNER" WHERE "INNER"."DEPT_NO"=:B1))
6 - filter("INNER"."DEPT_NO"=:B1)
실행계획을 보면 인라인 뷰 안의 스칼라 서브쿼리 AV_SAL 컬럼을 인라인 뷰 밖에서 조건으로
사용을 하고 있기 때문에 실행계획에서 FILTER 부분에서 확인을 할 수 있다.
만약 스칼라 서브쿼리 부분이 조건에 활용되지 않는다면 이 부분은 실행계획에서 확인하기 힘들것이다.
EXPLAIN PLAN FOR
SELECT COUNT(AV_SAL)
FROM (SELECT /*+ NO_MERGE */
OUTER.DEPT_NO,
OUTER.SAL,
OUTER.EMP_NO,
OUTER.PADDING,
(SELECT AVG(INNER.SAL)
FROM EMP
INNER WHERE INNER.DEPT_NO = OUTER.DEPT_NO) AV_SAL
FROM EMP OUTER)
--WHERE SAL > AV_SAL
;
@XPLAN;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 65 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 20000 | 253K| 65 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 20000 | 60000 | 65 (2)| 00:00:01 |
----------------------------------------------------------------------------
이렇게 스칼라 서브쿼리가 실행계획에서 보여야 함에도 불구하고 안보이는 이유는 스칼라 서브쿼리 집합
밖에서 그룹함수를 사용하였기 때문이다. 일반적으로 View Depth가 깊거나 View 밖에서 그룹함수를 사용할경우
View 안의 스칼라 서브쿼리는 가려서 안보이게 된다.
-- 인라인 뷰 안의 내용만 확인할 경우
EXPLAIN PLAN FOR
SELECT /*+ NO_MERGE */
OUTER.DEPT_NO,
OUTER.SAL,
OUTER.EMP_NO,
OUTER.PADDING,
(SELECT AVG(INNER.SAL)
FROM EMP
INNER WHERE INNER.DEPT_NO = OUTER.DEPT_NO) AV_SAL
FROM EMP OUTER
;
@XPLAN;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 1406K| 65 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| EMP | 3333 | 26664 | 65 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 20000 | 1406K| 65 (2)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("INNER"."DEPT_NO"=:B1)
서브쿼리 팩토링이란 9i부터 제공된 구문으로 WITH절을 사용하여 SQL을 사용할 때 집합을 미리 만드는 방법을 일컷는다.
WITH 절을 쉽게 이해한다면 인라인 뷰의 내용을 WITH절에 선언하고 SELECT 절에서는 테이블처럼 사용한다고 보면 된다.
이 방법에는 2가지 종류가 있는데 다음과 같다.
DROP TABLE T1;
CREATE TABLE T1
AS
SELECT *
FROM ALL_OBJECTS
WHERE ROWNUM <= 3000
;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'T1',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
EXPLAIN PLAN FOR
WITH GENERATOR AS
(
SELECT /*+ INLINE */
ROWNUM ID
FROM T1
WHERE ROWNUM <= 1000
)
SELECT /*+ ORDERED USE_NL(V2) */
MOD(ROWNUM, 6),
ROWNUM,
ROWNUM,
RPAD('X', 60)
FROM GENERATOR V1,
GENERATOR V2
WHERE ROWNUM <= 20000
;
@XPLAN;
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 262 (1)| 00:00:04 |
|* 1 | COUNT STOPKEY | | | | |
| 2 | NESTED LOOPS | | 20000 | 262 (1)| 00:00:04 |
| 3 | VIEW | | 20 | 1 (0)| 00:00:01 |
|* 4 | COUNT STOPKEY | | | | |
| 5 | TABLE ACCESS FULL| T1 | 3000 | 13 (0)| 00:00:01 |
| 6 | VIEW | | 1000 | 13 (0)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | |
| 8 | TABLE ACCESS FULL| T1 | 3000 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20000)
4 - filter(ROWNUM<=1000)
7 - filter(ROWNUM<=1000)
DROP TABLE T1;
CREATE TABLE T1
AS
SELECT *
FROM ALL_OBJECTS
WHERE ROWNUM <= 3000
;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'T1',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
EXPLAIN PLAN FOR
WITH GENERATOR AS
(
SELECT /*+ MATERIALIZE */
ROWNUM ID
FROM T1
WHERE ROWNUM <= 1000
)
SELECT /*+ ORDERED USE_NL(V2) */
MOD(ROWNUM, 6),
ROWNUM,
ROWNUM,
RPAD('X', 60)
FROM GENERATOR V1,
GENERATOR V2
WHERE ROWNUM <= 20000
;
@XPLAN;
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | | 2030 (1)| 00:00:25 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | TABLE ACCESS FULL | T1 | 3000 | | 13 (0)| 00:00:01 |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | NESTED LOOPS | | 1000K| | 2017 (1)| 00:00:25 |
| 7 | VIEW | | 1000 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6602_EC10A807 | 1000 | 13000 | 2 (0)| 00:00:01 |
| 9 | VIEW | | 1000 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6602_EC10A807 | 1000 | 13000 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=1000)
5 - filter(ROWNUM<=20000)
그룹 함수가 포함된 뷰 또는 인라인 뷰와 그룹 함수가 없는 뷰 또는 인라인 뷰 각각 1개가 조인할 때
이를 Complex View Merging이라고 한다.
만약 이 조인이 될 경우 아래의 2 경우를 생각해 볼 수 있다.
1) 그룹함수가 있는 집합을 먼저 생성하고 그룹함수가 없는 집합과 조인을 함.
2) 그룹함수가 있는 집합 안의 내용과 그룹함수가 없는 집합을 먼저 조인하게 한 후
그 뒤에 그룹함수를 사용하는 방법
DROP TABLE T;
CREATE TABLE T AS
SELECT MOD(LEVEL, 10) GUBN,
LEVEL VAL
FROM DUAL
CONNECT BY LEVEL <= 1000;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'T',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
EXPLAIN PLAN FOR
SELECT *
FROM (SELECT GUBN, SUM(VAL)
FROM T
GROUP BY GUBN) T_1,
T T_2
WHERE T_1.GUBN = T_2.GUBN
;
@XPLAN;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 32000 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 32000 | 8 (25)| 00:00:01 |
| 2 | VIEW | | 10 | 260 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 10 | 60 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T | 1000 | 6000 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T | 1000 | 6000 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T_1"."GUBN"="T_2"."GUBN")
실행계획에서 보는 것처럼 인라인 뷰 T_1을 먼저 액세스한 뒤 'HASH GROUP BY'를 통해
먼저 집합을 만들과 그 뒤에 T_2 테이블을 HASH JOIN 한 것을 볼 수 있다.
EXPLAIN PLAN FOR
SELECT /*+ MERGE(T_1) */
*
FROM (SELECT GUBN, SUM(VAL)
FROM T
GROUP BY GUBN) T_1,
T T_2
WHERE T_1.GUBN = T_2.GUBN
;
@XPLAN;
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 585K| | 323 (4)| 00:00:04 |
| 1 | HASH GROUP BY | | 50000 | 585K| 3552K| 323 (4)| 00:00:04 |
|* 2 | HASH JOIN | | 100K| 1171K| | 8 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T | 1000 | 6000 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T | 1000 | 6000 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GUBN"="T_2"."GUBN")
이번에는 MERGE 힌트를 사용하여 2개 테이블을 먼저 HASH JOIN하게 한 뒤 그 다음 HASH GROUP BY가
된 것을 확인할 수 있다.
일반적으로 2개 이상의 집합이 조인될 때 1개까지는 그룹함수가 있는 집합이 있을 경우
'Complex View Merging'이 가능하지만 그룹함수가 2개 이상일 경우는 'Complex View Merging'이
안되는 경우가 대부분이다. 그 이유는 옵티마이저가 2개 이상인 집합에 대해 먼저 조인을 하고
이후에 그룹함수를 사용할 경우 데이터를 보존할 가능성이 희박하다고 보기 때문에 먼저 집합을
보존하고자 'Complex View Merging'을 대부분 하지 않는다.
EXPLAIN PLAN FOR
SELECT /*+ MERGE(T_1) MERGE(T_2) */
*
FROM (SELECT GUBN, SUM(VAL)
FROM T
GROUP BY GUBN) T_1,
(SELECT GUBN, SUM(VAL)
FROM T
GROUP BY GUBN) T_2,
T T_3
WHERE T_1.GUBN = T_2.GUBN
AND T_1.GUBN = T_3.GUBN
;
@XPLAN;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 58000 | 12 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 58000 | 12 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 10 | 520 | 9 (34)| 00:00:01 |
| 3 | VIEW | | 10 | 260 | 4 (25)| 00:00:01 |
| 4 | HASH GROUP BY | | 10 | 60 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T | 1000 | 6000 | 3 (0)| 00:00:01 |
| 6 | VIEW | | 10 | 260 | 4 (25)| 00:00:01 |
| 7 | HASH GROUP BY | | 10 | 60 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL| T | 1000 | 6000 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T | 1000 | 6000 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T_1"."GUBN"="T_3"."GUBN")
2 - access("T_1"."GUBN"="T_2"."GUBN")
결국 이를 해결하기 위해서는 가급적 그룹함수가 있는 집합이 1개만 유지되도록 해야 한다는 것을 의미하며
2개 이상일 경우 다른 그룹함수 집합을 스칼라 서브쿼리나 펑션으로 대체해야 한다.
이 Operation은 그룹함수가 없는 뷰 또는 인라인 뷰와 다른 집합 사이에 조인을 하게 될 ? 다른 집합에서
뷰 또는 인라인 뷰로 조건이 침투가 되도록 하는 방법이다. 만약 당신이 특정 SQL을 튜닝하게 된다면
이 Operation을 유도하느냐 못하느냐에 따라 수행속도가 아주 많이 차이나는 것을 확인할 수 있을 것이다.
DROP TABLE T1;
DROP TABLE T2;
DROP TABLE T3;
CREATE TABLE T1 AS
SELECT ROWNUM - 1 ID1,
TRUNC((ROWNUM - 1) / 10) N1,
LPAD(ROWNUM, 10, '0') SMALL_VC,
RPAD('X', 100) PADDING
FROM ALL_OBJECTS
WHERE ROWNUM <= 5000;
ALTER TABLE T1 ADD CONSTRAINT T1_PK PRIMARY KEY(ID1);
CREATE TABLE T2 AS
SELECT TRUNC((ROWNUM - 1) / 5) ID1,
ROWNUM ID2,
LPAD(ROWNUM, 10, '0') SMALL_VC,
RPAD('X', 100) PADDING
FROM ALL_OBJECTS
WHERE ROWNUM <= 25000;
ALTER TABLE T2 ADD CONSTRAINT T2_PK PRIMARY KEY(ID1, ID2);
CREATE TABLE T3 AS
SELECT TRUNC((ROWNUM - 1) / 5) ID1,
ROWNUM ID2,
LPAD(ROWNUM, 10, '0') SMALL_VC,
RPAD('X', 100) PADDING
FROM ALL_OBJECTS
WHERE ROWNUM <= 25000;
ALTER TABLE T3 ADD CONSTRAINT T3_PK PRIMARY KEY(ID1, ID2);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME =>'T1',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME =>'T2',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME =>'T3',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/
CREATE OR REPLACE VIEW V1 AS
SELECT T2.ID1,
T2.ID2,
T3.SMALL_VC,
T3.PADDING
FROM T2,
T3
WHERE T3.ID1 = T2.ID1
AND T3.ID2 = T2.ID2
;
ALTER SESSION SET STATISTICS_LEVEL = ALL ;
SELECT /*+ NO_PUSH_PRED(V1) */
T1.*,
V1.*
FROM T1,
V1
WHERE V1.ID1(+) = T1.ID1
AND T1.N1 = 5
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('2hswhprr2pp84', NULL, 'ALLSTATS LAST'));
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN OUTER | | 1 | 50 | 50 |00:00:00.30 | 25545 | 752K| 752K| 1083K (0)|
|* 2 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 |00:00:00.01 | 91 | | | |
| 3 | VIEW | V1 | 1 | 25000 | 25000 |00:00:00.25 | 25454 | | | |
| 4 | NESTED LOOPS | | 1 | 25000 | 25000 |00:00:00.23 | 25454 | | | |
| 5 | TABLE ACCESS FULL| T3 | 1 | 25000 | 25000 |00:00:00.03 | 448 | | | |
|* 6 | INDEX UNIQUE SCAN| T2_PK | 25000 | 1 | 25000 |00:00:00.15 | 25006 | | | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("V1"."ID1"="T1"."ID1")
/*+ NO_PUSH_PRED(V1) */ 힌트를 사용하여 V1 뷰에 조건이 침투가 안되도록 유도하였다.
위에서 보는 것처럼 조건이 침투가 안되서 T3 테이블을 25000건 액세스 한것을 확인할 수 있다.
SELECT T1.*,
V1.*
FROM T1,
V1
WHERE V1.ID1(+) = T1.ID1
AND T1.N1 = 5
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gh71wnftc6m85', NULL, 'ALLSTATS LAST'));
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS OUTER | | 1 | 50 | 50 |00:00:00.01 | 182 |
|* 2 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 |00:00:00.01 | 95 |
| 3 | VIEW PUSHED PREDICATE | V1 | 10 | 1 | 50 |00:00:00.01 | 87 |
| 4 | NESTED LOOPS | | 10 | 1 | 50 |00:00:00.01 | 87 |
| 5 | TABLE ACCESS BY INDEX ROWID| T3 | 10 | 5 | 50 |00:00:00.01 | 32 |
|* 6 | INDEX RANGE SCAN | T3_PK | 10 | 5 | 50 |00:00:00.01 | 18 |
|* 7 | INDEX UNIQUE SCAN | T2_PK | 50 | 1 | 50 |00:00:00.01 | 55 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N1"=5)
6 - access("T3"."ID1"="T1"."ID1")
7 - access("T2"."ID1"="T1"."ID1" AND "T3"."ID2"="T2"."ID2")
filter("T3"."ID1"="T2"."ID1")
이번에는 힌트를 제거하여 V1 뷰에 조건이 침투가 되도록 유도하였고 이로 인해 T3 테이블을 50건만
액세스하는 것을 확인할 수 있다.
1) 서브쿼리(IN, EXISTS, ANY, ...)는 Driving Operation(공급자)과 Filter Operation(확인자)으로 나눌 수 있다.
2) 서브쿼리를 Driving과 Filter로 사용하는 기준은 어느쪽이 작은 로우의 집합인지 파악
-- 1. oracle version
SELECT *
FROM V$VERSION;
BANNER
-----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
-- 2. create table & index & statistics
DROP TABLE TAB_A PURGE;
DROP TABLE TAB_B PURGE;
CREATE TABLE TAB_A AS
SELECT LEVEL CNT,
MOD(LEVEL, 10) GUBN
FROM DUAL
CONNECT BY LEVEL <= 100
;
CREATE TABLE TAB_B AS
SELECT LEVEL CNT,
MOD(LEVEL, 10) GUBN
FROM DUAL
CONNECT BY LEVEL <= 100
;
CREATE UNIQUE INDEX TAB_A_U1 ON TAB_A (CNT) COMPUTE STATISTICS;
CREATE INDEX TAB_A_N1 ON TAB_A (GUBN) COMPUTE STATISTICS;
CREATE UNIQUE INDEX TAB_B_U1 ON TAB_B (CNT) COMPUTE STATISTICS;
CREATE INDEX TAB_B_N1 ON TAB_B (GUBN) COMPUTE STATISTICS;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TAB_A', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TAB_B', CASCADE => TRUE);
-- 3-1) Main절에 Unique 조건을 주어 Filter로 푸는 경우를 살펴봄
-- 보는 것처럼 Main절을 1건 읽은 뒤 서브쿼리를 한건만 읽어서 최적으로 액세스 함
SELECT A.*
FROM TAB_A A
WHERE A.CNT = 1
AND EXISTS (SELECT B.GUBN
FROM TAB_B B
WHERE B.GUBN = A.GUBN)
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 3 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.003 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 NESTED LOOPS SEMI (cr=3 pr=0 pw=0 time=109 us)
1 TABLE ACCESS BY INDEX ROWID TAB_A (cr=2 pr=0 pw=0 time=75 us)
1 INDEX UNIQUE SCAN TAB_A_U1 (cr=1 pr=0 pw=0 time=41 us)(Object ID 10438011)
1 INDEX RANGE SCAN TAB_B_N1 (cr=1 pr=0 pw=0 time=32 us)(Object ID 10438014)
-- 3-2) Main절에 Unique 조건을 주어 Filter가 더 ?음에도 불구하고 Driving으로 사용할 경우
-- 서브쿼리를 강제로 Driving하기 위해 QB_NAME 힌트를 사용
-- 보는 것처럼 서브쿼리를 Driving하기 때문에 불필요하게 100건을 읽은 뒤 Main절에서 Filter를 하고 있음
SELECT /*+ LEADING(B@SUB) */
A.*
FROM TAB_A A
WHERE A.CNT = 1
AND A.GUBN IN (SELECT /*+ QB_NAME(SUB) */
B.GUBN
FROM TAB_B B
WHERE B.GUBN = A.GUBN)
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.003 0 3 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.004 0 3 0 1
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 HASH JOIN (cr=3 pr=0 pw=0 time=3362 us)
10 SORT UNIQUE (cr=1 pr=0 pw=0 time=218 us)
100 INDEX FULL SCAN TAB_B_N1 (cr=1 pr=0 pw=0 time=150 us)(Object ID 10438014)
1 TABLE ACCESS BY INDEX ROWID TAB_A (cr=2 pr=0 pw=0 time=68 us)
1 INDEX UNIQUE SCAN TAB_A_U1 (cr=1 pr=0 pw=0 time=36 us)(Object ID 10438011)
-- 4-1) 서브쿼리에 Unique 조건을 주어 Driving으로 푸는 경우를 살펴봄
-- 보는 것처럼 서브쿼리를 1건 읽은 뒤 Main절을 10건 읽어서 최적으로 액세스 함
SELECT A.*
FROM TAB_A A
WHERE A.GUBN IN (SELECT B.GUBN
FROM TAB_B B
WHERE B.GUBN = A.GUBN
AND B.CNT = 1)
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 6 0 10
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.004 0 6 0 10
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
10 NESTED LOOPS (cr=6 pr=0 pw=0 time=137 us)
1 TABLE ACCESS BY INDEX ROWID TAB_B (cr=2 pr=0 pw=0 time=73 us)
1 INDEX UNIQUE SCAN TAB_B_U1 (cr=1 pr=0 pw=0 time=41 us)(Object ID 10438013)
10 TABLE ACCESS BY INDEX ROWID TAB_A (cr=4 pr=0 pw=0 time=55 us)
10 INDEX RANGE SCAN TAB_A_N1 (cr=2 pr=0 pw=0 time=242 us)(Object ID 10438012)
;
-- 4-2) 서브쿼리에 Unique 조건을 주어 Driving으로 푸는것이 더 ?음에도 불구하고 Filter로 사용할 경우
-- Main을 강제로 Driving하기 위해 힌트를 사용
-- 보는 것처럼 Main절을 Driving 때문에 불필요하게 100건을 읽은 뒤 서브쿼리에서 Filter를 하고 있음
SELECT /*+ LEADING(A) */
A.*
FROM TAB_A A
WHERE EXISTS (SELECT /*+ NO_UNNEST */
B.GUBN
FROM TAB_B B
WHERE B.GUBN = A.GUBN
AND B.CNT = 1)
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.004 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.001 0 24 0 10
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.010 0.004 0 24 0 10
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
10 FILTER (cr=24 pr=0 pw=0 time=197 us)
100 TABLE ACCESS FULL TAB_A (cr=4 pr=0 pw=0 time=1084 us)
1 TABLE ACCESS BY INDEX ROWID TAB_B (cr=20 pr=0 pw=0 time=196 us)
10 INDEX UNIQUE SCAN TAB_B_U1 (cr=10 pr=0 pw=0 time=107 us)(Object ID 10438013)
;
3) IN이 서브쿼리를 Driving으로, 또는 EXISTS가 서브쿼리를 Filter로 반드시 풀지 않음
-- 4-2) 예제 사용
-- EXISTS 구문을 사용하였기 때문에 TAB_A가 Driving이 될 것 같지만 옵티마이저는 CBQT(Cost Based Query Transformation)
-- 를 사용하기 때문에 서브쿼리가 Driving이 되도록 변경하여 플랜을 작성한다.
SELECT A.*
FROM TAB_A A
WHERE EXISTS (SELECT B.GUBN
FROM TAB_B B
WHERE B.GUBN = A.GUBN
AND B.CNT = 1)
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 6 0 10
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.000 0 6 0 10
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
10 NESTED LOOPS (cr=6 pr=0 pw=0 time=131 us)
1 TABLE ACCESS BY INDEX ROWID TAB_B (cr=2 pr=0 pw=0 time=74 us)
1 INDEX UNIQUE SCAN TAB_B_U1 (cr=1 pr=0 pw=0 time=42 us)(Object ID 10438013)
10 TABLE ACCESS BY INDEX ROWID TAB_A (cr=4 pr=0 pw=0 time=54 us)
10 INDEX RANGE SCAN TAB_A_N1 (cr=2 pr=0 pw=0 time=143 us)(Object ID 10438012)
h2.2. 서브쿼리 파라미터
1) 서브쿼리 관련 파라미터의 버전별 변화 과정
| 이름 | 8i | 9i | 10g | 설명 |
|---|---|---|---|---|
| _unnest_notexists_sq | n/a | single | n/a | 하나 이상의 테이블을 가지는 NOT EXISTS 서브쿼리를 가능하면 UNNEST |
| _unnest_subqerty | false | true | true | 상관 서브쿼리 unnesting을 가능하게 함 |
| _ordered_semi-join | true | true | true | 정렬된 세미 조인(EXISTS) 서브쿼리를 가능하게 함 |
| _cost_equality_semi_join | n/a | true | true | 등식(=equality) 세미 조인(EXISTS)의 비용계산을 활성화 |
| _always_anti_join | nested_loops | choose | choose | 가능하면 anti-JOIN(NOT EXISTS) 방식을 항상 사용 |
| _always_semi_join | standard | choose | choose | 가능하면 semi-JOIN(EXISTS) 방식을 항상 사용 |
| _optimizer_correct_sq_selectivity | n/a | n/a | true | 서브쿼리 선택도의 정확한 계산을 강제 |
| _optimizer_squ_bottomup | n/a | n/a | true | Bottom-up 방식으로 서브쿼리 unnesting을 가능하게 함 |
| _distinct_view_unnesting | n/a | n/a | false | in subquery를 'select distinct' 뷰로 unnesting 가능하게 함 |
| _right_outer_hash_enable | n/a | n/a | true | right outer(semi와 anti 해시 조인을 포함해서) 해시 조인을 가능하게 함 |
| _remove_aggr_subquery | n/a | n/a | true | 포함된 집계 서브쿼리의 제거를 가능하게 함 |
h2.3. 서브쿼리의 분류
| 카테고리 | 특징 |
|---|---|
| 상관/비상관 | 상관 서브쿼리는 outer 쿼리 블록에 있는 컬럼을 참조한다. 상관 서브쿼리는 대개 조인으로 변환될 수 있다. 비상관 서브쿼리는 선행 서브쿼리가 될 가능성이 있다. |
| 단순/복잡 | 단순한 서브쿼리는 단일 테이블만을 포함한다. 복잡한 서브쿼리는 여러 개의 테이블을 포함하며, 조인형태 또는 서브쿼리 안에 또 다른 서브쿼리를 갖는 형태이다. 서브쿼리가 복잡할 때는 적용되지 않고 단순할 때만 옵티마이저가 수행하는 기능들이 있다. |
| 집계 | 단순한(단일 테이블) 서브쿼리일지라도 그 안에 어떤 집계연산을 포함한다면 옵티마이저가 그들을 변환하지 못하게 하는 제약이 있을 수 있다. |
| 단일행 | (기껏해야) 단일 로우를 리턴하는 서브쿼리. 이는 대개 그 서브쿼리가 쿼리 전체의 선행 포인트가 될 수 있음을 의미한다. |
| IN / EXISTS | NOT IN 서브쿼리는 NOT EXISTS 서브쿼리로 재작성될 수 있다. 그런 후에 어떤 제약 하에서 안티 조인으로 변환될 수 있다. 'NOT IN'이 'IN'의 반대말이 아니며, 특히 NULL 컬럼인 경우 이 점에 주의해야 한다. |
h2.4. Semi JOIN
세미 조인이란 선행 테이블의 한 로우가 후행 테이블에서 한 로우와 조인에 성공하면
그 선행 로우에 대해서 더는 후행 테이블로의 프로세싱을 진행하지 않고 멈추기 때문에
리소스 사용을 절약하는 효과가 있으며 IN 또는 EXISTS 같은 구문의 효율적인 처리를
위해 나온 방식이다.
9i까지는 SQL 구문에서 선행 테이블이 먼저 드라이빙 되도록 구성되어 있다면 옵티마이저가
선행 테이블의 데이터가 많다 할지라도 실행계획을 그렇게 구성하였지만 10g로 오면서
선행 테이블의 데이터가 많다면 후행 테이블을 먼저 드라이빙하고 실행계획에서는 'SEMI'에서
'RIGHT SEMI' 문구가 보인다.
-- 1. 테이블 생성 및 통계정보 생성
DROP TABLE EMP PURGE;
DROP TABLE DEPT PURGE;
CREATE TABLE EMP (DEPT_NO NUMBER NOT NULL,
SAL NUMBER,
EMP_NO NUMBER,
PADDING VARCHAR2(60),
CONSTRAINT E_PK PRIMARY KEY(EMP_NO))
;
INSERT INTO EMP
SELECT MOD(ROWNUM, 6),
ROWNUM,
ROWNUM,
RPAD('X', 60)
FROM ALL_OBJECTS
WHERE ROWNUM <= 20000
;
CREATE TABLE DEPT (DEPT_NO NUMBER(6),
DEPT_GROUP NUMBER)
;
INSERT INTO DEPT VALUES(0, 1);
INSERT INTO DEPT VALUES(1, 1);
INSERT INTO DEPT VALUES(2, 1);
INSERT INTO DEPT VALUES(3, 2);
INSERT INTO DEPT VALUES(4, 2);
INSERT INTO DEPT VALUES(5, 2);
COMMIT;
ALTER TABLE DEPT ADD CONSTRAINT D_UK UNIQUE (DEPT_NO);
ALTER TABLE DEPT MODIFY DEPT_NO NOT NULL;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'EMP',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'DEPT',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
-- 2. 9i Semi Join
SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '9.2.0.8';
EXPLAIN PLAN FOR
SELECT EMP.*
FROM EMP
WHERE EXISTS (SELECT 1
FROM DEPT
WHERE EMP.DEPT_NO = DEPT.DEPT_NO
AND DEPT.DEPT_GROUP = 1)
;
@XPLAN;
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 751K| 54 |
|* 1 | HASH JOIN SEMI | | 10000 | 751K| 54 |
| 2 | TABLE ACCESS FULL| EMP | 20000 | 1406K| 36 |
|* 3 | TABLE ACCESS FULL| DEPT | 3 | 15 | 4 |
-----------------------------------------------------------
;
-- 3. 10g Semi Join
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '10.2.0.3';
EXPLAIN PLAN FOR
SELECT EMP.*
FROM EMP
WHERE EXISTS (SELECT 1
FROM DEPT
WHERE EMP.DEPT_NO = DEPT.DEPT_NO
AND DEPT.DEPT_GROUP = 1)
;
@XPLAN;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 751K| 70 (2)| 00:00:01 |
|* 1 | HASH JOIN RIGHT SEMI| | 10000 | 751K| 70 (2)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | DEPT | 3 | 15 | 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 20000 | 1406K| 65 (2)| 00:00:01 |
-----------------------------------------------------------------------------
;
h2.5. Anti Join
안티 조인은 세미 조인의 부정형으로 보면 된다.
EXPLAIN PLAN FOR
SELECT EMP.*
FROM EMP
WHERE EMP.DEPT_NO NOT IN (SELECT DEPT.DEPT_NO
FROM DEPT
WHERE DEPT.DEPT_GROUP = 2)
;
@XPLAN;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 751K| 70 (2)| 00:00:01 |
|* 1 | HASH JOIN RIGHT ANTI| | 10000 | 751K| 70 (2)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | DEPT | 3 | 15 | 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 20000 | 1406K| 65 (2)| 00:00:01 |
-----------------------------------------------------------------------------
h2.6. NULL과 NOT IN 그리고 NOT EXISTS(reference : http://www.ocmkorea.com)
서브쿼리에서 NOT EXISTS를 NOT IN으로 변환 시 조인키에 NULL값이 허용되어 있고
NULL값이 들어가 있다면 데이터가 다르게 나올 수 있다.
-- 1. 테이블 생성 및 통계정보 생성
DROP TABLE TAB1 PURGE;
DROP TABLE TAB2 PURGE;
CREATE TABLE TAB1 AS
SELECT '1111' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '2222' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '3333' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '4444' COL1, '9802' COL2 FROM DUAL UNION ALL
SELECT '4444' COL1, '9804' COL2 FROM DUAL UNION ALL
SELECT '5555' COL1, NULL COL2 FROM DUAL UNION ALL
SELECT '6666' COL1, '9807' COL2 FROM DUAL UNION ALL
SELECT NULL COL1, '9809' COL2 FROM DUAL
;
CREATE TABLE TAB2 AS
SELECT '1111' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '2222' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT '3333' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT 'KKKK' COL1, NULL COL2 FROM DUAL UNION ALL
SELECT '5555' COL1, '9801' COL2 FROM DUAL UNION ALL
SELECT NULL COL1, '9809' COL2 FROM DUAL
;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'TAB1',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'TAB2',
CASCADE => TRUE,
ESTIMATE_PERCENT => NULL,
METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
-- 2. NOT IN 테스트
SELECT *
FROM TAB1
WHERE COL2 NOT IN (SELECT COL2
FROM TAB2)
;
no rows selected
TAB1
| COL1 | COL2 |
|---|---|
| 1111 | 9801 |
| 2222 | 9801 |
| 3333 | 9801 |
| 4444 | 9802 |
| 4444 | 9804 |
| 5555 | NULL |
| 6666 | 9807 |
| NULL | 9809 |
TAB2
| COL1 | COL2 |
|---|---|
| 1111 | 9801 |
| 2222 | 9801 |
| 3333 | 9801 |
| KKK | NULL |
| 5555 | 9801 |
| NULL | 9809 |
이렇게 구성되어 있는 테이블을 COL2 컬럼으로 NOT IN인 것들을 찾을 때 데이터를 한건도 찾을 수 없었다.
예상대로라면 TAB2의 DISTICT.COL2인 값을 제외한 TAB1.COL1을 찾으면 되므로 '9802, 9804, NULL, 9807'
4개의 로우가 나와야 하나 한건도 나오지 않았는데 그 이유는 아래와 같다.
옵티마이저는 TAB2.COL2 컬럼의 NOT IN 조건에 만족하는 값을 찾기 위해 먼저 DISTINCT를 해서
'9801, NULL, 9809' 값을 가져온다. 그 다음 그 값을 가지고 TAB1.COL2 값과 아래처럼 비교를 한다.
여기서 COL2 <> '9801' 조건은 TRUE이나 COL2 <> NULL 조건은 FALSE이므로 이 조건은 모두 FALSE가 되고
이로 인해 데이터를 한건도 가져오지 못한다.
그러므로 값을 취하려면 서브쿼리에서 COL2 값이 IS NOT NULL인 값들만 취하도록 변경해 주어야 한다.
SELECT *
FROM TAB1
WHERE COL2 NOT IN (SELECT COL2
FROM TAB2
WHERE COL2 IS NOT NULL)
;
COL1 COL2
-------- --------
4444 9802
4444 9804
6666 9807
TAB2.COL2에서 IS NOT NULL값을 취했기 때문에 TAB1.COL1에서도 NULL값은 가져오지 않는다.
EXISTS는 드라이빙이 아닌 필터 방식으로 처리하므로 데이터를 가져올 수 있다.
SELECT *
FROM TAB1
WHERE NOT EXISTS (SELECT 1
FROM TAB2
WHERE TAB2.COL2 = TAB1.COL2)
ORDER BY COL1
;
COL1 COL2
그러므로 NOT IN을 NOT EXISTS로 변경 시 이 점을 반드시 유의하여 접근해야 한다.