쿼리 변환(Query Transformation)은 옵티마이저가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 말한다. 이는 Query Transformer가 담당한다.
- 휴리스틱 쿼리 변환 : 결과만 보장된다면 무조건 쿼리 변환을 수행한다.
- 비용기반 쿼리 변환 : 변환된 쿼리의 비용이 더 낮을 때만 그것을 사용하고, 그렇지 않을 때는 원본 쿼리 그대로 두고 최적화를 수행한다.
서브쿼리 Unnesting
서브쿼리 Unnesting은 중첩된 쿼리(Nested Subquery)를 풀어내는 것을 말한다. 중첩된 서브쿼리(nested subquery)는 메인쿼리와 부모와 자식이라는 종속적이고 계층적인 관계가 존재한다. 따라서 논리적인 관점에서 그 처리 과정은 IN, EXISTS를 불문하고 필터 방식이어야 한다. 즉, 메인쿼리에서 읽히는 레코드마다 서브쿼리를 반복 수행하면서 조건에 맞지 않는 데이터를 골라내는 것이다.
하지만 서브쿼리를 처리하는데 있어 필터 방식이 항상 최적의 수행속도를 보장하지 못하므로 옵티마이저는 둘 중 하나를 선택한다.
1) 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화한다. 이를 일컬어 '서브쿼리 Unnesting'이라고 한다. ('서브쿼리 Flattening'이라고도 부른다.)
2) 서브쿼리를 Unnesting하지 않고 원래대로 둔 상태에서 최적화한다. 메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 각각 최적화를 수행하며, 이때 서브쿼리에 필터 오퍼레이션이 나타난다.
ex) select * from emp where deptno in (select /*+ no_unnest */ deptno from dept);
Predicate 정보를 보면 필터 방식으로 수행된 서브쿼리의 조건절이 바인드 변수로 처리된 부분("DEPTNO" = :B1)이 눈에 띄는데, 이를 통해 옵티마이저가 서브쿼리를 별도의 서브플랜으로 최적화한다는 사실을 알 수 있다. Unnesting하지 않은 서브쿼리를 수행할 떄는 메인 쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반복 수행한다.
이제 위의 쿼리를 Unnesting 해보자. (View Merging)
select e.* from emp e, dept d where e.deptno = d.deptno;
<시행착오>
select e.* from emp e, dept d where e.deptno = d.deptno;
직접 예제를 돌려보았을 때 위의 결과대로 나와야 하는데 아래와 같이 나와서 당황했다.
분명 인덱스를 통해 접근하든 테이블 액세스를 하든 dept에 대한 액세스가 있어야 조인이 가능할텐데... 저렇게 emp에 대한 테이블 액세스만 실행계획에 나왔다. 도대체 어떻게 아무런 dept에 대한 액세스 없이 결과가 나오지...?
알고보니 내가 emp와 dept 사이에 FK를 걸어놨었다. dept의 deptno를 PK로 두고 emp의 deptno가 dept의 deptno를 참조하는 방향으로 말이다. 당연히 emp의 deptno는 dept의 deptno에 포함되어 있으니 옵티마이저가 굳이 dept 테이블에 접근할 이유가 없었던 것이다.
주의할 점은, 서브쿼리를 Unnesting한 결과가 항상 더 나은 성능을 보장하지 않는다는 사실이다. 따라서 성능에 대한 판단은 기본적으로 맡기는 것이 바람직하지만, 옵티마이저가 항상 완벽할 수는 없으므로 사용자가 직접 이 기능을 제어할 필요성이 생긴다.
unnest : 서브쿼리를 Unnesting 함으로써 조인방식으로 최적화하도록 유도한다.
no_unnest : 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화하도록 유도한다.
서브쿼리가 M쪽 집합이거나 Nonunique 인덱스일 때는 어떻게 될까?
1. 서브쿼리가 M쪽 집합인 경우
select * from dept where deptno in (select deptno from emp);
in 특성 상 dept의 deptno와 같은 deptno를 emp에서 찾더라도 끝까지 탐색하는 경향이 있다. 따라서 결과는 emp의 행 수만큼 나온다.
select d.* from dept d, emp e where d.deptno = e.deptno;
이렇게 쿼리를 바꿔버리면 dept의 행 수만큼 결과가 나올 것이다. 따라서 위의 쿼리의 결과와 달라져 버린다.
2. 서브쿼리 내의 테이블 컬럼에 PK/Unique 제약이나 Unique 제약이 없을 경우
select * from emp where deptno in (select deptno from dept);
이 경우 일반 조인문으로 쿼리 변환을 해도 결과에는 오류가 생기지 않는다. 하지만 dept 테이블의 deptno 컬럼에 PK/Unique 제약이나 Unique 인덱스가 없으면 옵티마이저는 emp와 dept 간의 관계를 알 수 없고, 결과를 확신할 수 없으니 일반 조인문으로의 쿼리 변환을 시도하지 않는다.
(이 상태에서는 dept 테이블에 중복된 deptno가 들어가도 할말이 없다. 따라서 Unnest 했을 때와 하지 않았을 때 결과가 달라질 수 있는데 이 경우 옵티마이저에게 책임이 없다.)
이럴 때 옵티마이저는 두 가지 방식 중 하나를 선택하는데, Unnesting 후 어느 쪽 집합을 먼저 드라이빙 하느냐에 따라 달라진다.
- 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면, 먼저 sort unique 오퍼레이션을 수행함으로써 1쪽 집합으로 만든 다음에 조인한다.
ex) select b.* from (select /*+ no_merge */ distinct deptno from dept order by deptno) a, emp b where b.deptno = a.deptno; - 메인 쿼리 쪽 테이블이 드라이빙된다면 세미 조인 방식으로 조인한다.
ex) select * from emp where deptno in (select deptno from dept);
NL 세미조인으로 수행할 때는 sort unique 오퍼레이션을 수행하지 않고도 결과집합이 M쪽 집합으로 확장되는 것을 방지하는 알고리즘을 사용한다. 기본적으로 NL Join과 동일한 프로세스로 진행하지만, Outer 테이블의 한 로우가 Inner 테이블의 한 로우와 조인에 성공하는 순간 진행을 멈추고 Outer 테이블의 다음 로우를 계속 처리하는 방식이다.
뷰 Merging
뷰 Merging
: 인라인 뷰, 서브쿼리를 해석할 때 독자적으로 실행하지 않고 메인쿼리와 함께 실행되는 경우 쿼리 블록을 풀어서 기존 쿼리와 함께 최적화를 수행하는 것.
뷰 쿼리 블록은 액세스 쿼리 블록(뷰를 참조하는 쿼리 블록)과의 머지 과정을 거친다.
1) 사람 관점에서 서브쿼리로 표현하면 조인문보다 더 직관적으로 읽을 수 있다.
select *
from (select *
from emp
where job = 'SALESMAN') a ,
(select *
from dept
where loc = 'CHICAGO') b
where a.deptno = b.deptno
2) 반면, 옵티마이저는 쿼리 블록을 풀어내려는 습성이 있다.
select *
from emp, dept
where emp.deptno = dept.deptno and emp.job = 'SALESMAN' and dept.loc = 'CHICAGO';단순한 뷰는 Merging 하더라도 성능이 나빠지지 않는다.
create or replace view emp_salesman as select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where job = 'SALESMAN'
select e.empno, e.ename, e.job, e.mgr, e.sal, d.dname from emp_salesman e, dept d where d.deptno = e.deptno and e.sal >= 1500;뷰 Merging 했을 때
뷰 Merging 하지 않았을 때
하지만 group by절이나 select-list에 distinct 연산자를 포함한 절은 뷰 Merging 시 오히려 성능이 나빠질 수 있다.
뷰 Merging이 불가능한 경우
- 집합 연산자(union, union all, intersect, minus)
- connect by절
- ROWNUM pseudo절
- select-list에 집계함수(avg, count, max, min, sum) 사용
- 분석함수
조건절 Pushing
옵티마이저가 뷰를 처리함에 있어 1차적으로 뷰 Merging을 고려하지만, 조건절 Pushing을 시도할 수도 있다. 이는 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 밀어넣는 기능을 말한다.
조건절 Pushing과 관련해 DBMS가 사용하는 기술
- 조건절 Pushdown : 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽으로 밀어넣는 것을 말함.
- 조건절 Pullup : 쿼리 블록 안에 있는 조건절을 쿼리 블록 밖으로 내오는 것을 말하며, 그것을 다시 다른 쿼리 블록에 Pushdown 하는데 사용함.
- 조인 조건(Join Predicate) Pushdown : NL Join 수행 중에 드라이빙 테이블에서 읽은 값을 건건이 Inner쪽 뷰 쿼리 블록 안으로 밀어넣는 것
조건절 Pushdown
group by절을 포함한 뷰를 처리할 때, 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽에 밀어넣을 수 있다면 group by 해야할 데이터량을 줄일 수 있다.
테이블 1개에 대해서
select deptno, avg_sal
from (select deptno, avg(sal) avg_sal
from emp
group by deptno) a
where deptno = 30;
만약 쿼리 변환이 일어나지 않는다면, emp 테이블을 Full Scan하고서 group by 이후에 deptno=30을 필터링했을 것이다.
두 테이블을 조인하는 경우
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.deptno = b.deptno and b.deptno = 30;
6 - access("DEPTNO"=30)을 통해 뷰에서 데이터량을 줄이고 나서 group by와 조인 연산을 수행한 것을 알 수 있다. b.deptno = 30 조건이 조인 조건을 타고 a쪽으로 전이됨으로써 a.deptno = 30이 내부적으로 생기는 '조건절 이행'이 발생하였다.
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.deptno = b.deptno and b.deptno = 30 and a.deptno = 30;이렇게 a.deptno = 30이 생긴 후 이 조건절이 인라인 뷰 안쪾으로 Pushing된 것이다.
조건절 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;
기존 쿼리에는 e2 인라인 뷰에 deptno에 대한 조건절이 없다. 하지만 7 - access("DEPTNO"=10)에서 e2 인라인 뷰에 조건절이 추가된 것을 볼 수 있다. 이는 '조건절 이행'이 발생하여 e1 인라인 뷰의 deptno = 10을 끄집어 내어 조인 조건에 의해 e2.deptno = 10이 내부적으로 생성된 후 e2 인라인 뷰에 Pushing된 것이다.
따라서 이 쿼리는 다음과 같다.
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 Join 수행 중에 드라이빙 테이블에서 읽은 조인 컬럼 값을 Inner쪽 뷰 쿼리 블록 내에서 참조할 수 있도록 하는 기능이다.
조건절 이행
A=B이고 B=C이면 A=C이다. 라는 추론을 통해 새로운 조건절을 내부적으로 생성해주는 쿼리변환이다.
select *
from dept d, emp e
where e.job = 'MANAGER' and e.deptno = 10 and d.deptno = e.deptno;
- e.deptno = 10이다.
- e.deptno = d.deptno다.
- 따라서 d.deptno 역시 10이다.
select *
from dept d, emp e
where e.job = 'MANAGER' and e.deptno = 10 and d.deptno = 10;쿼리가 이렇게 변환되어 Join을 수행하기 전에 emp와 dept 테이블에 각각 필터링을 적용함으로써 조인되는 데이터량을 줄일 수 있다. 또한, dept 테이블 액세스를 위한 인덱스 사용을 추가로 고려할 수 있게 돼 더 나은 실행계획을 수립할 가능성이 커진다.
불필요한 조인 제거
1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는다면, 쿼리수행 시 1쪽 테이블은 결과 집합에 영향을 미치지 않으므로 읽지 않아도 된다. 옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환하는데, 이를 '조인 제거' 또는 '테이블 제거'라고 한다.
select e.empno, e.ename, e.deptno, e.sal, e.hiredate
from dept d, emp e
where d.deptno = e.deptno;이 코드에서 dept 테이블은 조인 조건식에서밖에 사용하지 않는다.
만약 d.deptno가 dept의 PK고 e.deptno가 emp에서 d.deptno에 대한 FK라면 굳이 저 조인 조건이 필요 없을 것 같다. e.deptno가 FK면 모두 d.deptno중 하나로 이루어져 있기 때문이다.
조인 제거 기능이 작동하려면 PK와 FK 제약이 반드시 설정돼있어야 한다. 만약 PK가 없으면 두 테이블 간 조인 카디널리티를 파악할 수 없고, FK가 없으면 조인에 실패하는 레코드가 존재할 수도 있어 옵티마이저가 함부로 쿼리 변환을 수행할 수 없다.
(+ PK제약이 없다는 것은 값의 중복을 허용한다는 것인데 그 상태에서 조인 조건을 없애버리면 결과가 아예 다르게 나올 것이다.)
FK가 설정되었어도 emp의 deptno 컬럼이 null 허용 컬럼이면 올바른 결과가 나오지 않을 수 있다. 위의 구문을 통해 조인 시 null은 결과에 반영하지 않을텐데 함부로 조인 조건을 없애버리면 null도 결과에 포함될 것이기 때문이다. 이런 오류를 방지하기 위해 옵티마이저가 내부적으로 e.deptno is not null 조건을 추가해준다.
OR 조건을 Union으로 변환
select * from emp where job = 'CLERK' or deptno = 20;이 쿼리가 그대로 실행된다면 Full Table Scan으로 실행된다.
아니면 job 컬럼 인덱스와 deptno 컬럼 인덱스를 결합하고 비트맵 연산을 통해 테이블 액세스 대상을 필터링하는 Index Combine이 작동할 수도 있다.
job과 deptno 각각에 대한 인덱스를 사용하고 싶다면...
select *
from emp
where job = 'CLERK'
union all
select *
from emp
where deptno = 20 and LNNVL(job='CLERK');이렇게 작성하면 된다. 대신 emp 테이블에 두 번 액세스하기 때문에 job이 'CLERK'이면서 deptno가 20인 데이터 비중이 작을수록 효과적이며 오히려 그 반대의 경우 쿼리 수행 비용이 증가한다.
사용자가 쿼리를 직접 바꿔주지 않아도 옵티마이저가 이런 작업을 대신해주는 경우가 있는데, 이를 'OR-Expansion'이라고 한다.
Oracle에서 Or-Expansion을 제어하기 위해 사용하는 힌트로는 use_concat과 no_expand가 있다.
use_concat : Or-Expansion 유도
no_expand : Or-Expansion 방지
(데이터를 많이 넣지 않은 상태로 돌려서 USE_CONCAT를 사용해도 각각 Table Full Scan으로 실행된 것 같다... 데이터가 많고 조건절에 해당하는 데이터가 적다면 인덱스 스캔이 더 유리하다.)
기타 쿼리 변환
집합 연산을 조인으로 변환
select job, mgr
from emp
minus
select job,mgr
from emp
where deptno = 10;
지금 deptno = 10에 속한 사원들의 job, mgr을 제외시키고 나머지 job과 mgr 집합만을 찾는데 각각 Sort Unique 연산을 수행한 후 Minus 연산을 수행하는 것을 볼 수 있다.
이를 Join 형태로 쿼리를 변환하면...
select distinct 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)
); 
sys_op_map_nonnull 함수는 null값끼리 '=' 비교(null = null) 하면 false이지만 true가 되도록 처리해야 하는 경우가 있고, 그럴 때 이 함수를 사용하면 된다.
조인 컬럼에 IS NOT NULL 조건 추가
조인컬럼이 null인 경우 어차피 조인에 실패하기 때문에 필터 조건을 추가해주면 불필요한 테이블 액세스 및 조인 시도를 줄일 수 있어 쿼리 성능 향상에 도움이 된다.
is not null 조건을 사용자가 직접 기술하지 않더라도, 옵티마이저가 필요하다고 판단되면 내부적으로 추가해준다.
(Oracle의 경우 null값 비중이 5% 이상일 때 해당된다.)
필터 조건 추가
select * from emp where sal between :mn and :mx;이 경우 쿼리를 수행할 때 사용자가 :mx보다 :mn 변수에 더 큰 값을 입력한다면 쿼리 결과는 공집합이다.
사전에 두 값을 비교해 알 수 있음에도 불구하고 쿼리를 실제 수행하고 나서 공집합을 출력한다면 매우 비합리적이다. 이를 방지하기 위해 옵티마이저가 임의로 필터 조건식을 추가해준다.
블록 I/O가 전혀 발생하지 않았으며 실행계획 상으로는 Table Full Scan을 수행하고 나서 필터 처리가 일어나는 것 같지만 실제로는 Table Full Scan 자체를 생략한 것이다.
조건절 비교 순서
DBMS 또는 버전에 따라 다르지만, 예전 옵티마이저는 where절에 기술된 순서 또는 반대 순서로 처리하는 내부 규칙을 따름으로써 비효율을 야기하고는 했다. 하지만, 최신 옵티마이저는 비교 연산해야 할 일량을 고려해 선택도가 낮은 컬럼의 조건식부터 처리하도록 내부적으로 순서를 조정한다.
