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

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

1. 조건절에 같은 컬럼을 사용한 두 개의 조건식이 있다면 옵티마이져는 새로운 조건식을 이행성규칙에 따라 생성하고 이 조건식을 이용하여 최적화를 수행
2. <color blue>원래의 조건식에서는 인덱스를 사용하지 못하게 되었더라도 인덱스를 사용하는 실행계획이 나타날 수 있다</color>
3. 조건식 형식

   WHERE column1 comparison_operators  constant
        AND column1 = column2 

   1. comparison_operators : =, !=, ^=, <, <>, >, ⇐, >= 중 하나
   2. constant : 연산, SQL 함수, 문자열, 바인드 변수, 상관관계 변수를 포함하는 상수 수식

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

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

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

1. IN-LIST ITERATOR나 CONCATENATION으로 실행계획이 나타남
2. 강제적인 적용을 위해 ‘USE_CONCAT’ 힌트를 사용.
3. 함부로 사용하면 액세스가 불필요하게 분기되어 오히려 처리량이 크게 증가

SELECT *
FROM emp
WHERE job = 'CLERK'
  OR  deptno = 10;

SELECT * FROM emp WHERE deptno = 10
UNION ALL
SELECT * FROM emp WHERE job = 'CLERK‘ and deptno <> 10;

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으로 수행

1. 인라인뷰나 뷰를 최적화하기 위해 사용하는 뷰와 관련된 변환이다.
2. 뷰를 사용하는 쿼리는 크게 뷰쿼리와 액세스쿼리로 나눈다

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. 컬럼들도 대응되는 원래 테이블의 컬럼들로 병합

• <color blue>만약 EMP 테이블에 DEPTNO + SAL 로 구성된 인덱스가 있었다면
  변환된 SQL에 의해 완벽한 실행계획을 갖게 된다</color>

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 이면 뷰병합가능)

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 은 항상 거짓이므로 실행되지 않음

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

• <color red>만약 액세스쿼리에서 특정일을 조건으로 주었지만 이 조건이 병합되지 못한다면 …</color>

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. 실행계획을 수립하는 입장에서 보면 반드시 평균급여를 미리 구해야 할 필요는 없다

FILTER
 SORT (GROUP BY)  
  HASH JOIN  
    TABLE ACCESS (FULL) OF 'EMP' 
    TABLE ACCESS (FULL) OF 'EMP' 

HASH JOIN  
 TABLE ACCESS (FULL) OF 'EMP'  
 VIEW  
   SORT (GROUP BY)  
     TABLE ACCESS (FULL) OF 'EMP'

1. 뷰병합을 하지 않는 것이 유리

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

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

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

 SELECT name FROM patients WHERE sex = :input_sex_value ;

1. 인덱스 미사용

1. 인덱스 미사용

    WHERE sex =‘M’; 

1. 인덱스 사용

    WHERE sex =‘F’; 

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