문서의 이전 판입니다!
옵티마이져는 가능한 모든 수식의 값을 미리 구한다
<color red>보다 양호한 실행계획을 얻을 수 있도록 가능한 최대로 적절하게 SQL 형태를 변환하는 것이 중요하다</color>
이행성 규칙(Transitivity principle)
조건절에 같은 컬럼을 사용한 두 개의 조건식이 있다면 옵티마이져는 새로운 조건식을 이행성규칙에 따라 생성하고 이 조건식을 이용하여 최적화를 수행
<color blue>원래의 조건식에서는 인덱스를 사용하지 못하게 되었더라도 인덱스를 사용하는 실행계획이 나타날 수 있다</color>
조건식 형식
WHERE column1 comparison_operators constant
AND column1 = column2
comparison_operators : =, !=, ^=, <, <>, >, ⇐, >= 중 하나
constant : 연산, SQL 함수, 문자열, 바인드 변수, 상관관계 변수를 포함하는 상수 수식
[예1] 이행성 규칙
<color blue>비용기준 업티마이져를 사용할 때만 적용</color>
SELECT *
FROM emp e, dept d
WHERE e.deptno = 20
AND e.deptno = d.deptno;
만약 e.deptno 인덱스가 없었다면, EMP테이블을 전체 테이블 액세스를 하면서 매번 dept 테이블 연결
이행성 규칙에 의해 실행계획이 DEPT 테이블을 먼저 인덱스로 액세스하는 실행계획이 가능
비교하는 대상이 상수 수식이 아닌 컬럼인 경우는 이행이 일어나지 않음
WHERE column1 comparison_operators column3
AND column1 = column2
[예2] 이행성 규칙 - OR조건을 가진 UNION ALL 사용
OR를 가지는 조건들을 UNION ALL을 사용한 복합 문장으로 변환하는 경우이다
UNION ALL로 각각의 인덱스를 경유하는 실행계획을 수립하고 이를 결합
이런 현상은 IN 을 사용한 상수수식 비교에서도 유사하게 나타난다
그 이유는 이런한 형태의 IN은 OR로 변환되기 때문이다
변환기준
OR를 사용한 조건절을 분기시켰을 때 각각이 인덱스 접근 경로로 이용할 수 있다면 변환을 수행
인덱스 사용불가로 전체 테이블을 스캔 하거나, OR 조건이 검증기능만 수행하면 변환하지 않음
즉, 분기된 각각의 액세스가 불필요한 범위를 액세스하지 않는다고 판단한 경우에만 적용
실행계획
IN-LIST ITERATOR나 CONCATENATION으로 실행계획이 나타남
강제적인 적용을 위해 ‘USE_CONCAT’ 힌트를 사용.
함부로 사용하면 액세스가 불필요하게 분기되어 오히려 처리량이 크게 증가
SQL
SELECT *
FROM emp
WHERE job = 'CLERK'
OR deptno = 10;
SQL 변환
SELECT * FROM emp WHERE deptno = 10
UNION ALL
SELECT * FROM emp WHERE job = 'CLERK‘ and deptno <> 10;
[예3] 이행성 규칙 - 서버쿼리의 변환
서비쿼리를 가진 복잡한 문장을 조인 문장으로 변환하는 경우
최적화하기 위해서는 2가지 방법 중 하나를 선택
SELECT *
FROM emp
WHERE deptno IN (SELECT deptno
FROM dept
WHERE loc = ‘NEW YORK’) ;
같은 기능의 조인 문장으로 변환하여 다시 이를 최적화
서브쿼리 <color blue>SELECT deptno FROM dept WHERE loc = ‘NEW YORK’</color>는 집합이므로 조인을 해도 메인쿼리에 영향을 주지 않음.
SELECT emp.*
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND dept.loc = ‘NEW YORK’ ;
현재 문장을 그대로 최적화
제공자역할
서브쿼리를 먼저 수행하여 결과를 메인쿼리에 제공
서브쿼리 실행결과를 메인쿼리의 처리주관 인덱스에 제공할 수 있을 때 적용
확인자 역할
메인쿼리가 실행되면서 서브쿼리를 확인하는 필터형 처리
각의 집합을 액세스한 후 Merge하거나 혹은 Hash Join으로 수행
뷰병합(View Merging)
인라인뷰나 뷰를 최적화하기 위해 사용하는 뷰와 관련된 변환이다.
뷰를 사용하는 쿼리는 크게 뷰쿼리와 액세스쿼리로 나눈다
뷰쿼리 | 뷰를 생성할때 사용한 SELECT 문
dictionary에 저장된 select문 |
액세스쿼리 | 이 뷰를 수행하는 SQL문 |
뷰병합(View Merging)법 | 뷰쿼리를 액세스쿼리에 병합해 넣는 방식 |
조건절 진입(Predicate pushing)법 | 뷰병합을 할 수 없는 경우를 대상으로
뷰쿼리 내부에 액세스쿼리의 조건절을 진입시키는 방식 |
뷰쿼리를 액세스쿼리로 병합
뷰쿼리
CREATE VIEW emp_10
(e_no, e_name, job, manager, salary, commission, deptno)
AS
SELECT empno, ename, job, mgr, sal, comm, deptno
FROM emp
WEHRE deptno = 10;
액세스쿼리
SELECT e_no, e_name, salary, deptno
FROM emp_10
WHERE salary > 1000000;
뷰쿼리를 액세스쿼리에 병합
SELECT empno, ename, sal, hiredate
FROM emp
WHERE deptno = 10
AND sal > 1000000;
액세스쿼리에 있는 뷰를 원래 테이블인 emp로 변환
남아있는 조건절을 다시 액세스쿼리에 병합
컬럼들도 대응되는 원래 테이블의 컬럼들로 병합
View Merging이 불가능한 경우
집합 연산 (UNION, UNION ALL, INTERSECT, MINUS)
CONNECT BY
ROWNUM을 사용한 경우
SELECT-List의 그룹함수(AVG, COUNT, MAX, MIN, SUM)
GROUP BY (단, Merge 힌트를 사용했거나 관련 파라메터가 Enable 이면 뷰병합가능)
SELECT-List의 DISTINCT (단, Merge 힌트를 사용했거나 관련 파라메터가 Enable 이면 뷰병합가능)
조건절 진입(Predicate pushing)
뷰병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 액세스쿼리의 조건절을 진입시키는 방식
뷰쿼리
CREATE VIEW emp_union_view
(e_no, e_name, job, mgr, sal, comm, deptno)
AS
SELECT empno, ename, job, mgr, sal, comm, deptno
FROM regular_emp
UNION ALL
SELECT empno, ename, job, manager, salary, comm, 90
FROM temporary_emp;
액세스쿼리
SELECT e_no, e_name, mgr, sal
FROM emp_union_view
WHERE deptno = 20;
병합쿼리
SELECT empno, ename, mgr, sal
FROM ( SELECT empno, ename, mgr, sal
FROM regular_emp
WHERE deptno = 20
UNION ALL
SELECT empno, ename, manager, salary
FROM temporary_emp
WHERE 90 = 20 );
뷰쿼리가 기준이 되어 뷰쿼리의 구조에 액세스쿼리의 조건들을 진입시키는 변환
액세스쿼리에 있는 조건들이 뷰쿼리의 각 SELECT문의 조건절 속으로 파고들어감(Pushing)
액세스쿼리의 컬럼들에 대응되는 뷰쿼리의 컬럼 선택
WHERE 90 = 20 은 항상 거짓이므로 실행되지 않음
GROUP BY 뷰의 병합
뷰쿼리
CREATE VIEW emp_group_by_deptno
AS
SELECT deptno, AVG(sal) avg_sal,
MIN(sal) min_sal, MAX(sal) max_sal
FROM emp
GROUP BY deptno;
전체 테이블 로우에 대해 GROUP BY 한 것처럼 보이는 뷰
액세스쿼리
SELECT *
FROM emp_group_by_deptno
WHERE deptno = 10;
병합
SELECT deptno, AVG(sal) avg_sal,
MIN(sal) min_sal,MAX(sal) max_sal
FROM emp
WHERE deptno = 10
GROUP BY deptno;
Complex_view_merging , Optimizer_secure_view_merging 파라메터가 Enabled 되어 있으면 가능하다
조건을 만족하는 대상들만 GROUP BY
액세스 쿼리의 병합
액세스쿼리
SELECT d.loc,v.avg_sal
FROM dept d, emp_group_by_deptno v
WHERE d.deptno = v.deptno
AND d.loc = 'London';
액세스 쿼리의 정상적인 병합
병합된 쿼리
SELECT dept.loc, AVG(sal)
FROM dept, emp
WHERE dept.deprtno = emp.deptno
AND dept.loc = 'London'
GROUP BY dept.rowid, dept.loc;
실행계획
SORT (GROUP BY)
NESTED LOOPS
TABLE ACCESS (FULL) OF 'EMP'
TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
INDEX (RANGE SCAN) OF 'PK_DEPT'(UNIQUE)
조건처리가 완료된 후 GROUP BY
액세스 쿼리의 병합이 안 일어나는 경우
액세스쿼리
SELECT /*+ NO_MERGE(v) */ d.loc,v.avg_sal
FROM dept d, emp_group_by_deptno v
WHERE d.deptno = v.deptno
AND d.loc = 'London';
병합쿼리
SELECT d.loc, v.avg_sal
FROM (SELECT deptno, AVG(sal) avg_sal,
MIN(sal) min_sal,
MAX(sal) max_sal
FROM emp
GROUP BY deptno) v, dept d
WHERE d.deptno = v.deptno
AND d.loc = 'London' ;
실행계획
HASH JOIN
TABLE ACCESS (FULL) OF 'DEPT'
VIEW OF 'EMP_GROUP_BY_DEPTNO'
SORT (GROUP BY)
TABLE ACCESS (FULL) OF 'EMP'
뷰쿼리를 먼저 처리한 후 조인
인라인 서브쿼리의 병합
사용자 정의 바인드 변수의 엿보기(Peeking)
바인드 변수를 사용한 쿼리는 먼저 파싱과 최적화가 이루어진 후에 바인드 변수에 바인딩이 이루어진다
최적화가 이루어지는 시점에는 변수로 제공되는 컬럼은 바인딩 값에 대한 통계정보를 사용할 수 없다는 것을 의미
분포도가 균일하지 못한 컬럼에 바인드 변수를 사용하면 최악의 실행계획이 생성될 수도 있다
Peeking
바인드 변수를 사용한 쿼리가 처음 실행될 때
옵티마이져는 사용자가 지정한 바인드 변수의 값을 살짝 커닝함으로써
조건절의 컬럼값이 상수값으로 제공될 때와 마찬가지로 선택도를 확인하여 최적화를 수행
첫번째 파싱 : 공유 SQL 영역에 처음 등록될 때
Peeking의 비효율성
성별 컬럼에 M 이 99% 이고, F가 1% 들어있는 경우
SELECT name FROM patients WHERE sex = :input_sex_value ;
평균적용
M이 첫번째일때
F가 첫번째일때
* <color red>한가지 별도로 쿼리의 실행계획을 확인했을때와 실제로 수행된 실행계획은 다를 수 있다.</color>