<오라클 성능 고도화 원리와 해법2> Ch04-07 OR-Expansion
오라클 성능 고도화 원리와 해법2 - Ch04-07 OR-Expansion
(1) OR- Expansion 기본
만약 아래 쿼리가 그대로 수행된다면 OR 조건 때문에 Full Table Scan으로 처리될 것이다. 아니면 Index Combine(1장3절 참조)이 작동할 수도 있다(스크립트 Ch4 19.kt 참조).
1
2
select * from emp
where job = 'CLERK' or deptno = 20
만약 job과 deptno에 각각 생성된 인덱스를 사용하고 싶다면 아래와 같이 union all 형태로 변경해주면 된다.
1
2
3
4
5
6
select *
from emp
where job='CLERK' union all
select * from emp
where deptno = 20
and NVL(job,'CLERK') != 'CLERK'
사용자가 쿼리를 직접 변경해주지 않아도 옵티마이저가 그런 작업을 대신해 주는 경우가 있는데, 이를 ‘OR-Expansion’ 이라고 한다.
분기된 두 쿼리가 각각 다른 인덱스를 사용하긴 하지만, emp 테이블 액세스가 두번 일어난다. 따라서 중복 액세스 되는 영역(deptno = 20 이면서 job = ‘CLERK’)의 데이터 비중이 작을수록 효과적이고, 그 반대의 경우라면 오히려 쿼리 수행 비용이 증가한다. OR-Expansion 쿼리 변환이 처음부터 비용기반으로 작동한 것도 이 때문이다.
중복 액세스되더라도 결과 집합에는 중복이 없게 하려고 union all 아래쪽에 오라클이 내부적으로 NVL함수를 사용한 것을 확인하기 바란다. job <> ‘CLERK’ 이거나 job이 null 인 집합만을 읽으려는 것이며, 이 함수는 조건식이 false 이거나 알 수 없는(Unknown) 값일 때 true 를 반환한다.
OR-Expansion을 제어하기 위해 사용하는 힌트로는 use concat 과 no_expand 두 가지가 있다. use concat은 OR-Expansion을 유도하고자 할 때 사용하고, no_expand는 이 기능을 방지하고자 할 때 사용한다.
1
2
select /*+ USE CONCAT */ * from emp where job = 'CLERK' or deptno = 20;
select /*+ NO_EXPAND */ * from emp where job = 'CLERK' or deptno = 20;
OR-Expansion 기능을 아예 작동하지 못하도록 막으려면 아래와 같이 _no_or_expansion 파라미터를 true로 설정하면 되고, 그럴 경우 use concat 힌트를 사용하더라도 OR-Expansion이 일어나지 않는다. 기본값은 false다.
1
alter session set "_no_or_expansion" = true;
(2) OR- Expansion 브랜치별 조인 순서 최적화
OR-Expansion에 의해 분기된 브랜치마다 각기 다른 조인 순서를 가질 수 있음은 매우 중요한 사실이다.
여기서도 두 쿼리의 교집합이든 번 출력되는 것을 방지하려고 NVL 함수가 사용되었다. (실제 위와 같은 실행 계획이 나오지 않으면 스크립트 파일에 있는 힌트를 써서 유도할 수 있다.)
(3) 같은 컬럼에 대한 OR-Expansion
아래 두 쿼리는 내용적으로 100% 같은 쿼리다(스크립트 Ch4_21.kt 참조).
< 쿼리그 >
1
2
3
select * from emp
where (deptno = 10 or deptno = 30) and
ename = :ename
< 쿼리? >
1
2
select * from emo
where deptno in (10, 30) and ename = :ename
따라서 이들 쿼리도 아래와 같이 OR-Expansion 처리가 가능하다.
실제로 9i까지는 위와 같은 형태 즉, 같은 컬럼에 대한 OR 조건이나 IN-List도 OR-Expansion이 작동할 수 있었지만 10g부터는 기본적으로 아래와 같이 IN-List Iterator 방식으로만 처리된다.
만약 억지로 OR-Expansion으로 유도하려면 use_concat 힌트에 아래와 같은 인자를 제공하면 되지만, IN-List Iterator에 비해 나은 점이 없으므로 굳이 그렇게 할 이유는 없다.
(4) NVL/DECODE 조건식에 대한 OR-Expansion
1권 4장에서도 설명했듯이 사용자가 선택적으로 입력하는 조건에 대해 nvl 또는 decode 함수를 이용할 수 있다. 아래 쿼리는 deptno 검색 조건을 사용자가 선택적으로 입력할 수 있는 경우에 대비한 것이다(스크립트 Ch4.22.0k 참조).
1
2
select * from emp
where deptno = nvl(:deptno, deptno) and ename like :ename || '%'
오라클 9부터 쿼리를 위와 같이 작성하면, 아래와 같은 형태로 OR-Expansion 쿼리 변환이 일어난다.
1
2
3
4
5
6
7
8
select * from emp
where
:deptno is null
and
deptno is not null
and
ename like ename || '%' union all
select * from emo
무엇보다 중요한 것은 :deptno 변수값 입력 여부에 따라 다른 인덱스를 사용한다는 사실이다.
이 유용한 기능을 제어하는 파라미터는 orexpandnullpredicate이다. 오래 전부터 SQL 튜너들이 union all을 이용해 위와 같은 튜닝 기법을 많이 사용해 왔는데, 이제는 옵티마이저가 스스로 처리를 함으로써 많이 편리해진 것은 사실이다. 하지만 NVL 또는 decode를 여러 컬럼에 대해 사용했을 때는 그 중 변별력이 가장 좋은 컬럼 기준으로 한번만(분기가 일어난다. 옵션 조건이 복잡할 때는 이 방식에만 의존하기 어려운 이유가 여기에 있고, 그럴 때는 여전히 수동으로 union all 분기를 해줘야만 한다.