새로쓴 대용량 데이터베이스솔루션 1 (2008년)
3.1.4. 질의의 변환(Query Transforming)
{section}{column:width=300}
옵티마이져는 가능한 모든 수식의 값을 미리 구한다.
상수의 연산을 실행될 때마다 이루어지는 것이 아니라 *질의 변환단계에서 한 번만 수행*한다.
예)
① sales_qty > 1200/12
② sales_qty > 100
③ sales_qty*12 > 1200
=> ①,② 은 동일하게 취급이된다. 왜냐하면, 사전에 최대한의 연산을 하여 두 번째 조건식이 된다.
=> ③은 연산을 하여 ①,② 조건식을 만들지 않는다. 왜) 비교연산자의 추가적 연산은 연산자를 좌우로 이동시켜
단순화를 하지는 않기 때문이다.
단순화 시키는 문장
① job like 'SALESMAN'
② job = 'SALESMAN'
=> LIKE를 사용한 조건절에서 '%' 나 '_'를 사용하지 않는 경우에는 '=' 연산으로 *조건절로 단순화* 시킨다.
=> 가변길이 데이터타입일 때만 이러한 수식의 단순화가 가능하다.
조건절로 확장,변환시킨 문장
- 조건식에 IN, OR로 이용한 문장
① job IN ('CLERK','MANAGER')
② job = 'CLERK' OR job = 'MANAGER'
=> IN 비교 연산자를 사용한 문장에서는 OR 논리 연산자를 이용해 여러개의 '='로 된 같은기능의 조건절로 확장시킨다.
- 조건식에 ANY, SOME 로 이용한 문장
① sales_qty > ANY (:in_qty1, :in_qty2)
② sales_qty > :in_qty1 OR sales_qty > :in_qty2
=> 인반적인 조건식이라면 '=' 비교연산자와 OR논리 연산자를 이용해 같은 기능의 조건절로 확장시킬수있다.
- 조건식에 ALL 로 이용한 문장
① sales_qty > ALL (:in_qty1, :in_qty2)
② sales_qty > :in_qty1 AND sales_qty > :in_qty2
=> 조건절은 '=' 연산자와 AND 논리 연산자를 사용해 같은 기능의 조건절로 변환 시킨다.
- 조건식에 BETWEEN 를 이용한 문장
① sales_qty BETWEEN 100 and 200
② sales_qty >= 100 AND sales_qty <= 200
=> 비교연산자를 사용한 조건식은 '>=', '<=' 비교 연산자를 사용하여 변환된다.
- 조건식에 NOT를 이용한 문장
① NOT (sal < 30000 or comm is null)
② NOT sal < 30000 and comm is NOT null)
③ sal >= 30000 and comm is NOT null
=> 논리 연산자를 제거할 수 있는 반대 비교연산을 찾아 대체시키는 변환을 한다.
=> ① → ② → ③ 식으로 변환을 한다.
조건절에 서브쿼리사용 한 경우
- 조건식에 ANY, SOME 로 이용한 문장
① where 100000 > ANY (select sal from emp where job = 'CLERK')
② where EXISTS (select sal from emp where job = 'CLERK' and 100000 > sal)
=> EXISTS 연산자를 이용한 서브쿼리로 변환될 수 있다.
- 조건식에 ALL 로 이용한 문장
① where 100000 > ALL (select sal from emp where job = 'CLERK')
② where NOT (100000 <= ALL (select sal from emp where job = 'CLERK'))
③ where NOT EXISTS (select sal from emp where job = 'CLERK' and 100000 <= sal)
=> NOT ANY로 변환한 후에 다시 EXISTS 연산자를 이용하여 서브쿼리로 변환 될수있다.
- 조건식에 NOT를 이용한 문장
① NOT deptno = ( select deptno from emp where empno = 7689)
② deptno <> (select deptno from emp where empno = 7689)
=> NOT를 없애기 위해 반대 비교연산을 찾아 대체시키는 변환을 한다.
{column}{section}
3.1.4.1 이행성규칙(Transitivity principle)
{section}{column:width=300}
조건절에 같은 컬럼을 사용한 두 개의 조건식이 있다면 옵티마이져는 새로운 조건식을 이행성 규칙에 따라 생성하고
이 조건식을 이용하여 최적화를 수행한다.
원래의 조건식에서 인덱스를 사용하지 못하게 되더라도 인덱스를 사용하는 실행게획이 나타날 수도 있다.
----------------------------------------------------------------------------------------------------
가정.
where COLUMN1 comparison_operators constant
and COLUMN1 = COLUMN2
※ comparison_operators : =, !=, ^=, <, <>, >, <=, >= 중의 하나
constant : 연산, SQL함수, 문자열, 바인ㄷ 변수, 상관관계 변수를 포함하는 상수 수식
=> 3단 논법 A=B, B=C ,A=C 라는 원칙이 적용하듯이 옵티마이져도 같은 조건절을 추론한다.
COLUMN2 comparison_operators constant -----> 이것은 비용기준 옵티마이져일때만 사용가능하다.
- WHERE 절에 OR로 연결된 두개의 조건절이 있을 때 발생할 수 있는 경우
-----------------------------------------------------------------------------------------------------
※ 이행성규칙은 효율적이라고 판된될 때만 변환이 일어난다.
■ OR를 사용한 조건절을 분기시켰을 때 각각이 인덱스 접근 경로로 이용할 수 있다면 옵티마이져는 변환을 한다.
■ UNION ALL에 의해 각각의 인덱스를 경유하는 실행계획을 선택하고 나중에 결합을 한다.
■ 실행계획에서는 'IN-LIST ITERATOR' 나 'CONCATENATION'이라는 표시가 나타난다.
■ 조건절에 인덱스를 사용할수 없고 전체 테이블을 스캔한다고하면 OR를 사용한 조건절은 그 문장에 대한 변환을 하지않는다.
■ 변환을 못할경우 강제적으로 하는 힌트 'USE_CONCAT'사용한다.
select * from emp e, dept d where e.deptno = 20 and e.deptno = d.deptno;
=> 이행성규칙을 적용하여 d.deptno = 20을 추론할수있다
- 이행성 규칙이 일어나지 않는경우
예) where COLUMN1 comparion_operators COLUMN3
and COLUMN1 = COLUMN2
=> 비교하는 대상이 상수 수식이 아닌 컬럼인 경우는 이행이 일어나지 않는다.
=> COLUMN1 comparion_operators COLUMN3 DMS CNFHSGKWL DKSGSMSEK.
- OR를 가지는 조건들을 UNION ALL을 사용한 복합 문장으로 변환하는경우
select * from emp where job = 'CLERK' OR deptno = 10 ;
=> JOB과 DEPTNO 컬럼에 각각 인덱스가 존재한다면 옵티마이져는 위의 쿼리를 아래와 같은 쿼리로 변환한다.
select * from emp where job = 'CLERK'
UNION ALL
SELECT * from emp where deptno = 10 and job <> 'CLERK';
=> 이러한 현상은 우리가 IN을 사용한 상수수식 비교에서도 유사하게 나타난다. 그 이유는 이러한 형태의 IN은 OR로 변환되기 때문이다
※ 서브쿼리를 가진 복잡한 문장을 조인문장으로 변환하는경우 최적화하기 위해서 두가지 방법이있다.
① 이들을 같은 기능의 조인 문장으로 변환하여 다시이를 최적화하는 방법
② 현재의 문장을 그대로 최적화하는방법
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'NEW YORK');
=> 만약 DEPT 테이브의 DEPTNO컬럼이 기본키나 유일 인덱스였다면 옵티마이져는==>
SELECT emp.* FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.loc = 'NEW YORK';
조인문으로 변환할 수 없다면, 메인쿼리와 서브쿼리에 대한 실행 계획을 선택==>
SELECT * FROM emp WHERE sal >(SELECT AVG(sal) FROM emp WHERE depton = 20);
{column}{section}
3.1.4.2 뷰병합(Vieew Merging)
뷰병합 - 뷰쿼리 : 우리가 뷰를 생성할 때 사용한 SELECT 문, 딕셔너리에 저장되어 있는 SELECT 문,FROM 절에 괄호로 묶어 둔 SELECT
- 액세스 쿼리 : 뷰를 수행하는 SQL ,인라인뷰 바깥에 있는 SELECT 를 액세스
로 나눈다.
- 뷰쿼리와 엑세스쿼리를 병합하는 방법
1. 뷰병합(View Merging)법: 뷰쿼리를 액세스쿼리에 병합해 넣는방식
2. 조건절 진입(Pushing predicate)법 : 뷰병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 액세스쿼리의 조건절 진입시키는방식 {section}{column:width=300}
- 뷰쿼리를 액세스 쿼리로 병합시키기
- 액세스 쿼리에 있는 뷰의 이름을 뷰쿼리의 원래의 테이블로 이름을 바꾸고, 뷰의 WHERE절에 있는 조건절을 액세스 쿼리 WHERE절에 추가 시킨다.
VIEW CREATE
create View emp_10 (e_no, e_name, job, manager, hire_date, salary, commission, deptno) AS
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from emp
where deptno= 10;
- 뷰를 액세스하는 쿼리
select em_no, e_name, salary, hire_date
from emp_10
where salary >10000;
=> 액세스쿼리가 기준이 된다. 즉 액세스쿼리를 기준으로 뷰퀄에 있는 대응인자들을 병합한다.
- SQL 병합 완료 된 쿼리
select empno, ename, sal, hiredate
from emp
where deptnl = 10
and sal > 10000
=> 액세스쿼리에 있는 뷰를 원래의 테이블인 emp에 변환하고, 남아있는 조건절을 다시 액세스쿼리에 병합
- 액세스쿼리에있는 조건들을 뷰쿼리에 진입시킨경우
=> VIEW 생성
CREATE VIEW emp_union_view(e_no,e_name, job, mgr, hiredate,sal, comm,deptno)
AS select empno, ename, job, mgr, hiredate, sal, comm, deptno from regular_emp
UNION ALL
select empno, ename, job, magager, hiredate, salary, comm, 90 from temporary_emp;
① select e_no, e_name, mgr,sal
from emp_union_view
where deptnl = 20;
② select empno, ename, mgr, sal
from (select empno, ename, ggr, sal from regular_emp where deptno = 20
UNION ALL
select empno, ename, magager, salary from temporary_emp where 90 = 20);
=>① → ② 에 변환이 일어난다.
=> 변환된 쿼리를 보면 액세스쿼리에 있는 조건들이 각 select 문의 조건절속으로 파고 들어갔다.
=> 임시사원직은 부서코드를 가지지 않아서 '90' 을 지정했으므로 두번째 select의 조건절은 상수값을 서고비교하는모습니다.
- GROUP BY 를 사용한 뷰
create view emp_group_by_deptno
AS select deptno, avg(sal) avg_sal, mon(sal min_sal, max(sal) max_sal
from emp
GROUP BY deptno;
① select * from emp_group_by_deptno
where deptno = 10;
② select deptno, avg(sal) avg_sal, mon(sal min_sal, max(sal) max_sal
from emp
where deptno = 10
GROUP BY deptno;
=> ① → ② 에 변환이 일어난다.
=> 뷰는 조건절을 가지고 있지 않으면서 GROUP BY 를 하고 있으므로 마치 전체 테이블을 대상으로 GROUP BY 를 해 둔것처럼보인다.
=> 뷰 최적화를 위한 변환에 의해 나타나는 쿼리에는 액세스 쿼리에 부여한 조건들이 진입된다.
※ 뷰병합과 관련되 파라메터(complex_view_merging, optimizer_secure_view_merging) 가
작동상태되어 있으면 복잡한 뷰 내부에 GROUP BY 나 DISTINCT 를 사용했다라도 액세스쿼리의 조건들이 뷰쿼리에 파고들어갈수있다.
- 액세스 퀄리가 병합이 된경우
select d.loc, v.avg_sal
from dept d, emp_group_by_deptno v
wehre d.deptno = v.deptno
and d.loc = 'London';
병합된 쿼리 -> select dept.loc, avg(sal) 실행계획
from dept, emp SORT (GROUP BY)
where dept.deprtno = emp.deptno => NESTED LOOPS
and dept.loc = 'London' TABLE ACCESS (FULL) OF 'EMP'
GROUP BY dept.rowid, dept.loc; TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
INDEX (RANGE SCAN) OF
=> 원 뷰 쿼리에 GROUP BY DEPTO는 없어지고, 스스로 dept.rowid로 대체하였고
모든 범위의 액세스가 완료된 후에 SORT(GROUP BY)를 한다는 사실이다.
이 형태는 가능한 모든 조건이 먼저 적용된 후에 2차 가공을 하므로 가장 최적의 문장이 되었다.
- 힌트를 사용하여 액세스쿼리에 병합이 일어나지 안는경우
select /*+ NO_MERGE(v) */ 실행계획
d.loc, v.avg_sal HASH JOIN
from dept d, emp_group_by_deptno v => TABLE ACCESS (FULL) OF 'DEPT'
where d.deptno = v.deptno VIEW OF 'EMP_GROUP_BY_DEPTNO'
and d.loc = 'London'; SORT (GROUP BY)
TABLE ACCESS (FULL) OF 'EMP';
※ 병합이 불가능한 경우
■ 집합연산(UNION, UNION ALL, INTERSECT, MINUS)
■ CONNECT BY
■ ROWNUM 을 사용한경우
■ SELECT-List의 그룹함수(AVG,COUNT,MAX,MIN,SUM)
■ GROUP BY(단, Merge 힌트를 사용했거나 관련 파라메터가 Enable이면 뷰병합 가능)
■ SELECT-List 의 DISTINCT (단, Merge 힌트를 사용했거나 관련 파라메터가 Enable이면 뷰병합 가능)
- 인라인 뷰의 병합
select /*+ MERGE(V) */ e1.last_name, e1.sal, v.avg_salary
from emp e1, (select deptno, avg(sal) avg_salary
from emp e2
GROUP BY deptno ) v
where e1.deptno = v.deptno
and e1.sal > v.avg_salary;
실행계획 실행계획 분석
FILTER HASH JOIN
SORT (GROUP BY) TABLE ACCESS (FULL) OF 'EMP'
HASH JOIN => VIEW
TABLE ACCESS (FULL) OF 'EMP' SORT (GROUP BY)
TABLE ACCESS (FULL) OF 'EMP' TABLE ACCESS (FULL) OF 'EMP'
=> 먼저 인라인뷰를 수행하고 그결과 EMP테이블을 조인한다.
글므로 뷰병합이 안일어난게 유리하다.
※ 뷰병합의 적용에 관련된 파라메터는 기본값을 'TRUE'에 두는 것이 바람직하며, 위의 적용기준을 이용하여 필요하다면
NO_MERGE힌트를 적용하는 방법이 좋은 실행계획을 얻을 확률이 높다.
{column}{section}
3.1.4.3 사용자 정의 바인드 변수의 엿보기(Peeking)
- 바인드 변수를 사용한 커리가 처음 실행될 때 옵티마이져는 사용자가 지정한 바인드 변의 값을 '살짝 커닝'
함으로써 조건절의 컬럼값이 상수값으로 제공될때와 마찬가지로 선택도를 확인하여 최적화를 수행하도록 한다. - 커서가 계속 실행되어 다른 값이 바인딩 되더라도 더이상 PEEKING은 발생하지 않고 앞서 생성된 실행계획을 그대로 사용한다.
- 최초에 실질적인 파싱이 일어날때만 단 한번 변수의 값을 엿본다는 것이다.
- 자신이 작성한 SQL이 개략적으로라도 어떤 형식으로 수행하며, 어느 정도의 처리량으로 수행될 것인지 파악할 수 있는 힘이 바탕이 되어야한다.
- 무작정 접근하지말고 최소한의 상황판단을 한 후에 실행을 한다.
문서에 대하여
- 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 이화식님의 새로쓴 대용량 데이터베이스 솔루션을 참고했습니다.*
- 이 문서를 다른 블로그나 홈페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^\^
"구루비 데이터베이스 스터디모임" 에서 2008년에 "새로쓴 대용량 데이터베이스 솔루션1" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/2565
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.