쿼리변환
옵티마이저가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 말합니다. 비용기반 옵티마이저의 서브엔진 중
QUERY TRANSFORMER가 그런 역할을 담당합니다.
서브쿼리 UNNESTING
중첩된 서브쿼리를 풀어내는 것을 말합니다. 서브쿼리를 메인쿼리와 동일한 레벨로 풀어내면 다양한 엑세스 경로와 조인 메서드를 평가할 수 있습니다. 특히 옵티마이저는 많은 조인 테크닉이 있기 때문에 조인 형태로 변환했을 때 더 나은 실행계획을 찾을 가능성이 높아집니다.
하나의 쿼리에 여러 서브쿼리가 중첩해있습니다.
SELECT * FROM EMP A
WHERE EXISTS (
SELECT 'X' FROM DEPT
WHERE DEPNO = A.DEPTNO
)
AND SAL >
(
SELECT AVG(SAL) FROM EMP B
WHERE EXISTS (
SELECT 'X' FROM SALGRADE
WHERE B.SAL BETWEEN LOSAL AND HISAL
AND GRADE = 4
)
)
중첩된 서브쿼리는 메인쿼리에 계층적으로 종속된 관계입니다. 따라서 처리과정은 항상 필터방식입니다. 메인 쿼리에서 읽히는 레코드마다 서브쿼리를 반복하면서 조건에 맞는 데이터만 결과집합에 추가합니다. 하지만 서브쿼리를 처리하는데있어 필터 방식이 항상 최적의 수행속도를 보장하는 것은 아니므로 옵티마이저는 아 두가지 중 하나의 선택을 합니다.
- 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화 합니다
- 원래 상태에서 최적화한다. 메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 각각 최적화를 수행합니다. 이때 필터 오퍼레이션이 나타납니다.
서브쿼리 UNNESTING이 이뤄지면 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있게 됩니다. 중첩된 서브쿼리를 UNNESTING 하지 않고 쿼리블록별로 최적화 할때 각각 최적화된 쿼리가 전체의 최적을 달성하지 못할때가 많습니다. 또한 PLAN GENERATOR가 고려대상으로 삼을만한 실행계획을 생성하는 작업이 매우 제한된 범위 내에서만 이뤄집니다.
서브쿼리가 M쪽 집합이거나 NONUNIQUE 인덱스 일때
메인쿼리와 서브쿼리가 M : 1 관계인 경우 일반 조인문으로 바꿔도 같은 결과가 보장됩니다. 옵티마이저는 PK 제약이 설정된 테이블을 1쪽 집합으로 인식합니다. 만약 서브쿼리 쪽 테이블 조인 칼럼에 PK/UNIQUE 제약 또는 UNIQUE 인덱스가 없다면 변환 결과가 달라집니다.
메인쿼리와 서브쿼리가 1: M 관계인 경우 서브쿼리의 연관칼럼이 UNIQUE 하지 않습니다. 이때 필터방식으로 수행된 결과의 레코드 수는 메인쿼리(1쪽집합)의 총 건수를 넘지 못합니다. 그런데 옵티마이저가 임의로 UNNESTING을 수행하면 메인쿼리가 기준테이블이 되므로 수행결과가 달라집니다.
메인쿼리와 서브쿼리가 M : 1 관계인 경우라하더라도 1쪽 테이블에 PK/UNIQUE 제약 혹은 UNIQUE 인덱스가 없으면 옵티마이저는 두 테이블의 관계를 파악할 수 없어 UNNESTING 하지않습니다. 튜닝을 통해 직접 UNNESTING 할 수 있지만 1쪽 집합에 중복값이 발생해 결과에 이상이 생겨도 옵티마이저는 책임지지 않습니다.
옵티마이저는 이런 상황을 방지하기위해 두 가지 방식 중 하나를 선택합니다. 선택방식은 UNNESTING 후 어느 쪽 집합을 먼저 드라이빙 하느냐에 따라 달라집니다.
1쪽 집합을 확신할 수 없는 서브쿼리쪽 테이블이 먼저 드라이빙 되면 SORT UNIQUE 오퍼레이션을 수행해 1쪽 집합임을 보장한 다음 조인합니다. 반대로 메인 쿼리쪽 테이블이 먼저 드라이빙 되면 세미조인방식으로 조인해 1쪽 테이블에 중복데이터가 있는 오류가 있더라도 최초 한 건만 조인합니다.
뷰 MERGING
인라인 뷰 쿼리 블록은 뷰를 참조하는 쿼리블록과의 머지 과정을 거쳐 조인형태로 변환될 수 있습니다. 이를 VIEW MERGING이라고 합니다. VIEW MERGING을 통해 옵티마이저가 더 다양한 엑세스 경로를 후보군으로 삼을 수 있습니다.
VIEW MERGING 이전과 이후의 실행계획은 다음과 같이 달라집니다.
-- 이전
-- EXECUTION PLAN
-- SELECT STATEMENT OPTIMIZER = ALL_ROWS
-- NESTED LOOPS
-- VIEW OF 'EMP_SALESMAN' (VIEW)
-- TABLE ACCESS (BY INDEX OF ROWID) OF EMP (TABLE)
-- INDEX(RANGE SCAN) OF EMP_SAL_IDX(IDX)
-- TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE)
-- INDEX (UNIQUE SCAN) OF 'DEPT_PK'(INDEX(UNIQUE))
-- 이후
-- EXECUTION PLAN
-- SELECT STATEMENT OPTIMIZER = ALL_ROWS
-- NESTED LOOPS
-- TABLE ACCESS (BY INDEX ROWID) OF 'EMP'(TABLE)
-- INDEX (RANGE SCAN) OF 'EMP_SAL_IDX' (INDEX)
-- TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE)
-- INDEX (UNIQUE SCAN) OF 'DEPT_PK'
단순한 뷰는 MERGING 해도 성능이 나빠지지 않습니다. 하지만 복잡한 연산을 포함한 뷰는 MERGING 하면 성능이 나빠질 수 있습니다.
- group by 절
- SELECT LIST에 DISTINCT 연산자가 포함된 형태
VIEW MERGING은 쿼리 수행비용이 더 낮아지는지 조사한 후 적용 여부를 판단해야 합니다. 뷰 MERGING이 애초에 불가능한 경우도 있습니다.
- 집합 연산자
- connect by 절
- ROWNUM PSEUDO 칼럼
- SELECT-LIST 집계함수
- 분석함수
조건절 PUSHING
옵티마이저가 뷰를 처리할때 1차적으로 VIEW MERGING을 고려하지만 조건절 PUSHING을 시도할 수도 있습니다. 뷰를 참조하여 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 밀어넣는 기능을 말합니다. 뷰 내부서 조건절을 처리하도록 밀어 넣으면 다음 단계에서 처리해야할 일량을 최소화할 수 있습니다. PUSHING과 관련해 DBMS가 사용하는 기술은 다음과 같습니다.
- 조건절 PUSHDOWN
- 조건절 PULLUP
- 조인조건 PUSHDOWN
조건절 PUSHDOWN
뷰 밖에 있는 조건절을 뷰 안쪽으로 일어 넣어 다음 단계에서의 일량을 줄이고 인덱스 상황에 따라 더 효과적인 인덱스 선택이 가능해집니다.
SELECT DEPTNO, AVG_SAL
FROM (
SELECT DEPTNO, AVG(SAL) AVG_SAL
FROM EMP
GROUP BY DETPNO
) A
WHERE DEPTNO = 30
-- OPERATION
-- SELECT STATEMENT
-- VIEW
-- SORT GROUP BY NOSORT
-- TABLE ACCESS BY INDEX ROWID | EMP
-- INDEX RANGE SCAN EMP_DEPTNO_IDX
위 쿼리를 처리할때 쿼리변환이 작동하지 않는다면 뷰에서 사용된 테이블을 FULL SCAN하고 GROUP BY 한 이후 외부조건으로 필터링합니다. 하지만 조건절 pushing이 작동함으로써 인덱스 범위 스캔이 가능해집니다.
SELECT B.DEPTNO, B.DNAME, A.AVG_SAL
FROM (
SELECT DEPTNO, AVG(SAL) AVG_SAL
FROM EMP
GROUP BY DEPTNO
) A,
DEPT B
WHERE A.DPETNO = B.DEPTNO
AND B.DEPTNO = 30
-- 0 SELECT STATEMENT
-- 1 NESSTED LOOPS
-- 2 TABLE ACCESS BY INDEX ROWID DEPT
-- 3 INDEX UNIQUE SCAN DPET_PK DEPT_PK
-- 4 VIEW
-- 5 SORT GROUP BY
-- 6 TABLE ACCESS BY INDEX ROWID EMP
-- 7 INDEX RANGE SCAN EMP_DEPTNO_IDX
-- predicate information
-- 3 - access ("B", "DEPTNO" = 30)
-- 7 - access ('DEPTNO' = 30)
위 쿼리는 조건절 이행을 통해 인라인뷰에 ADEPTNO = 30 이라는 조건이 뷰안으로 PUSHING 됐습니다.
조건절 PULLUP
뷰 내부의 조건절을 쿼리 블록 밖으로 끄집어내는 것을 말합니다. 그리고 끄집어낸 조건절을 다시 다른 쿼리 블록에 PUSHDOWN 할수도 있습니다.
SELECT *
FROM
(
SELECT DEPTNO, AVG(SAL)
FROM EMP
WHERE DEPTNO = 10
GROUP BY DEPTNO
) E1,
(
SELECT DEPTNO, MIN(SAL), MAX(SAL)
FROM EMP
GROUP BY DEPTNO
) E2
WHERE E1.DEPTNO = E2.DEPTNO
-- EXECUTION PLAN
-- 0 SELECT STATEMENT
-- 1 HASH JOIN
-- 2 VIEW
-- 3 HASH GROUP BY
-- 4 TABLE ACESS BY INDEX ROWID EMP
-- 5 INDEX RANGE SCAN EMP_DEPTNO_INDEX
-- 6 VIEW
-- 7 HASH GROUP BY
-- 8 TABLE ACESS BY INDEX ROWID EMP
-- 9 INDEX RANGE SCAN EMP_DEPTNO_INDEX
-- Predicate Information
-- 1 - -ACCESS ("E1"."DEPTNO" = "E2".DEPTNO")
-- 5 - ACCESS("DEPTNO" = 10)
-- 9 - ACCESS("DEPTNO" = 10)
실행계획을 통해 E1 뷰 내부의 조건절이 PULLUP 되어 E2로 PUSHDOWN 된것을 확인할 수 있습니다.
조인조건 PUSHDOWN
조인 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 것으로 NL 조인 수행중에 드라이빙 테이블에서 읽은 조인 칼럼 값을 INNER 쪽 뷰 쿼리블록 내에서 참조할 수 있도록 하는 기능입니다.
SELECT D.DEPTNO, D.DNAME, E.AVG_SAL
FROM DEPT D
,(
SELECT /*+NO_MERGE PUSH_PRED*/ DEPTNO
, AVG(SAL) AVG_SAL
FROM EMP GROUP BY DEPTNO
) E
WHERE E.DEPTNO(+) = D.DEPTNO
-- EXECUTION PLAN
-- 0 SELECT STATEMENT
-- 1 NESTED LOOPS OUTER
-- 2 TABLE ACCESS FULL DEPT
-- 3 VIEW PUSHED PREDICATE
-- 5 SORT AGGREGATE
-- 6 TABLE ACCESS BY INDEX ROWID EMP
-- 7 INDEX RANGE SCAN EMP_DEPTNO_IDX
실행계획에 나타난 VIEW PUSHED PREDICATE 오퍼레이션을 통해 조인조건애 해당하는 칼럼에대해서만 GROUP BY가 집계됐음을 알 수 있습니다. 위 쿼리에서는 NO_MERGE와 PUSH_PRED 힌트를 명시했지만 힌트가 없더라도 옵티마이저에 의해 같은 실행계획이 선택될 수 있습니다.
이 기능을 통해 서브쿼리를 부분범위처리할 수 있습니다. 조건절 없이 전체를 FULL SCAN 해야하는 경우 DEPT 테이블 전체를 GROUP BY 해야하므로 성능상 비효율적입니다.
집계함수가 하나라면 스칼라 서브쿼리로 변환해 부분범위처리가 가능하게 할 수 있습니다.
SELECT D.DEPTNO, D.DNAME
, ( SELECT AVG(SAL), FROM EMP WHERE DEPTNO = D.DEPTNO)
FROM DEPT D
-- EXECUTION PLAN
-- SELECT STATEMENT
-- SORT AGGREGATE
-- TABLE ACCESS BY INDEX ROWID TABLE EMP
-- INDEX RANGE SCAN INDEX EMP_DEPTNO_IDX
-- TABLE ACCESS FULL TABLE DEPT
집계함수가 여러개라면 같은 테이블을 반복 엑세스하는 비효율이 발생할 수 있습니다. 이럴때는 서브쿼리에서 구하려는 값을 모두 결합하고 바깥쪽 엑세스 쿼리에서 분리하는 방법을 사용할수 있습니다.
SELECT DEPTNO, DNAME
, TO_NUMBER(SUBSTR(SAL, 1, 7)) AVG_SAL
, TO_NUMBER(SUBSTR(SAL, 8, 7)) MIN_SAL
, TO_NUMBER(SUBSTR(SAL, 15) ) MAX_SAL
FROM (
SELECT /*+ NO_MERGE */ D.DEPTNO , D.DNAME
,(
SELECT LPAD(AVG(SAL), 7) || LPAD(MIN(SAL), 7), || MAX(SAL)
FROM EMP
WHERE DEPTNO = D.DEPTNO
) SAL
FROM DEPT D
)조건절 이행
A=B, B=C 이면 A=C이다 라는 추론을 통해 새로운 조건절을 내부적으로 생성해주는 쿼리변환입니다. 조건절 이행을 통해 조인 대상의 레코드 수를 줄여 조인되는 데이터양을 줄일 수 있고, 엑세스를 위한 인덱스 사용을 추가로 고려할 수 있게 됩니다.
불필요한 조인 제거
1:M 관계의 조인에서 조인조건을 제외한 어디서에서 1쪽 테이블을 참조하지 않는다면 쿼리 수행시 1쪽 테이블을 읽지않아도 쿼리 집합에 영향을 미치지 않습니다. 옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽는 쿼리를 변환합니다.
SELECT E.EMPNO, E.ENAME, E.DEPTNO, E.SAL, E.HIREDATE
FROM DETP D, EMP E
WHERE D.DEPTNO = E.DEPTNO
-- EXECUTION PLAN
-- TABLE ACCES FULL EMP
쿼리변환을 위해서는 PK와 FK 제약이 설정돼 있어야합니다. PK를 통해 두 테이블간의 조인카디널리티를 파악할 수 있고, FK가 없으면 조인에 실패하는 레코드가 존재할 수도 있어 옵티마이저가 함부로 쿼리변환을 수행할 수 없습니다.
조인조건이 NULL 허용컬럼이면 값이 null 인 레코드는 조인되지 않습니다. 하지만 쿼리변환을 통해 값이 NULL인 레코드가 결과집합에 포함될 수 있어 결과값이 달라질 수 있습니다. 이런 오류를 방지하기 위해 옵티마이저가 내부적으로 IS NOT NULL 조건을 추가해줍니다. M쪽 집합기준 OUTER 조인일 경우 NULL 제약이나 FK 제약없이도 조인 제거가 가능합니다.
OR 조건을 UNION으로 변환
옵티마이저가 필요에의해 OR 조건을 UNION으로 변경하는것을 OR-EXPANSION 쿼리 변환이라고 합니다.
select * from emp
where job = 'clerk' or deptno = 20
SELECT * FROM EMP
WHERE JOB = 'CLERK'
UNION ALL
SELECT * FROM EMP
WHERE DEPTNO = 20
AND LNNVL(JOB = 'CLEAK')
-- 0 SELECT STATEMENT
-- 1
-- 2 TABLE ACCESS BY INDEX ROWID EMP
-- 3 INDEX RANGE SCAN EMP_JOB_IDX
-- 4 TABLE ACCESS BY INDEX ROWID EMP
-- 5 INDEX RANGE SCAN EMPO_DEPTNO_IDX
-- PREDICATE INFORMATION
-- 3 - ACCESS("JOB=CLERK")
-- 4 - FILTER(LNNVL(JOB = CLEARK))
-- 5 - ACCESS("DEPTNO" = 20)
UNION ALL할 각각의 브랜치는 인덱스를 사용했고 분기된 두 브랜치가 각각 다른 인덱스를 사용하긴하지만 emp 테이블 액세스가 두 번 일어납니다. 따라서 중복 엑세스 되는 영역의 비중이 낮을 수록 효과적입니다. 반대의 경우라면 오히려 쿼리 수행비용이 증가할 수 있습니다. 중복 엑세스되더라도 결과 집합에는 중보이 없게하기위해 옵티마이저가 내부적으로 LNNVL 함수를사용해 중복으 제거합니다.
LNNVL 함수
함수 내부 조건이 FALSE 이거나 비교에 사용된 필드가 NULL 값인 경우 TRUE를 반홥합니다. ORACLE에서 OR-EXPANSION을 제어하기 위해 사용하는 힌트로는 USE_CONCAT, NO_EXPAND가 있습니다.
기타쿼리변환
집합연산을 조인으로 변환
INTERSECT 혹은 MINUS 같은 집합연산을 조인형태로 변환하는 것을 말합니다.
SELECT JOB, MGR FROM EMP
MINUS
SELECT JOB, MGR FROM EMP
WHERE DEPTNO = 10;
다음은 옵티마이저가 MINUS 연산을 조인 형태로 변환했을때의 실행계획입니다.
--- EXECUTION PLAN
-- 0 SELECT STATEMENT
-- 1 HASH UNIQUE
-- 2 HASH JOIN ANTI
-- 3 TABLE ACCESS FULL
-- 4 TABLE ACCESS FULL
-- PREDICATE INFORMATION
-- 2 - ACCESS(SYS_OP_MAP_NONNULL("JOB") = SYS_OP_MAP_NONNULL("JOB") AND SYS_OP_MAP_NONNULL("MGR") = SYS_OP_MAP_NONNULL("MGR"))
-- 4 - FILTER("DEPTNO = 10")
HASH ANTI JOIN을 수행하고 나서 중복 값을 제거하기 위한 HASH UNIQUE 연산을 수행합니다. 다음과 같은 형태로 쿼리변환이 일어난 것입니다.
SELECT DISTICT JOB, MGR FROM EMP E
WHERE NOT EXISTS (
SELECT 'X' FROM EMP
WHERE DEPTNO = 10;
AND SYS_OP_MAP_NONNULL(JOB) = SYS_OP_MAP_NONNULL(E.JOB)
AND SYS_OP_MAP_NONNULL(MGR) = SYS_OP_MAP_NONNULL(E.MGR)
);
조인 칼럼에 IS NOT NULL 조건 추가
select count (e.empno), count(d.dname)
from emp e, dept d
where d.deptno = e.deptno
and sal <= 2900
조인칼럼의 값이 NULL인 레코드는 조인 엑세스가 불필요합니다. 어차피 조인에 실패하기 때문입니다. 따라서 필터 조건을 추가해주면 불필요한 테이블 엑세스 및 조인 시도를 줄일 수 있습니다.
select count (e.empno), count(d.dname)
from emp e, dept d
where d.deptno = e.deptno
and sal <= 2900
AND E.DEPTNO IS NOT NULL
AND D.DEPTNO IS NOT NULL
IS NOT NULL 조건을 사용자가 직접 기술하지 않아도, 옵티마이저가 필요하다고 판단되면 내부적으로 추가합니다.
필터 조건 추가
공집합이 예상되는 조건절에 임의로 필터 조건을 추가하여 커리실행 자체를 맞는 방식입니다.
조건절 비교 순서
AND 조건의 경우 만족하는 적은 조거네 대한 평가를 먼저하는 것이 성능상 유리합니다.
SELECT * FROM T
WHERE A = 1
AND B = 1000;
다음과 같은 조건절을 처리할때도 부등호 조건을 먼저 평가하느냐 like 조건을 머저 평가하느냐에 따라 일량이 달라집니다.
SELECT /*+FULL(도서)*/ 도서번호, 도서명, 가격, 저자, 출판사, isbn
FROM 도서
WHERE 도서명 LIKE '데이터베이스%' -- 사용자가 입력한 검색 키워드
AND 도서명 > '데이터베이스성능고도화' -- 앞 페이지 화면에서 출력한 가장 마지막 도서명
DBMS에 따라 다르긴 하지만 최신 옵티마이저는 비교 연산해야할 일량을 고려해 선택도가 낮은 칼럼의 조건식부터 처리하도록 순서를 조정합니다.