쿼리변환이란 옵티마이저가 SQL을 분석해 동일한 결과를 리턴하면서도 더 나은 성능이 기대되는 형태로 SQL을 재작성하는 것을 말합니다. 비용기반 옵티마이저의 서브엔진 중 QUERY TRANSFORMER가 쿼리변환을 담당합니다. 쿼리변환은 다음 두가지 방식으로 작동합니다.
- 휴리스틱 쿼리변환
결과만 보장된다면 무조건 쿼리변환을 수행합니다.
- 비용기반 쿼리변환
변환된 쿼리의 비용이 더 낮을 때만 변환합니다.
서브쿼리 UNNESTING
변환을 통해 중첩된 서브 쿼리를 풀어냅니다. 서브쿼리를 메인쿼리와 같은 레벨로 풀면 다양한 액세스 경로와 조인 메서드를 평가할 수 있습니다. 많은 조인테크닉을 통해 더 나은 실행계획을 찾을 가능성이 높아집니다. 아래는 중첩된 서브쿼리(NESTED SUBQUERY)입니다.
SELECT * FROM EMP A
WHERE EXISTS (
SELECT 'X' FROM DEPT
WHERE DEPTNO = A.DPETNO
)
AND SAL >
(
SELECT AVG(SAL)
FROM EMP B
WHERE EXISTS (
SELECT 'X'
FROM SALGRADE
WHERE B.SAL BETWEEN LOSAL AND HISAL
AND GRADE = 4
)
)
중첩된 서브쿼리는 메인쿼리와 계층관계로 IN, EXISTS 연산자로 처리합니다. 메인쿼리에서 읽히는 레코드 마다 서브 쿼리를 반복 수행하면서 조건에 맞지 않는 데이터를 필터링합니다. 필터링 방식은 항상 최적의 수행속도를 보장하지 못하므로 옵티마이저는 아래의 선택지 중 하나를 선택합니다.
- SUBQUERY UNNESTING을 통해 동일한 결과를 보장하는 조인문으로 변환해 각각 최적화 합니다.
- 기존 구조를 유지하며 메인쿼리와 서브쿼리를 각각 최적화 합니다. 이때 서브쿼리에 필터 오퍼레이션이 나타납니다.
SUBQUERY UNNESTING은 계층구조를 해소하므로 SUBQUERY FATTENING이라고도 부릅니다. 이를 통해 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있습니다. 메인 쿼리와 서브 쿼리를 각각 최적화할때는 전체적인 최적화를 달성하지 못할때가 많습니다. 실제 SUBQUERY UNNESTING의 작동방식은 다음과 같습니다.
-- 부서에 소속된 직원
SELECT * FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT)
SELECT *
FROM (SELECT DEPTNO FROM DEPT) A, EMP B
WHERE B.DEPTNO = A.DEPTNO;
-- 직원이 소속돼 있는 부서
SELECT * FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP)
SELECT *
FROM (SELECT DEPTNO FROM EMP) A, DEPT B
WHERE B.DEPTNO = A.DEPTNO;
위 서브 쿼리가 UNNESTING 되면, 변환쿼리는 다음과 같은 조인문 형태가 됩니다.
SELECT *
FROM (SELECT DEPTNO FROM DEPT) A, EMP B
WHERE B.DEPTNO = A.DEPTNO;
SUBQUERY UNNESTING의 주의할 점은 항상 더 나은 성능을 보장하지 않는다는 사실입니다. 이를 사용자가 제어하기 위해 두 가지 힌트가 제공됩니다.
- unnest : 서브 쿼리를 UNNESTING 하여 조인방식으로 최적화를 유도합니다.
- no_unnest : 서브 쿼리를 그대로 두고 필터 방식으로 최적화 합니다.
서브쿼리가 M쪽 집합이거나 NONUNIQUE 인덱스일때
메인쿼리와 서브쿼리가 M:1 관계일 경우 일반 조인문으로 바꿔도 항상 같은 결과가 보장됩니다. 메인쿼리와 서브쿼리의 관계가 1:M인 경우, 결과 집합은 1쪽 테이블의 총 건수를 넘을 수 없습니다.
SELECT *
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP)
이때 옵티마이저가 임의로 조인문으로 변경하면 M쪽 집합 단위의 결과 집합이 만들어져 결과가 달라집니다.
SELECT *
FROM (SELECT DEPTNO FROM DEPT) A, EMP B
WHERE B.DEPTNO = A.DEPTNO;
특히 1쪽 테이블에 PK/UNIQUE 제약이나 UNIQUE 인덱스가 없으면 옵티마이저는 두 테이블의 관계를 알 수 없어 일반 조인문으로의 쿼리변환을 시도하지 않습니다. 위와 같은 경우 옵티마이져는 두 가지 방식 중 하나를 선택합니다. 옵티마이저의 선택은 UNNESTING 후 어느쪽 집합이 먼저 드라이빙 되느냐 에 따라 달라집니다.
SORT UNIQUE
서브쿼리 테이블이 먼저 드라이빙 되면 서브쿼리 테이블에 대해 SORT UNIQUE 오퍼레이션이 발생합니다.
메인쿼리와 서브쿼리의 관계가 1: M 일때, 서브쿼리 테이블이 먼저 드라이빙되면 SORT UNIQUE 오퍼레이션을 통해 서브쿼리 테이블을 1쪽 집합으로 만들고 조인합니다. 조인 결과가 1쪽 테이블 단위로 생셩되야 하기 때문입니다.
CREATE INDEX DEPT_DEPTNO_IDX ON DEPT(DEPTNO);
SELECT /*+LEADING(EMP) USE_NL(DEPT)*/ *
FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP);
-- Operation
-- SELECT STATEMENT
-- TABLE ACCESS BY INDEX ROWID EMP
-- NESTED LOOPS
-- SORT UNIQUE
-- INDEX FULL SCAN EMP_DEPTNO_IDX
-- INDEX RANGE SCAN DEPT_DEPTNO_IDX
메인쿼리와 서브쿼리의 관계가 M:1인 경우에도, 서브쿼리 테이블이 먼저 드라이빙되면 SORT UNIQUE OPERATION이 발생합니다. 서브쿼리가 1쪽 집합임을 보장하기 위함입니다.
CREATE INDEX DEPT_DEPTNO_IDX ON DEPT(DEPTNO);
SELECT /*+LEADING(DEPT) USE_NL(EMP)*/ *
FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT);
-- Operation
-- SELECT STATEMENT
-- TABLE ACCESS BY INDEX ROWID EMP
-- NESTED LOOPS
-- SORT UNIQUE
-- INDEX FULL SCAN OF DEPT_DEPTNO_IDX
-- INDEX RANGE SCAN OF EMP_DEPTNO_IDX
실제로 DEPT 테이블은 1쪽 테이블임에도 SORT UNIQUE OPERATION이 발생한 것을 확인할 수 있습니다. 다음과 같은 형태의 쿼리변환이 발생합니다.
SELECT B.*
FROM (
SELECT /*+NO_MERGE*/ DISTINCT DEPTNO
FROM DEPT
ORDER BY DEPTNO
)A, EMP B
WHERE B.DEPTNO = A.DEPTNOSEMI JOIN
메인쿼리쪽 테이블이 먼저 드라이빙되면 서브쿼리에 대해 세미조인 방식으로 조인합니다.
메인쿼리와 서브쿼리가 1: M 관계일 경우, NL 세미조인 방식으로 조인하면 SORT UNIQUE 오퍼레이션을 수행하지 않고도 결과 집합이 서브쿼리의 M쪽 집합으로 확장하는 것을 방지하는 알고리즘이 사용됩니다. OUTER 테이블의 한 로우가 INNER 테이블의 한 로우와 조인에 성공하면 진행을 멈추고 OUTER 테이블의 다음 로우를 처리합니다.
select * from DEPT
where deptno in (select deptno from EMO);
-- Operation
-- SELECT STATEMENT
-- NESTED LOOPS SEMI
-- TABLE ACCESS FULL OF DEPT
-- INDEX RANGE SCAN OF EMP_IDX
VIEW MERGING
INLINE-VIEW 를 이용해 쿼리를 작성하면 내용을 파악하기 쉽습니다.
SELECT *
FROM
(
SELECT * FROM EMP WHERE JOB = 'SALESMAN'
) A,
(
SELECT * FROM DEPT WHERE LOC = 'CHICAGO'
) B
WHERE A.DPETNO = B.DEPTNO
하지만 서브쿼리로 블록화된 쿼리는 옵티마이징에 불리하게 작용합니다. 아래처럼 쿼리 블록을 풀어서 해석하기 때문입니다.
SELECT *
FROM EMP A, DEPT B
WHERE A.DPETNO = B.DEPTNO
AND A.JOB = 'SALESMAN'
AND B.LOC = 'CHICAGO'
블록을 푸는 과정을 VIEW MERGING 이라고 합니다. VIEW MERGING을 통해 옵티마이저는 더 다양한 엑세스 경로를 고려대상으로 삼을 수 있습니다.
CREATE OR REPLACE VIEW 명령어를 통해 VIEW 를 생성할 수 있습니다.
CREATE OR REPLACE VIEW EMP_SALESMAN
AS
SELECT EMPNO, ENAMNE, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMO
WHERE JOB = 'SALESMAN';
VIEW는 일반테이블과 조인할 수 있습니다.
SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, D.DNAME
FROM EMP_SALESMAN E, DEPT D
WHERE D.DPETNO = E.DEPTNO
AND E.SAL >= 1500;
위 조인을 VIEW MERGING 하지않고 그대로 최적화 하면 다음과 같은 실행계획을 생성합니다.
-- EXECUTION PLAN
-- SELECT STATEMENT OPTIMIZER = ALL_ROWS
-- NESTED LOOPS
-- VIEW OF 'EMP_SALESMAN'(VIEW)
-- TABLE ACCESS BY INDEX 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)
VIEW MERGING이 작동하면 아래와 같은 실행계획을 생성합니다.
-- EXECUTION PLAN
-- SELECT STATEMENT OPTIMIZER = ALL_ROWS
-- NESTED LOOPS
-- TABLE ACCESS BY INDEX 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)
단순한 구조의 VIEW는 MERGING 이 성능에 영향을 주지 않지만 아래와 같은 복잡한 연산을 포함한 VIEW를 MERGING 하면 성능이 악화될 수 있습니다.
- group by 절
- select-list에 DISTINCT 연산자 포함
이로인해, VIEW를 MERGING 했을 때, 쿼리 수행비용이 낮아지는지를 우선 조사한 후 MERIGING 여부를 판단하는 쪽으로 옵티마이저가 발전하고 있습니다. 필요하다면 MERGING 여부를 직접 조정할 수도 있습니다. ORACLE의 경우, MERGING을 제어하기 위해 merge와 no_merge 힌트를 제공합니다. 아래는 VIEW MERGING이 불가능한 경우 입니다.
- 집합 연산자
- CONNECT BY 절
- ROWNUM PSEUDO 칼럼
- SELECT-LIST 집계함수
- 분석함수
조건절 PUSHING
옵티마이저가 VIEW를 처리할때 1차적으로 MERGING을 고려하고 조건절 PUSHING을 시도할 수 있습니다. 조건절 PUSHING은 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 안으로 밀어넣는 기능입니다. 이를 통해 VIEW 내부의 일량을 최소화하고 반환되는 결과의 건수를 줄일 수 있습니다. 조건절 PUSHING과 관련해 DBMS가 사용하는 기술은 다음과 같습니다.
- 조건절 PUSHDOWN : 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안으로 밀어 넣습니다.
- 조건절 PULLUP : 쿼리블록 안에 있는 조건절이 쿼리 블록 밖으로 나가고 그 조건절을 다른 쿼리 블록에 PUSHDOWN 합니다.
- 조인조건 PUSHDOWN : NL 조인 수행중 드라이빙 테이블에서 읽은 값을 건건이 INNER 쪽 뷰 쿼리 블록 안으로 밀어넣습니다.
조건절 PUSHDOWN
group by 를 포함한 뷰를 처리할때, 쿼리 블록 밖에있는 조건절을 쿼리블록 안쪽으로 밀어 넣을 수 있다면 GROUP BY 해야할 데이터의 양을 줄일 수 있습니다. 인덱스 상황에 따라 더 효과적인 인덱스 선택이 가능해지기도 합니다.
SELECT DEPTNO, AVG_SAL
FROM (
SELECT DEPTNO, AVG(SAL) AVG_SAL
FROM EMP
GROUP BY DETPNO
) A
WHERE DEPTNO = 30
위 쿼리의 실행계획을 보면 VIEW 내부에 조건절이 없음에도 EMP_DEPTNO_IDX를 사용합니다. 이는 조건절 PUSHING이 작동함으로써 인라인 뷰 내부에 DEPTNO = 30 이라는 조건이 적용됐음을 알 수 있습니다.
SELECT STATEMENT
VIEW
SORT GROUP BY NOSORT
TABLE ACCESS BY INDEX ROWID OF EMP
INDEX RANGE SCAN OF EMP_DEPTNO_IDX
아래의 쿼리와 실행계획을 보면 인라인 뷰에서 DEPTNO = 30 조건을 활용해 데이터량을 줄이고 GROUP BY 하는 것을 확인할 수 있습니다. 조건절이 PUSHDOWN 할 수 있었던 이유는 '조건절 이행' 쿼리변환이 먼저 발생했기 때문입니다. 외부 쿼리의 조건절이 조인조건을 타고 VIEW 내부로 전이됨으로써 뷰 내부 테이블에 대한 조건절로 실행됩니다.
SELECT
FROM
(
SELECT DEPTNO, AVG(SAL) AVG_SAL
FROM EMP
GROUP BY DEPTNO
)
DEPT B
WHERE A.DEPTNO = B.DEPTNO
AND B.DEPTNO = 30
- 0 SELECT STATEMENT
- 1 0 NESTED LOOPS
- 2 1 TABLE ACCESS BY INDEX ROWID DEPT
- 3 2 INDEX UNIQUE SCAN OF DEPT_PK
- 4 1 VIEW
- 5 4 SORT GROUP BY
- 6 5 TABLE ACCESS BY INDEX ROWID EMP
- 7 6 INDEX RANGE SCAN OF EMP_DEPTNO_IDX
-- predicate information
-- 3 - access ("B", "DEPTNO" = 30)
-- 7 - access ('DEPTNO' = 30)조건절 PULLUP
안쪽에 있는 조건을 바깥쪽으로 빼내는 것을 조건절 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)
위 쿼리에서 인라인 뷰 E2에는 DEPTNO 에 대한 조건이 없지만 PREDICATE 정보를 보면 양쪽 모두 이 조건에 대한 인덱스가 사용된 것을 확인할 수 있습니다. 다음과 같은 형태로 쿼리 변환이 일어납니다.
SELECT * FROM
(SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO) E1
,(SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO) E2
WHERE E1.DEPTNO = E2.DEPTNO조인조건 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
-- 4 FILTER
-- 5 SORT AGGREGATE
-- 6 TABLE ACCESS BY INDEX ROWID EMP
-- 7 INDEX RANGE SCAN EMP_DEPTNO_IDX
-- Predicate INFORMATION
-- 4 - FILTER (COUNT(*) > 0)
-- 7 - ACCESS ("DEPTNO = 0, "DEPTNO"")
EMP 테이블에도 DEPTNO 에 대한 인덱스가 사용됐음을 확인할수 있습니다. 힌트가 없더라도 옵티마이저에의해 동일한 실행계획이 선택됩니다. 이 기능은 부분범위처리가 필요한 상황에서 유용합니다. EMP를 FULL SCAN 하는 경우 DEPT 테이블의 deptno 마다 emp 테이블 전체를 GROUP BY 하므로 성능상 불리합니다. 위 쿼리는 집계함수가 하나 뿐이므로 스칼라 서브 쿼리로 변환해 부분범위처리가 가능하게 할 수 있습니다.
SELECT D.DEPTNO, D.DNAME
, ( SELECT AVG(SAL), FROM EMP WHERE DEPTNO = D.DEPTNO)
FROM DEPT D
집계함수가 여러 개일 때는 같은 테이블을 여러번 반복해서 읽는 문제가 발생합니다.
SELECT D.DEPTNO, D.DNAME
,(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) AVG_SAL
,(SELECT MIN(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) MIN_SAL
,(SELECT MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) MAX_SAL
FROM DEPT D
이런 경우 하나의 쿼리로 구하려는 값을 모두 결합한 후, 바깥쪽 엑세스 쿼리에서 SUBSTR함수로 분리하는 방법을 활용할 수 있습니다.
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 이다. 라는 추론을 통해 새로운 조건절을 내부적으로 생성하는 쿼리변환 입니다.A 테이블에서 사용된 필터 조건이 조인조건을 타고, 반대편 B 테이블에 대한 필터 조건으로 이행됩니다.
-- EXECUTION PLAN
-- 0 SELECT STATEMENT
-- 1 NESTED LOOPS
-- 2 TABLE ACCESS BY INDEX ROWID DEPT
-- 3 INDEX UNIQUE SCAN DEPT_PK
-- 4 TABLE ACCESS BY INDEX ROWID EMP
-- 5 INDEX RANGE SCAN EMP_IDX
-- PREDICATE INFORMATION
-- 3 - ACCESS("D", "DEPTNO" = 10)
-- 5 - ACCESS("E", "DPETNO" = 10 AND "E".JOB = "MANAGER")
위 쿼리는 E.DEPTNO = 10 이고 D.DEPTNO = E.DEPTNO 이므로 D.DEPTNO가 10으로 추론됐습니다. 조건절 이행을 통해 아래와 같은 형태로 변환된 것입니다. 변환을 통해 조인전 테이블에 필터링을 적용함으로써 조인되는 데이터의 양을 줄일 수 있습니다. 또한 조건절 추가에 따른 활용인덱스 개선 또한 가능합니다.
SELECT *
FROM DEPT D, EMP E
WHERE E.JOB = 'MANAGER'
AND E.DEPTNO = 10
AND D.DEPTNO = 10불필요한 조인 제거
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를 통해 모든 칼럼이 조인에 성공함을 보장할 수 있습니다. 이 조건이 보장되야만 쿼리 변환을 수행할 수 있습니다.
ALTER TABLE DEPT ADD
CONSTRAINT DEPTNO_PK PRIMARY KEY(DEPTNO);
ALTER TABLE EMP ADD
CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO)
REFERECENS DEPT(DEPTNO);
단, FK가 설정돼 있더라도 M쪽 칼럼이 null 허용 칼럼이라면 겨로가가 다를 수 있습니다. NULL 값은 조인에 실패하는데 옵티마이저가 조인문을 제거하면 결과집합에 포함되기 때문입니다. 이런 오류를 방지하기 위해 옵티마이저는 내부적으로 EMP.DEPT IS NOT NULL 조건을 추가합니다. OUTER 조인인 경우 NOT NULL 제약, IS NOT NULL 조건, FK 제약이 없어도 논리적을 조인 제거가 가능합니다.
SELECT E.EMPNO, E.ENAME, E.SAL, E.HIREDATE
FROM EMP E, DEPT D
WHERE D.DEPTNO(+) = E.DEPTNO -- OUTER JOIN
-- EXECUTION PLAN
-- TABLE ACCESS FULL EMPOR 조건을 UNION으로 변환
아래 쿼리가 변환없이 수행되면 OR 조건으로 인해 FULL TABLE SCAN으로 처리됩니다.
select * from emp
where job = 'clerk' or deptno = 20
만약 인덱스를 사용하고 싶다면 UNION ALL 형태로 변경할 수 있습니다.
SELECT * FROM EMP
WHERE JOB = 'CLERK'
UNION ALL
SELECT * FROM EMP
WHERE DEPTNO = 20
AND LNNVL(JOB = 'CLEAK')
사용자가 직접 쿼리를 변경하지 않아도 옵티마이저가 이런 작업을 대신할때 이를 OR-EXPANSION이라고 합니다.
-- 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)
동일한 테이블에 대한 엑세스가 두 번 일어나기 때문에 중복 엑세스 되는 영역의 데이터 비중이 낮을수록 효과적입니다. 반대의 경우라면 쿼리 수행비용이 오히려 증가합니다. ORACLE 에서 OR-EXPANSION 을 제어하는 힌트로는 USE-CONCAT, NO_EXPAND 가 있습니다. USE_CONCAT 은 OR-EXPANSION을 유도하고 NO_EXPAND 는 이를 방지합니다.
SELECT /*+USE_CONCAT*/ * FROM EMP
WHERE JOB = 'CLEAK' OR DEPTNO = 20
SELECT /*+NO_EXPAND*/ * FROM EMP
WHERE JOB = 'CLEAK' OR DEPTNO = 20기타 쿼리변환
집합연산을 조인으로 변환
INSERT 나 MINUS 같은 집합연산을 조인 형태로 변환해 처리할 수 있습니다. MINUS 연산을 통해 특정 조건의 집합을 제외하는 쿼리에서 옵티마이저가 조인 형태로 변환합니다. HASH ANTI JOIN을 수행하고나서 중복 값을 제거하기 위한 HASH UNIQUE 연산을 수행합니다.
SELECT JOB, MGR FROM EMP
MINUS
SELECT JOB, MGR FROM EMP
WHERE DEPTNO = 10;
-- 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")
다음과 같은 형태로 쿼리변환이 일어났습니다.
SELECT DISTINCT JOB, MRG
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)
)
ORACLE에서 지원하는 SYS_OP_NONNULL 함수는 비공식적인 함수이지만 NULL = NULL 을 true로 처리한다. JOB과 MGR이 NULL 허용 컬럼이기 때문에 이 함수를 이용해 처리합니다.
조인 칼럼에 IS NOT NULL 조건 추가
조인 칼럼에서 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 조건은 사용자가 직접 기술하지 않더라도 옵티마이저가 필요에 의해 내부적으로 추가합니다.
필터 조건 추가
바인드 변수로 BETWEEN 검색하는 쿼리에서 앞 변수가 뒷 변수보다 크면 결과는 항상 공집합입니다.
SELECT * FROM EMP
WHERE SAL BETWEEN :MN AND :MX
-- EXECUTION PLAN
-- 0 SELECT STATEMENT
-- 1 FILTER
-- 2 TABLE ACCESS FULL
-- PREDICATE INFORMATION
-- 1 - FILTER(TO_NUMBER(:MN) <= TO_NUMBER(:MX))
-- 2 - FILTER("EMP"."SAL" >= TO_NUMBER(:MN) AND "EMP"."SAL" <= TO_NUMBER(:MX))
이런 쿼리의 실행을 막기위해 옵티마이저는 임의로 필터조건을 추가해 처리합니다.
조건절 비교 순서
AND 조건의 경우 만족하는 칼럼이 적은 조건에 대한 평가를 먼저하는 것이 성능상 유리합니다. 옵티마이저는 일량을 고려해 선택도가 낮은 칼럼의 조건식 부터 처리하도록 내부적으로 순서를 조정합니다.
