목차

제 3장 SQL의 실행계획 - 질의의 변환(Query Transforming)

  1. 옵티마이져는 가능한 모든 수식의 값을 미리 구한다
  2. <color red>보다 양호한 실행계획을 얻을 수 있도록 가능한 최대로 적절하게 SQL 형태를 변환하는 것이 중요하다</color>

이행성 규칙(Transitivity principle)

  1. 조건절에 같은 컬럼을 사용한 두 개의 조건식이 있다면 옵티마이져는 새로운 조건식을 이행성규칙에 따라 생성하고 이 조건식을 이용하여 최적화를 수행
  2. <color blue>원래의 조건식에서는 인덱스를 사용하지 못하게 되었더라도 인덱스를 사용하는 실행계획이 나타날 수 있다</color>
  3. 조건식 형식
    WHERE column1 comparison_operators  constant
         AND column1 = column2 
    1. comparison_operators : =, !=, ^=, <, <>, >, ⇐, >= 중 하나
    2. constant : 연산, SQL 함수, 문자열, 바인드 변수, 상관관계 변수를 포함하는 상수 수식

[예1] 이행성 규칙

  1. <color blue>비용기준 업티마이져를 사용할 때만 적용</color>
  2. SELECT *
    FROM  emp e, dept d
    WHERE e.deptno = 20
      AND e.deptno = d.deptno;
  3. 만약 e.deptno 인덱스가 없었다면, EMP테이블을 전체 테이블 액세스를 하면서 매번 dept 테이블 연결
  4. 이행성 규칙에 의해 실행계획이 DEPT 테이블을 먼저 인덱스로 액세스하는 실행계획이 가능

비교하는 대상이 상수 수식이 아닌 컬럼인 경우는 이행이 일어나지 않음

WHERE column1 comparison_operators  column3
     AND column1 = column2

[예2] 이행성 규칙 - OR조건을 가진 UNION ALL 사용

  1. OR를 가지는 조건들을 UNION ALL을 사용한 복합 문장으로 변환하는 경우이다
  2. UNION ALL로 각각의 인덱스를 경유하는 실행계획을 수립하고 이를 결합
  3. 이런 현상은 IN 을 사용한 상수수식 비교에서도 유사하게 나타난다
    그 이유는 이런한 형태의 IN은 OR로 변환되기 때문이다

변환기준

  1. OR를 사용한 조건절을 분기시켰을 때 각각이 인덱스 접근 경로로 이용할 수 있다면 변환을 수행
  2. 인덱스 사용불가로 전체 테이블을 스캔 하거나, OR 조건이 검증기능만 수행하면 변환하지 않음
  3. 즉, 분기된 각각의 액세스가 불필요한 범위를 액세스하지 않는다고 판단한 경우에만 적용

실행계획

  1. IN-LIST ITERATORCONCATENATION으로 실행계획이 나타남
  2. 강제적인 적용을 위해 ‘USE_CONCAT’ 힌트를 사용.
  3. 함부로 사용하면 액세스가 불필요하게 분기되어 오히려 처리량이 크게 증가
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] 이행성 규칙 - 서버쿼리의 변환

  1. 서비쿼리를 가진 복잡한 문장을 조인 문장으로 변환하는 경우
  2. 최적화하기 위해서는 2가지 방법 중 하나를 선택
SELECT *
FROM  emp
WHERE deptno IN (SELECT deptno 
                 FROM dept 
                 WHERE loc =NEW YORK’) ;

같은 기능의 조인 문장으로 변환하여 다시 이를 최적화

  1. 서브쿼리 <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’ ;

현재 문장을 그대로 최적화

  1. 제공자역할
    1. 서브쿼리를 먼저 수행하여 결과를 메인쿼리에 제공
    2. 서브쿼리 실행결과를 메인쿼리의 처리주관 인덱스에 제공할 수 있을 때 적용
  2. 확인자 역할
    1. 메인쿼리가 실행되면서 서브쿼리를 확인하는 필터형 처리
    2. 각의 집합을 액세스한 후 Merge하거나 혹은 Hash Join으로 수행

뷰병합(View Merging)

  1. 인라인뷰나 뷰를 최적화하기 위해 사용하는 뷰와 관련된 변환이다.
  2. 뷰를 사용하는 쿼리는 크게 뷰쿼리와 액세스쿼리로 나눈다
뷰쿼리 뷰를 생성할때 사용한 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;
  1. 액세스쿼리에 있는 뷰를 원래 테이블인 emp로 변환
  2. 남아있는 조건절을 다시 액세스쿼리에 병합
  3. 컬럼들도 대응되는 원래 테이블의 컬럼들로 병합

