-
쿼리변환 Query TransFormation데이터베이스 2021. 6. 25. 22:09
1. 쿼리변환이란?
2. 서브쿼리 Unnesting
3. 뷰 Merging
4. 조건절 Pushing
5. 조건절 이행
6. 불필요한 조인 제거
7. OR 조건을 Union 으로 변환
8. 기타 쿼리변환
1. 쿼리변환이란?
옵티마이저가 SQL 을 분석해 의미적으로 동일하면서 더 나은 성능이 기대되는 형태로 재작성하는 것을 말한다.
이는 본격적으로 실행계획을 생성하고 비용을 계산하기에 앞서 사용자 SQL 을 최적화에 유리한 형태로 재작성하는 것으로서, DBMS 버전이 올라갈 수록 종류가 더 다양해짐은 물론 더 적극적인 시도가 이루어지고 있다.
비용기반 옵티마이저의 서브 엔진으로 Query TransFormer, Estimator, Plan Generator 가 있다고 했는데 Query TransFormation 가 그런 역할을 담당한다.
쿼리 변환은 다음 두 가지 방식으로 작동한다.
- 휴리스틱 변환 (Heuristic) : 쿼리 결과만 보장된다면 무조건 쿼리 변환을 수행한다.
일종의 규칙 기반 최적화기법이라고 할 수 있으며, 경험적으로 (최소한 동일하거나) 항상 더 나은 성능을 보일 것이라는 옵티마이저 개발팀의 판단이 반영된 것이다.
- 비용기반 변환 : 변환된 쿼리 비용이 더 낮을 때만 그것을 사용하고, 아닐 때는 원본 쿼리를 두고 최적화를 수행한다.
2. 서브쿼리 Unnesting
'서브쿼리 Unnesting'는 중첩된 서브쿼리를 풀어내는 것을 말한다.
서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 매서드를 평가할 수 있다.
특히 옵티마이저는 많은 조인테크닉을 가지기 때문에 조인 형태로 반환했을 때 더 나은 실행계획을 찾을 가능성이 높아진다.
아래는 하나의 쿼리에 서브쿼리가 이중삼중으로 중첩된 것이다.
select * from emp a where exists ( select 'x' from dept where deptno = 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 ) )
위 쿼리와 그림에서 알 수 있듯이 중첩된 서브쿼리는 메인쿼리와 부모와 자식이라는 종속적, 계층적 관계가 존재한다.
따라서 논리적인 관점에서 그 처리과정을 IN, Exists 를 불문하고 필터 방식이어야 한다.
즉 메인 쿼리에서 읽히는 레코드마다 서브쿼리를 반복 수행하면서 조건에 맞지 않는 데이터를 골라내는 것이다.
하지만 서브쿼리를 처리하는 데 있어 필터 방식이 항상 최적의 수행속도를 보장하지 못하므로 옵티마이저는 아래 둘 중 하나를 선택한다.
1. 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화한다. 이를 서브쿼리 Unnesting 이라고 한다.
2. 서브쿼리를 Unnesting 하지 않고 원래대로 둔 상태에서 최적화한다. 메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 각각 최적화를 수행하며, 이때 서브쿼리에 필터 오퍼레이션이 나타난다.
1번 서브쿼리 Unnesting 은 메인과 서브쿼리 간 계층구조를 풀어 서로 같은 레벨(flat한 구조)로 만들어 준다는 의미로 서브쿼리 Flattening 이라고도 한다. 이렇게 쿼리 변환이 이뤄지고 나면 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있게 된다.
2번처럼 쿼리 블록별로 최적화할 때는 각각의 최적이 쿼리문 전체의 최적을 달성하지 못할 때가 많다. 그리고 Plan Generator 가 고려대상으로 삼을만한 다양한 실행계획을 생성해 내는 작업이 매우 제한적인 범위 내에서만 이뤄진다.
아래는 IN 서브쿼리를 포함하는 SQL 을 Unnesting 하지 않고 최적화하는 필터 방식의 실행계획이다.
select * from emp where deptno in (select deptno from dept) ----------------------------- 0 SELECT STATEMENT 1 FILTER 2 TABLE ACCESS FULL | EMP 3 INDEX UNIQUE SCAN | DEPT_PK Predicate Information ----------------------------- 1 - filter (EXIST (SELECT 0 FROM "DEPT" "DEPT " WHERE "DEPTNO"=:B1)) 3 - access ("DEPTNO"=:B1)Predicate 정보를 보면 필터 방식으로 수행된 서브쿼리의 조건절이 바인드 변수로 처리된 부분이 눈에 띈다.(DEPTNO=:B1) 이것을 통해 옵티마이저가 서브쿼리를 별도의 서브플랜으로 최적화 한다는 사실을 알 수 있다.
메인 쿼리도 하나의 쿼리 블록이므로 서브쿼리를 제외한 상태에서 별도로 최적화가 이뤄졌다.(아무 조건이 없으므로 FULL TABLE SCAN 이 최적이다.)
이처럼 Unnesting 하지 않은 서브쿼리를 수행할 때는 메인 쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반복 수행한다. (내부적으로 IN 서브쿼리를 Exists 서브쿼리로 변환한다는 사실도 Predicate 정보에서 알 수 있다.)
위 서브 쿼리가 Unnesting 되면 변환된 쿼리는 아래와 같은 조인문 형태가 된다.
실행계획을 보면 서브쿼리인데도 일반적인 Nested Loop 조인방식으로 수행된 것을 볼 수 있다.
select * from (select deptno from dept) a, emp b where b.deptno = a.deptno; -------------------------------------- 0 SELECT STATEMEN 1 TABLE ACCESS BY INDEX ROWID | EMP 2 NESTED LOOPS 3 INDEX FULL SCAN | DEPT_PK 4 INDEX RANGE SCAN | EMP_DEPTNO_IDX Predicate Information -------------------------------------- 4 - access ("DEPTNO"="DEPTNO")주의할 점은 서브쿼리를 Unnesting 한 결과가 항상 더 나은 성능을 보장하지 않는다는 사실이다.
옵티마이저가 항상 완벽할 순 없으므로 사용자가 직접 이 기능을 제어할 필요성이 생기는데 오라클은 아래 두 힌트를 제공한다.
1. unnest : 서브쿼리를 Unnesting 함으로써 조인방식으로 최적화 유도
2. no_unnest : 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화 유도
* 서브쿼리가 M 쪽 집합이거나 Nonunique 인덱스일 때
지금까지 예제는 메인쿼리의 emp 테이블과 서브쿼리의 dept 테이블이 M:1 관계이기때문에 일반 조인문으로 바꿔도 쿼리 결과가 보장된다.
옵티마이저는 dept 테이블 deptno 컬럼에 PK 제약이 설정된 것을 통해 dept 테이블이 1쪽 집합이라는 것을 알 수 있다. 따라서 안심하고 쿼리 변환을 수행한다.
만약 서브쿼리 쪽 테이블의 조인 컬럼에 PK/Unique 제약 또는 Unique 인덱스가 없다면, 일반 조인문처럼 처리하면 어떻게 되는지 보자.
사례1 )
select * from dept where deptno in (select deptno from emp)위 쿼리는 1쪽 집합을 기준으로 M쪽 집합을 필터링하는 형태이므로 당연히 서브쿼리쪽 emp 테이블 deptno 컬럼에는 Unique 인덱스가 없다. dept 테이블이 기준 집합이므로 결과 집합은 이 테이블의 총 건수를 넘지 않아야 한다.
그런데 옵티마이저가 임의로 아래와 같이 조인문으로 변환하면 M 쪽 집합인 emp 테이블 단위의 결과 집합이 만들어지고 결과 오류가 생긴다.
select * from (select deptno from emp)a , dept b where b.deptno = a.deptno;사례2)
select * from emp where deptno in (select deptno from dept)위 쿼리는 M쪽 집합을 드라이빙해 1쪽 집합을 서브쿼리로 필터링하도록 작성됐으므로 조인문으로 바꾸더라도 결과에 오류가 생기지는 않는다. 하지만 dept 테이블 deptno 컬럼에 PK/Unique 제약이나 Unique 인덱스가 없으면 옵티마이저는 emp 와 dept 간에 관계를 알 수 없고 결과를 확신할 수 없으니 일반 조인문으로의 쿼리 변환을 시도하지 않는다(만약 SQL 튜닝 차원에서 위 쿼리를 사용자가 직접 조인문으로 바꿨는데, 어느 순간 dept 테이블 deptno 컬럼에 중복 값이 입력되면서 결과에 오류가 생기더라도 옵티마이저에게는 책임이 없다).
이럴 때 옵티마이저는 두 가지 방식 중 하나를 선택하는데, Unnesting 후 어느쪽 집합을 먼저 드라이빙 하느냐에 따라 달라진다.
1. 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면, 먼저 sort unique 오퍼레이션을 수행함으로써 1쪽 집합으로 만든 다음에 조인한다.
2. 메인 쿼리쪽 테이블이 드라이빙된다면 세미 조인 방식으로 조인한다. 이것이 세미조인이 탄생하게 된 배경이다.
sort unique 오퍼레이션 방식으로 수행할 때의 실행계획이다.
select * from emp where deptno in (select deptno from dept); ------------------------------------------ 0 SELECT STATEMENT 1 TABLE ACCESS BY INDEX ROWID | EMP 2 NESTED LOOPS 3 SORT UNIQUE 4 INDEX FULL SCAN | DEPT_DEPTNO_IDX 5 INDEX RANGE SCAN | EMP_DEPTNO_IDX Predicate Information (identified by operation id): ------------------------------------------ 5 - access("DEPTNO"="DEPTNO")실제로 DEPT 테이블은 Unique 한 집합이지만 옵티마이저는 이를 확신할 수 없어 sort unique 오퍼레이션을
수행했다. 다음과 같은 형태로 쿼리 변환이 일어난 것이다.select b.* from (select /*+ no_merge */ distinct deptno from deptno order by deptno) a, emp b where b.deptno = a.deptno;아래는 세미 조인 방식으로 수행할 때의 실행계획이다.
select * from emp where deptno in (select deptno from dept) --------------------------------------------- 0 SELECT STATEMENT 1 NESTED LOOPS SEMI 2 TABLE ACCESS FULL | EMP // OUTER 테이블 3 INDEX RANGE SCAN | DEPT_IDX // INNER 테이블 Predicate Information (identified by operation id): --------------------------------------------- 3 - access ("DEPTNO"="DEPTNO")NL 세미 조인으로 수행할 때는 sort unique 오퍼레이션을 수행하지 않고도 결과 집합이 M쪽 집합으로 확장되는 것을 방지하는 알고리즘을 사용한다. 기본적으로 NL 조인과 동일한 프로세스로 진행하지만, Outer(=Driving) 테이블의 한 로우가 Inner 테이블의 한 로우와 조인에 성공하는 순간 진행을 멈추고 Outer 테이블의 다음 로우를 계속 처리하는 방식이다.
아래 psuedo 코드를 참고한다면 어렵지 않게 이해할 수 있다.
for(i=0;;i++){ for(j=0;;j++){ if(i==j) break; } }
3. 뷰 Merging
아래 쿼리1처럼 인라인뷰를 사용하면 쿼리 내용을 파악하기가 더 쉽다. 서브쿼리도 마찬가지다. 서브쿼리로 표현하면 아무래도 조인문보다 더 직관적으로 읽힌다.
쿼리1>
select * from (select * from emp where job='SALESMAN') a ,(select * from dept where loc='CHICAGO') b where a.deptno = b.deptno;그런데 사람의 눈으로 볼 때는 쿼리를 블록화하는 것이 더 읽기 편할지 모르지만, 최적화를 수행하는 옵티마이저의 시각에서는 더 불편하다. 그런 탓에 옵티마이저는 가급적 쿼리2> 처럼 쿼리 블록을 풀어내려는 습성을 갖는다.
쿼리2>
select * from emp a, dept b where a.deptno = b.deptno and a.job = 'SALESMAN' and b.loc = 'CHICAGO'따라서 위에서 본 쿼리1>의 뷰 쿼리 블록은 액세스 쿼리 블록(뷰를 참조하는 쿼리 블록)과의 머지과정을 거쳐
쿼리2> 와 같은 형태로 변환되는데, 이를 '뷰Merging' 이라고 한다. 뷰를 Merging 해야 옵티마이저가 더 다양한 액세스 경로를 조사 대상으로 삼을 수 있게 된다.
다음과 같이 조건절 하나만을 가진 단순한 emp_salesman 뷰가 있다.
create or replace view emp_salesman as select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where job = 'SALEMAN';위 emp_salesman 뷰와 조인하는 간단한 조인문을 작성해보자.
select e.empno, e.ename, e.job, e.mgr, e.sal, e.dname from emp_salesman e, dept d where d.deptno = e.deptno and e.sal >= 1500; ------------------------------------------ Execution Plan ------------------------------------------ 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=156) 1 0 NESTED LOOPS (Cost=3 Card=2 Bytes=156) 2 1 VIEW OF 'EMP_SALESMAN' (VIEW) (Cost=2 Card=2 Bytes=130) 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=2) 4 3 INDEX (RANGE SCAN) OF 'EMP_SAL_IDX' (INDEX) (Cost=1 Card=7) 5 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=13) 6 5 INDEX (UNIQUE SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)뷰 Merging 이 작동한다면 변환된 쿼리는 다음과 같은 모습일 것이다.
select e.empno, e.ename, e.job, e.mgr, e.sal, d.dname from emp e, dept d where d.deptno = e.deptno and e.job = 'SALESMAN' and e.sal >= 1500; ---------------------------------------- Execution Plan ---------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=84) 1 0 NESTED LOOPS (Cost=3 Card=2 Bytes=84) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=2 Bytes=58) 3 2 INDEX (RANGE SCAN) OF 'EMP_SAL_IDX' (INDEX) (Cost=1 Card=7) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=13) 5 4 INDEX (UNIQUE SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)위와 같이 단순한 뷰는 Merging 하더라도 성능이 나빠지지 않는다.
하지만 다음과 같이 복잡한 연산을 포함하는 뷰를 Merging 하면 오히려 성능이 나빠질 수 있다.
* group by 절
* select-list 에 distinct 연산자 포함
따라서 뷰를 Merging 했을 때 쿼리 수행 비용이 더 낮아지는지를 조사한 후에 적용 여부를 판단하는 쪽으로 옵티마이저가 발전하고 있다.
가급적 옵티마이저의 판단과 기능에 의존하는 것이 좋지만 필요시 개발자가 조정할 줄도 알아야한다.
오라클의 경우 이 기능을 제러할 수 있도록 merge 와 no_merge 힌트를 제공한다.
이를 사용하기에 앞서 실행계획을 통해 뷰 Merging 이 발생했는지, 그것이 적정한지 판단하는 능력이 더 중요하다.
아래는 뷰 Merging 이 불가능한 경우인데, 힌트가 제공되지 않을 땐 이런 제약을 활용해 튜닝을 실시하기도 한다.
* 집합(set) 연산자 (union,union all, intersect, minus)
* connect by 절
* ROWNUM pseudo 절
* select-list 에 집계 함수(avg, count, max,min,sum) 사용
* 분석 함수(Analytic Function)
4. 조건절 Pushing
옵티마이저가 뷰를 처리함에 있어 1차적으로 뷰 Merging 을 고려하지만, 조건절(Predicate) Pushing 을 시도할 수도 있다. 이는 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 기능을 말한다.
조건절이 가능한 빨리 처리되도록 뷰 안으로 밀어 넣는다면, 뷰 안에서의 처리 일량을 최소화하게 됨은 물론 리턴되는 결과 건수를 줄임으로써 다음 단계에서 처리해야 할 일량을 줄일 수 있다.
조건절 Pushing 과 관련해 DBMS 가 사용하는 기술로는 다음 3가지가 있다.
- 조건절(Predicate) PushDown : 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽으로 밀어 넣는 것을 말함
- 조건절(Predicate) PullUp : 쿼리 블록 안에 있는 조건절을 쿼리 블록 밖으로 내오는 것을 말하며, 그것을 다시 다른 쿼리 블록에 PushDown 하는 데 사용
- 조인 조건(Join Predicate) PushDown : NL 조인 수행 중에 드라이빙 테이블에서 읽은 값을 건건이 Inner 쪽(=right side) 뷰 쿼리 블록 안으로 밀어 넣는 것을 말함
가. 조건절(Predicate) Pushdown
group by 절을 포함한 아래 뷰를 처리할 때, 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽에 밀어 넣을 수 있다면, group by 해야 할 데이터 양을 줄일 수 있다. 인덱스 상황에 따라서는 더 효과적인 인덱스 선택이 가능해지기도 한다.
select deptno, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno) a -- FROM 절 뒤에.. 인라인 뷰 where deptno = 30 -------------------------------------------------- 0 SELECT STATEMNT 1 VIEW | 2 SORT GROUP BY NOSORT 3 TABLE ACCESS BY INDEX ROWID | EMP 4 INDEX RANGE SCAN | EMP_DEPTNO_IDX -------------------------------------------------- Predicate Information (identified by operation id): -------------------------------------------------- 4 - access("DEPTNO"=30)위 쿼리에 정의한 뷰 내부에는 조건절이 하나도 없다.
만약 쿼리 변환이 작동하지 않는다면, EMP 테이블을 Full Scan하고서 group by 이후에 deptno=30 조건을 필터링했을 것이다. 하지만 조건절 Pushing이 작동함으로써 4번에서 EMP_DEPTNO_IDX 인덱스를 사용한 것을 실행계획에서 볼 수 있다.이번에는 조인문으로 테스트해 보자.
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; -------------------------------------------------- 0 SELECT STATEMNT 1 NESTED LOOPS 2 TABLE ACCESS BY INDEX ROWID | DEPT 3 INDEX UNIQUE SCAN | DEPT_PK 4 VIEW 5 SORT GROUP BY 6 TABLE ACCESS BY INDEX ROWID | EMP 7 INDEX RANGE SCAN | EMP_DEPTNO_IDX -------------------------------------------------- Predicate Information (identified by operation id): -------------------------------------------------- 3 - access("B"."DEPTNO"=30) 7 - access("DEPTNO"=30)위 실행계획과 Predicate Information을 보면, 인라인 뷰에 deptno=30 조건절을 적용해 데이터량을 줄이고서
group by 와 조인연산을 수행한 것을 알 수 있다.
deptno=30 조건이 인라인 뷰에 pushdown 될 수 있었던 이유는, 뒤에서 설명할 "조건절 이행" 쿼리 변환이
먼저 일어났기 때문이다. b.deptno=30 조건이 조인 조건을 타고 a쪽에 전이됨으로써 다음과 같이 a.deptno=30
조건절이 내부적으로 생성된 것이다. 이 상태에서는 a.deptno=30 조건절이 인라인 뷰 안쪽으로 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.deptno=b.deptno and b.deptno=30 and a.deptno=30;나. 조건절 Pullup
조건절(Predicate) 을 쿼리 블록 안으로 밀어 넣을 뿐만 아니라 안쪽에 있는 조건들을 바깥쪽으로 끄집어 내기도
하는데, 이를 '조건절 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; ------------------------------------------------- 0 SELECT STATEMENT 1 HASH JOIN 2 VIEW 3 HASH GROUP BY 4 TABLE ACCESS BY INDEX ROWID | EMP 5 INDEX RANGE SCAN | EMP_DEPTNO_IDX 6 VIEW 7 HASH GROUP BY 8 TABLE ACCESS BY INDEX ROWID | EMP 9 INDEX RANGE SCAN | EMP_DEPTNO_IDX Predicate Information(identified by operation id): ------------------------------------------------- 1 - access("E1"."DEPTNO"="E2"."DEPTNO") 5 - access("DEPTNO"=10) 9 - access("DEPTNO"=10)인라인 뷰 e2에는 deptno=10 조건이 없지만 Predicate 정보를 보면 양쪽 모두 이 조건이 emp_deptno_idx 인덱스의
액세스 조건으로 사용된 것을 볼 수 있다. 다음과 같은 형태로 쿼리 변환이 일어난 것이다.(e2 에 추가된 where 절 )
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
'조인조건(Join Predicate) Pushdown' 은 말 그대로 조인 조건절(E.DEPTNO)을 뷰 쿼리 블록 안(EMP E)으로 밀어 넣는 것으로서,
NL 조인 수행 중에 드라이빙 테이블에서 읽은 조인 컬럼 값을 Inner 쪽(=right side) 뷰 쿼리 블록 내에서 참조할 수 있도록 하는 기능이다.
아래 실행계획에서 group by 절을 포함한 뷰를 액세스하는 단계에서 'VIEW PUSHED PREDICATE'오퍼레이션(id=3) 이 나타났다. 그 아래쪽에 EMP_DEPTNO_IDX 인덱스가 사용된 것을 볼 수 있다. 이는 dept 테이블로부터 넘겨진 deptno 에 대해서만 group by 를 수행함을 의미한다.
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; --------------------------------------------- 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(identified by operation id): -------------------------------------------------- 4 - filter(COUNT(*)>0) 7 - access("DEPTNO"="D"."DEPTNO")여기서는 no_merge 와 push_pred 힌트를 명시했지만, 힌트가 없어도 옵티마이저에 의해 같은 실행계획이 선택될 수 있다. 이 기능은 부분범위처리가 필요한 상황에서 특히 유용한데, Oracle 11g 에 이르러서야 구현됐다.
만약 위 sql 을 10g 이하 버전에서 실행하면, 조인조건 pushdown 이 작동하지 않아 다음과 같이 emp 쪽 인덱스를 Full Scan 하는 결과가 나타난다. dept 테이블에서 읽히는 deptno 마다 emp 테이블 전체를 group by 하므로 성능상 불리한 것이 당연하다.--------------------------------------------- 0 SELECT STATEMENT 1 NESTED LOOPS OUTER 2 TABLE ACCESS FULL | DEPT 3 VIEW 4 SORT GROUP BY 5 TABLE ACCESS BY INDEX ROWID | EMP 6 INDEX FULL SCAN | EMP_DEPTNO_IDX Predicate Information(identified by operation id): -------------------------------------------------- 3 - filter("E"."DEPTNO"(+)="D"."DEPTNO")위 쿼리는 다행히 집계함수가 하나뿐이므로 10g 이하 버전이라도 아래처럼 스칼라 서브 쿼리로 변환함으로써 부분범위처리가 가능하게 할 수 있다.
select d.deptno, d.dname ,(select avg(sal) from emp where deptno=d.deptno) from dept d집계함수가 여러 개일 때가 문제이다. 아래와 같이 쿼리하면 emp 에서 같은 범위를 반복적으로 액세스하는 비효율이 생긴다.
select d.deptno, d.name ,(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 함수로 분리하는 방법을 활용할 수 있다. Oracle 11g 이후로 이렇게 복잡하게 구현할 이유가 없어졌지만, 조인 조건 pushdown 기능을 제공하지 않는 DBMS 를 사용하고 있다면 이런 튜닝 기법이 유용할 수 있다.
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_merger*/ 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 )
5. 조건절 이행
'조건절 이행(Transitive Predicate Generation, Transitive Closure)' 을 한마디로 요약하며,
'(A=B) 이고 (B=C) 이면 (A=C)이다' 라는 추론을 통해 새로운 조건절을 내부적으로 생성해 주는 쿼리 변환이다.
'(A>B)이고 (B>C)이면 (A>C)이다' 와 같은 추론도 가능하다.
예를 들어 A 테이블에 사용된 필터조건이 조인 조건절을 타고, 반대편 B테이블에 대한 필터 조건으로 이행될 수 있다.
한 테이블 내에서도 두 컬럼 간 관계 정보(예를 들어,col1>=col2)를 이용해 조건절이 이행된다.
select * from dpetno, emp e where e.job='MANAGER' and e.deptno = 10 and d.deptno = e.deptno;위 쿼리에서 deptno=10 은 emp 테이블에 대한 필터 조건이다. 하지만 아래 실행계획에 나타나는 Predicate 정보를 확인해보면, dept 테이블에도 같은 필터 조건이 추가된 것을 볼 수 있다.
0 SELECT STATEMENT 1 NESTED LOOPS 2 TABLE ACCESS BY INDEX ROWID 3 INDEX UNIQUE SCAN 4 TABLE ACCESS BY INDEX ROWID 5 INDEX RANGE SCAN Predicate Information(identified by operation id): ----------------------------------------------------- 3 - access("D"."DEPTNO"=10) 5 - access("E"."DEPTNO"=10 AND "E"."JOB"="MANAGER")e.deptno=10 이고 e.deptno=d.deptno 이므로 d.deptno = 10 으로 추론됐다. 이런 조건절 이행(transitive)을 통해 쿼리가 다음과 같은 형태로 변환된 것이다.
select * from dept d, emp e where e.job= 'MANAGER' and e.deptno=10 and d.deptno=10위와 같이 변환한다면, 해시 조인 또는 소트 머지 조인을 수행하기 전에 emp 와 dept 테이블에 각각 필터링을 적용함으로써 조인되는 데이터량을 줄일 수 있다. 그리고 dept 테이블 액세스를 위한 인덱스 사용을 추가로 고려할 수 있게 돼
더 나은 실행계획을 수립할 가능성이 커진다.
6. 불필요한 조인 제거
1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는다면,
쿼리 수행 시 1쪽 테이블은 읽지 않아도 된다. 결과 집합에 영향을 미치지 않기 때문이다. 옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환한다. 이를 '조인 제거(Join Elimination)' 또는 '테이블 제거(Table Elimination)' 라고 한다.
select e.empno, e.ename, e.deptno, e.sal, e.hiredate from dept d, emp e where d.deptno=e.deptno; Rows Row Source Operation -------------------------------------------------------- 14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=58 us)위 쿼리에서 조인 조건식을 제외하며 1쪽 집합인 dept에 대한 참조가 전혀 없다.
따라서 emp테이블만 액세스한 것을 볼 수 있다. 이러한 쿼리 변환이 Oracle의 경우 10g 부터 작동하기 시작했지만
SQL server 등에서는 이미 오래전부터 적용돼 온 기능이다.조인 제거 기능이 작동하려면 다음과 PK 와 FK 제약이 설정돼 있어야만 한다. 이는 옵티마이저가 쿼리 변환을 수행하기 위한 지극히 당연한 조건이다. 만약 PK 가 없으면 두 테이블 간 조인 카디널리티를 파악할 수 없고, FK 가 없으면 조인에
실패하는 레코드가 존재할 수 있어 옵티마이저가 함부로 쿼리 변환을 수행할 수가 없다.
FK 가 설정돼 있더라도 emp 의 deptno 컬럼이 Null 허용 컬럼이면 결과가 다를 수 있다. 조인 컬럼 값이 Null 인 레코드는 조인에 실패해야 정상이다. 옵티마이저가 조인문을 함부로 제거하면 그 레코드들이 결과 집합에 포함되기 때문이다. 이런 오류를 방지하는 위해 옵티마이저가 내부적으로 e.deptno is not null 조건을 추가해 준다.
Outer 조인일 때는 not null 제약이나 is not null 조건은 물론, FK 제약이 없어도 논리적으로 조인 제거가 가능하다.
하지만 Oracle 10g까지는 아래에서 보듯 조인 제거가 일어나지 않았다.
select e.empno, e.name, e.sal, e.hiredate from emp e, dept d where d.deptno(+)=e.deptno -- Outer조인 Rows Row Source Operation ----------------------------------- 15 NESTED LOOPS OUTER (cr=10 pr=0 pw=0 time=119 us) 15 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=225 us) 14 INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=265 us)(Object ID 58557)11g 에서는 다음과 같이 불필요한 Inner 쪽 테이블 제거 기능이 구현된 것을 볼 수 있다.
select e.empno, e.ename, e.sal, e.hiredate from dbo.emp e left outer join dbo.dept d on d.deptno = e.deptno; EMP 테이블.스캔 수 1 , 논리적 읽기 수 2, 물리적 읽기 수 0, 미리 읽기 수 0. Rows StmtText 14 select e.empno, e.ename, e.sal, e.hiredate 14 Clustered Index Scan
7. OR 조건을 Union 으로 변환
아래 쿼리가 그대로 수행된다면 OR 조건이므로 Full Table Scan 으로 처리될 것이다(아니면 job 컬럼 인덱스와 deptno 컬럼 인덱스를 결합하고 비트맵 연산을 통해 테이블 액세스 대상을 필터링하는 Index Combine 이 작동할 수도 있다).
select * from emp where job='CLERK' or deptno=20만약 job과 deptno에 각각 생성된 인덱스를 사용하고 싶다면 다음과 같이 union all 형태로 바꿔주면 된다.
select * from emp where job='CLERK' union all select * from emp where deptno=20 and LNNVL(job='CLERK')사용자가 쿼리를 직접 바꿔주지 않아도 옵티마이저가 이런 작업을 대신해 주는 경우가 있는데, 이를 'OR-Expansion' 이라고 한다. 다음은 OR-Expansion 쿼리 변환이 일어났을 때의 실행계획과 Predicate 정보다.
0 SELECT STATEMENT 1 CONCATENATION 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 EMP_DEPTNO_IDX Predicate Information(identified by operation id): --------------------------------------------------------- 3 - access("JOB"='CLERK') 4 - filter(LNNVL("JOB"='CLERK')) **lnnvl 조건식 안의 조건이 true면 false 리턴 5 - access("DEPTNO"=20)job 과 deptno 컬럼을 선두로 갖는 두 인덱스가 각각 사용됐고, union all 위쪽 브랜치는 job='CLERK' 인 집합을 읽고 아래쪽 브랜치는 deptno=20 인 집합만을 읽는다.
분기된 두 쿼리가 각각 다른 인덱스를 사용하긴 하지만, emp 테이블 액세스가 두 번 일어난다. 따라서 중복 액세스되는 영역(deptno=20 이면서 job='CLERK') 의 데이터 비중이 낮을수록 효과적이고, 그 반대의 경우라면 오히려 쿼리 수행 비용이 증가한다. OR-Expansion 쿼리 변환이 처음부터 비용기반으로 작동한 것도 이 때문이다.
중복 액세스되더라도 결과 집합에는 중복이 없게 하려고 union all 아래쪽에 Oracle 이 내부적으로 LNNVL 함수를 사용한 것을 확인하길 바란다. job<>'CLERK' 이거나 job is null 인 집합만 읽으려는 것이며, 이 함수는 조건식이 false이거나 알수 없는 unknown 값일 때 true 를 리턴한다.
오라클에서 OR-Expansion 을 제어하기 위해 사용하는 힌트는 use_concat 과 no_expand 두 가지가 있다.
use_concat 은 OR-Expansion 을 유도하고자 할 때 사용하고, no_expand 는 이 기능을 방지하고자 할 때 사용한다.
select /*+USE_CONCAT*/ from emp where job='CLERK' or deptno=20; select /*+NO_EXPAND*/ from emp where job='CLERK' or deptno=20;
8. 기타 쿼리변환
가. 집합 연산을 조인으로 변환
Intersect 나 Minus 같은 집합(Set) 연산을 조인 형태로 변환하는 것을 말한다.
아래는 deptno=10 에 속한 사원들의 job, mgr 을 제외시키고 나머지 job, mgr 집합만을 찾는 쿼리이다.
각각 Sort Unique 연산을 수행한 후에 Minus 연산을 수행하는 것을 볼 수 있다.
select job, mgr from emp minus select job, mgr from emp where deptno=10; --------------------------------------- 0 SELECT STATEMENT 1 MINUS 2 SORT UNIQUE 3 TABLE ACCESS FULL | EMP 4 SORT UNIQUE 5 TABLE ACCESS FULL | EMP Predicate Information(identified by operation id): --------------------------------------------------- 5 - filter("DEPTNO"=10)다음은 옵티마이저가 Minus 연산을 조인 형태로 변환했을 때의 실행계획이다.
--------------------------------------- 0 SELECT STATEMENT 1 HASH UNIQUE 2 HASH JOIN ANTI 3 TABLE ACCESS FULL | EMP 4 TABLE ACCESS FULL | EMP Predicate Information(identified by operation id): --------------------------------------------------- 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)해시 Anti 조인을 수행하고 나서 중복 값을 제거하기 위한 Hash Unique 연산을 수행하는 것을 볼 수 있다.
다음과 같은 형태로 쿼리 변환이 일어난 것이다.select distinct job, mgr from emp e where not exist ( 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_p_map_nonnull 함수는 비공식적인 함수지만 가끔 유용하게 사용할 수 있다.
null 값끼리 '=' 비교 (null=null) 하면 false이지만 true 가 되도록 처리해야 하는 경우가 있고, 그럴 때 이 함수를 사용하면 된다. 위에서는 job 과 mgr 이 null 허용 컬럼이기 때문에 위와 같은 처리가 일어났다.
나. 조인 컬럼에 IS NOT NULL 조건 추가
select count(e.empno), count(d.dname) from emp e, dept d where d.deptno = e.deptno and sal <= 2900;위에 같은 조인문을 처리할 때 조인 컬럼 deptno 가 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;다. 필터 조건 추가
다음과 같이 바인드 변수로 between 검색하는 쿼리가 있을 때, 쿼리를 수행할 대 사용자가 :mx 보다 :mn 변수에 더 큰 값을 입력한다면 쿼리 결과는 공집합이다.
select * from emp where sal between :mn and :mx사전에 두 값을 비교해 알 수 있음에도 쿼리를 실제 수행하고서야 공집합을 출력한다면 매우 비합리적이다.
Oracle 9i 부터 이를 방지하기 위해 옵티마이저가 임의로 필터 조건식을 추가해준다. 아래 실행계획에서 1번 오퍼레이션 단계에 사용된 Filter Predicate 정보를 확인해보자.
0 SELECT STATEMENT 1 FILTER 2 TABLE ACCESS FULL | EMP Predicate Information ------------------------------------- 1 - filter(TO_NUMBER(:MN)<=TO_NUMBER(:MX)) 2 - filter("EMP"."SAL">=TO_NUMBER(:MN) AND "EMP"."SAL"<=TO_NUMBER(:MX))아래는 mn 에 500 mx에 100울 입력하고 실제 수행했을 때 결과인데 블록 I/O 가 전혀 발생하지 않은 것을 볼 수 있다.
실행계획 상으로는 Table Full Scan 을 수행하고 나서 필터 처리가 일어나는 것 같지만, 실제로는 Table Full Scan 자체를 생략한 것이다.
0 recursive calls 0 db block gets 0 consistent gets 0 physical reads라. 조건절 비교 순서
위 데이터를 아래 SQL 문으로 검색하면 B컬럼에 대한 조건식을 먼저 평가하는 것이 유리하다.
왜냐하면 대부분 레코드가 B=1000 조건을 만족하지 않아 A 컬럼에 대한 비교 연산을 수행하지 않아도 되기 때문이다.
select * from T where A=1 and B=1000;반대로 A=1 조건식을 먼저 평가한다면, A컬럼이 대부분 1이어서 B 컬럼에 대한 비교 연산까지 그만큼 더 수행해야 하므로 CPU 사용량이 늘어날 것이다.
다음과 같은 조건절을 처리할 때도 부등호(>) 조건을 먼저 평가하느냐 like 조건을 먼저 평가하느냐에 따라 일량에 차이가 생긴다.
select /*+full(도서)*/ 도서번호,도서명,가격,저자,출판사,isbn from 도서 where 도서명 like '데이터베이스%' -- 사용자 입력 키워드 and 도서명 > '데이터베이스성능고도화' -- 앞 페이지 화면에서 출력한 가장 마지막 도서명DBMS 또는 버전에 따라 다르지만, 예전 옵티마이저는 where 절에 기술된 순서 또는 반대 순서로 처리하는 내부 규칙을 따름으로써 비효율을 야기하곤 했다.
하지만 최신 옵티마이저는 비교 연산해야 할 일량을 고려해 선택도가 낮은 컬럼의 조건식부터 처리하도록 내부적으로 순서를 조정한다.
출처
SQL 전문가 가이드 - 한국데이터산업진흥원
반응형'데이터베이스' 카테고리의 다른 글
데이터베이스 Call 최소화와 고급 SQL 활용 (0) 2021.07.03 소트 튜닝 (0) 2021.06.29 SQL 공유 및 재사용 (0) 2021.06.22 옵티마이저 (0) 2021.06.21 고급 조인 기법 (0) 2021.06.20