1. 질의의 변환(Query Transforming)
   1. 질의 변환 Case
   2. 이행성규칙(Transitivity principle)
   3. 뷰병합(View Merging)
   4. 사용자 정의 바인드 변수의 엿보기(Peeking)
2. 개발자의 역할
   1. 집합적 사고로의 전환
   2. 수행 결과 뿐만 아니라 성능을 고려한 SQL을 활용

질의의 변환(Query Transforming)

질의 변환 Case

상수 변환

• 옵티마이져는 가능한 모든 수식의 값을 미리 구함
• 상수의 연산을 실행될 때마다 이루어지는 것이 아니라 질의 변환단계에서 한 번만 수행

  예)
    ① sales_qty > 1200/12
    ② sales_qty > 100
    ③ sales_qty*12  > 1200
      
    ①,② 는 동일하게 취급 - 사전에 최대한의 연산을 하여 두 번째 조건식이 됨
    ③은 연산을 하여 ② 조건식을 만들지 않음 - 비교연산자의 추가적 연산은 연산자를 좌우로 이동시켜 단순화를 하지는 않기 때문

Like 단순화

• LIKE를 사용한 조건절에서 '%' 나 '_'를 사용하지 않는 경우에는 '=' 연산으로 조건절 단순화
• 가변길이 데이터타입일 때만 이러한 수식의 단순화가 가능

조건식에 IN, OR을 이용한 Case

• IN 비교 연산자를 사용한 문장에서는 OR 논리 연산자를 이용해 여러개의 '='로 된 같은기능의 조건절로 확장

  예)
    ① job IN ('CLERK','MANAGER')
    ② job  = 'CLERK' OR  job  = 'MANAGER'

조건식에 ANY, SOME 을 이용한 Case

• '=' 비교연산자와 OR논리 연산자를 이용해 같은 기능의 조건절로 확장

  예)
    ① sales_qty > ANY (:in_qty1, :in_qty2)
    ② sales_qty > :in_qty1  OR  sales_qty > :in_qty2

ANY, SOME 뒤에 서브쿼리를 사용한 Case

• EXISTS 연산자를 이용한 서브쿼리로 변환

  예)
    ① where  100000 > ANY  (select sal from emp  where job = 'CLERK')
    ② where  EXISTS (select sal from emp  where job = 'CLERK'  and 100000 > sal)

조건식에 ALL 을 이용한 Case

• 조건절은 '=' 연산자와 AND 논리 연산자를 사용해 같은 기능의 조건절로 변환

  예)
    ① sales_qty > ALL (:in_qty1, :in_qty2)
    ② sales_qty > :in_qty1  AND  sales_qty > :in_qty2

ALL 뒤에 서브쿼리를 사용한 Case

• NOT ANY로 변환한 후에 다시 EXISTS 연산자를 이용하여 서브쿼리로 변환

  예)
    ① 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)

조건식에 BETWEEN 를 이용한 Case

• '>=', '<=' 비교 연산자를 사용하여 변환

  예)
    ① sales_qty  BETWEEN 100 and 200
    ② sales_qty  >= 100 AND  sales_qty <= 200

조건식에 NOT를 이용한 Case

• 논리 연산자를 제거할 수 있는 반대 비교연산을 찾아 대체시키는 변환

  예)
    ① NOT (sal < 30000 OR comm IS NULL)
    ② NOT  sal < 30000 AND comm IS NOT NULL)
    ③ sal >= 30000 AND comm IS NOT NULL
    
    ① →  ②  →  ③  순서로 변환

서브쿼리에 사용된 NOT을 변환하는 Case

• NOT를 없애기 위해 반대 비교연산을 찾아 대체시키는 변환

  예)
    ① NOT deptno = ( SELECT deptno FROM emp WHERE empno = 7689)
    ② deptno  <> (SELECT deptno FROM emp WHERE empno = 7689)

이행성규칙(Transitivity principle)

• 조건절에 같은 컬럼을 사용한 두 개의 조건식이 있다면 옵티마이져는 새로운 조건식을 이행성 규칙에 따라 생성하고 이 조건식을 이용하여 최적화를 수행

  WHERE column1 comparison_operators constant
      AND column1 = column2

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

• 추론 결과 : COLUMN2 comparison_operators constant

• 비용기준 옵티마이져일때만 적용됨
• 대상이 상수 수식이 아닌 컬럼인 경우는 이행이 일어나지 않는다.

WHERE 절에 OR로 연결된 두개의 조건절이 있을 때 발생할 수 있는 경우