View Merging이 불가능한 경우

  1. 집합 연산 (UNION, UNION ALL, INTERSECT, MINUS)
  2. CONNECT BY
  3. ROWNUM을 사용한 경우
  4. SELECT-List의 그룹함수(AVG, COUNT, MAX, MIN, SUM)
  5. GROUP BY (단, Merge 힌트를 사용했거나 관련 파라메터가 Enable 이면 뷰병합가능)
  6. SELECT-List의 DISTINCT (단, Merge 힌트를 사용했거나 관련 파라메터가 Enable 이면 뷰병합가능)

조건절 진입(Predicate pushing)

  1. 뷰병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 액세스쿼리의 조건절을 진입시키는 방식
뷰쿼리
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  );
  1. 뷰쿼리가 기준이 되어 뷰쿼리의 구조에 액세스쿼리의 조건들을 진입시키는 변환
  2. 액세스쿼리에 있는 조건들이 뷰쿼리의 각 SELECT문의 조건절 속으로 파고들어감(Pushing)
  3. 액세스쿼리의 컬럼들에 대응되는 뷰쿼리의 컬럼 선택
  4. WHERE 90 = 20 은 항상 거짓이므로 실행되지 않음

GROUP BY 뷰의 병합

  1. Pushing predicate Merge
뷰쿼리
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;
  1. 전체 테이블 로우에 대해 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;
  1. Complex_view_merging , Optimizer_secure_view_merging 파라메터가 Enabled 되어 있으면 가능하다
  2. 조건을 만족하는 대상들만 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)
  1. 조건처리가 완료된 후 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'
  1. 뷰쿼리를 먼저 처리한 후 조인

인라인 서브쿼리의 병합

뷰쿼리
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;
액세스쿼리
SELECT e.ename, e.sal
FROM emp e, dept d
WHERE (e.deptno, e.sal ) IN (SELECT deptno, avg_sal
                                           FROM emp_group_by_deptno)
    AND e.deptno = d.deptno
    AND d.loc = 'London' ;
  1. 실행계획을 수립하는 입장에서 보면 반드시 평균급여를 미리 구해야 할 필요는 없다
MERGE 힌트사용시
FILTER
 SORT (GROUP BY)  
  HASH JOIN  
    TABLE ACCESS (FULL) OF 'EMP' 
    TABLE ACCESS (FULL) OF 'EMP' 
NO_MERGE 힌트사용시
HASH JOIN  
 TABLE ACCESS (FULL) OF 'EMP'  
 VIEW  
   SORT (GROUP BY)  
     TABLE ACCESS (FULL) OF 'EMP'
  1. 뷰병합을 하지 않는 것이 유리

결론

  1. 처리범위를 줄일 수 있는 조건들이 많아 인라인뷰로 파고들어 가는 것이 일 량을 줄일 수 있다면 뷰병합이 유리
  2. 파라메터는 기본값을 ‘TRUE’로 하고, 필요하다면 NO_MERGE 힌트를 적용하는 것이 바람직함

사용자 정의 바인드 변수의 엿보기(Peeking)

  1. 바인드 변수를 사용한 쿼리는 먼저 파싱과 최적화가 이루어진 후에 바인드 변수에 바인딩이 이루어진다
  2. 최적화가 이루어지는 시점에는 변수로 제공되는 컬럼은 바인딩 값에 대한 통계정보를 사용할 수 없다는 것을 의미
  3. 분포도가 균일하지 못한 컬럼에 바인드 변수를 사용하면 최악의 실행계획이 생성될 수도 있다

Peeking

  1. 바인드 변수를 사용한 쿼리가 처음 실행될 때
    옵티마이져는 사용자가 지정한 바인드 변수의 값을 살짝 커닝함으로써
    조건절의 컬럼값이 상수값으로 제공될 때와 마찬가지로 선택도를 확인하여 최적화를 수행
  2. 첫번째 파싱 : 공유 SQL 영역에 처음 등록될 때

Peeking의 비효율성

성별 컬럼에 M 이 99% 이고, F가 1% 들어있는 경우

 SELECT name FROM patients WHERE sex = :input_sex_value ;
평균적용
  1. 인덱스 미사용
M이 첫번째일때
  1. 인덱스 미사용
     WHERE sex =‘M’; 
F가 첫번째일때
  1. 인덱스 사용
     WHERE sex =‘F’; 

* <color red>한가지 별도로 쿼리의 실행계획을 확인했을때와 실제로 수행된 실행계획은 다를 수 있다.</color>