3.1.4. 질의의 변환(Query Transforming)

• 질의 변환 Case

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

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

1. Like 단순화
   LIKE를 사용한 조건절에서 '%' 나 '_'를 사용하지 않는 경우에는 '=' 연산으로 조건절 단순화
   가변길이 데이터타입일 때만 이러한 수식의 단순화가 가능
   
   
   
   
2. 조건식에 IN, OR을 이용한 Case
   IN 비교 연산자를 사용한 문장에서는 OR 논리 연산자를 이용해 여러개의 '='로 된 같은기능의 조건절로 확장

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

1. 조건식에 ANY, SOME 을 이용한 Case
   '=' 비교연산자와 OR논리 연산자를 이용해 같은 기능의 조건절로 확장

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

1. 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)

1. 조건식에 ALL 을 이용한 Case
   조건절은 '=' 연산자와 AND 논리 연산자를 사용해 같은 기능의 조건절로 변환

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

1. 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)

1. 조건식에 BETWEEN 를 이용한 Case
   '>=', '<=' 비교 연산자를 사용하여 변환

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

1. 조건식에 NOT를 이용한 Case
   논리 연산자를 제거할 수 있는 반대 비교연산을 찾아 대체시키는 변환

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

1. 서브쿼리에 사용된 NOT을 변환하는 Case
   NOT를 없애기 위해 반대 비교연산을 찾아 대체시키는 변환

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

3.1.4.1 이행성규칙(Transitivity principle)

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

• • 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를 사용한 조건절은 그 문장에 대한 변환을 하지 않음

3.1.4.2 뷰병합(View Merging)

• 뷰를 사용하는 쿼리

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

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

1. 뷰병합(View Merging)법: 뷰쿼리를 액세스쿼리에 병합해 넣는방식
2. 조건절 진입(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

• 조건절 진입의 경우

1. 집합연산의 경우

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

1. 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;

1. 복잡한 뷰 내부에 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) ;

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

• 바인드 변수를 사용한 커리가 처음 실행될 때 옵티마이져는 사용자가 지정한 바인드 변의 값을 '살짝 커닝'함으로써 조건절의 컬럼값이 상수값으로 제공될때와 마찬가지로 선택도를 확인하여 최적화를 수행하도록 한다.
• 최초에 실질적인 파싱이 일어날때만 단 한번 변수의 값을 PEEKING 함
• PEEKING의 적용 여부는 _OPTIM_PEEK_USER_BINDS 파라메터로 결정
  
  
• 현실적으로 최초의 단 한 가지 경우의 선택도를 사용하는 것이나 전체를 평균적인 분포로 보는 것이나 논리적으로 오류일 확률은 다르지 않음
  : 11g에서는 Adaptive Cursor Sharing 기능으로 이러한 문제를 많이 해결함

3.1.5 개발자의 역할

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

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