• OR를 사용한 조건절을 분기시켰을 때 각각이 인덱스 접근 경로로 이용할 수 있다면 변환
• UNION ALL에 의해 각각의 인덱스를 경유하는 실행계획을 선택하고 나중에 결합
• 실행계획에서는 'IN-LIST ITERATOR' 나 'CONCATENATION'이라는 표시가 나타남

  예)
    select *  from emp  where job = 'CLERK' OR deptno = 10 ;
    select * from emp where job = 'CLERK'
    UNION ALL
    SELECT * from emp where deptno = 10 and job <> 'CLERK';

• 조건절에 인덱스를 사용할수 없고 전체 테이블을 스캔한다고하면 OR를 사용한 조건절은 그 문장에 대한 변환을 하지 않음
• 이행성규칙은 효율적이라고 판된될 때만 변환이 일어난다.

뷰병합(View Merging)

뷰를 사용하는 쿼리

• 뷰쿼리 : 우리가 뷰를 생성할 때 사용한 SELECT 문
• 액세스 쿼리 : 뷰를 수행하는 SQL

뷰쿼리와 엑세스쿼리를 병합하는 방법

• 뷰병합(View Merging)법: 뷰쿼리를 액세스쿼리에 병합해 넣는방식
• 조건절 진입(Pushing predicate)법 : 뷰병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 액세스쿼리의 조건절 진입시키는방식

조건절 진입의 경우(뷰병합이 불가능한 경우)

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

뷰쿼리를 액세스 쿼리로 병합시키기

• 액세스 쿼리에 있는 뷰의 이름을 뷰쿼리의 원래의 테이블로 이름을 바꾸고, 뷰의 WHERE절에 있는 조건절을 액세스 쿼리 WHERE절에 추가

  예)
    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

조건절 진입의 경우

집합연산의 경우

  예)
    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 문의 조건절속으로 파고 들어갔음을 알 수 있음

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;

  - 액세스 쿼리
    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;

복잡한 뷰 내부에 GROUP BY나 DISTINCT 를 사용한 경우

• 뷰병합과 관련된 파라메터(complex_view_merging, optimizer_secure_view_merging)가 작동상태되어 있으면 복잡한 뷰 내부에 GROUP BY 나 DISTINCT 를 사용했다라도 액세스쿼리의 조건들이 뷰쿼리에 파고들어 갈 수 있다.

  예) 
  - 액세스 쿼리
    SELECT emp.ename, emp.sal
      FROM emp, dept
     WHERE (emp.deptno, emp.sal) IN (SELECT deptno, avg_sal FROM emp_group_by_deptno)
       AND emp.deptno = dept.deptno
       AND dept.oc = 'London' ;
       
  - 변환된 쿼리
    SELECT e1.ename, e1.sal
      FROM emp e1, dept d, emp e2
     WHERE e1.deptno = d.deptno
       AND d.loc = 'London'
       AND e1.deptno = e2.deptno
     GROUP BY e1.rowid, d.rowid, e1.ename, e1.dalary
     HAVING e1.sal = AVG(e2.sal) ;

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

• 바인드 변수를 사용한 커리가 처음 실행될 때 옵티마이져는 사용자가 지정한 바인드 변의 값을 '살짝 커닝'함으로써 조건절의 컬럼값이 상수값으로 제공될때와 마찬가지로 선택도를 확인하여 최적화를 수행하도록 한다.
• 최초에 실질적인 파싱이 일어날때만 단 한번 변수의 값을 PEEKING 함
• PEEKING의 적용 여부는 _OPTIM_PEEK_USER_BINDS 파라메터로 결정

• 현실적으로 최초의 단 한 가지 경우의 선택도를 사용하는 것이나 전체를 평균적인 분포로 보는 것이나 논리적으로 오류일 확률은 다르지 않음
• 11g에서는 Adaptive Cursor Sharing 기능으로 이러한 문제를 많이 해결함

개발자의 역할

집합적 사고로의 전환

• 과거 절차형 프로그래밍의 개념에서 SQL의 근간이 되는 집합적, 비절차형 처리에 적응해야 함

수행 결과 뿐만 아니라 성능을 고려한 SQL을 활용

• SQL에 대한 활용도가 높아지면서 점차 모든 처리를 SQL로 해결하려는 개발자들이 생겨남
• 하지만 그 과정에서 요구되는 컬럼의 값이 늘어갈 때마다 조인으로 붙이고 집합의 결과 단위가 달라지면 Group by. 추가 정보가 필요하면 Union all 로 붙이고.. 무한 반복.
• 옵티마이저에 무지한 상태에서 어설픈 방법으로 복잡한 SQL을 작성하면 안됨

• 자신이 작성한 SQL이 개략적으로라도 어떤 형식으로 수행하며, 어느정도 처리량으로 수행될 것인지 파악할 수 있는 힘이 바탕이 되어야 함