by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

3.1.4. 질의의 변환(Query Transforming)




3.1.4. 질의의 변환(Query Transforming)

  • 질의 변환 Case
  1. 상수 변환
    옵티마이져는 가능한 모든 수식의 값을 미리 구함
    상수의 연산을 실행될 때마다 이루어지는 것이 아니라 질의 변환단계에서 한 번만 수행
      예)
        ① sales_qty > 1200/12
        ② sales_qty > 100
        ③ sales_qty*12  > 1200
          
        ①,② 는 동일하게 취급 - 사전에 최대한의 연산을 하여 두 번째 조건식이 됨
        ③은 연산을 하여 ② 조건식을 만들지 않음 - 비교연산자의 추가적 연산은 연산자를 좌우로 이동시켜 단순화를 하지는 않기 때문
    


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

  3. 조건식에 IN, OR을 이용한 Case
    IN 비교 연산자를 사용한 문장에서는 OR 논리 연산자를 이용해 여러개의 '='로 된 같은기능의 조건절로 확장
      예)
        ① job IN ('CLERK','MANAGER')
        ② job  = 'CLERK' OR  job  = 'MANAGER'
    


  4. 조건식에 ANY, SOME 을 이용한 Case
    '=' 비교연산자와 OR논리 연산자를 이용해 같은 기능의 조건절로 확장
      예)
        ① sales_qty > ANY (:in_qty1, :in_qty2)
        ② sales_qty > :in_qty1  OR  sales_qty > :in_qty2
    


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


  6. 조건식에 ALL 을 이용한 Case
    조건절은 '=' 연산자와 AND 논리 연산자를 사용해 같은 기능의 조건절로 변환
      예)
        ① sales_qty > ALL (:in_qty1, :in_qty2)
        ② sales_qty > :in_qty1  AND  sales_qty > :in_qty2
    


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


  8. 조건식에 BETWEEN 를 이용한 Case
    '>=', '<=' 비교 연산자를 사용하여 변환
      예)
        ① sales_qty  BETWEEN 100 and 200
        ② sales_qty  >= 100 AND  sales_qty <= 200
    


  9. 조건식에 NOT를 이용한 Case
    논리 연산자를 제거할 수 있는 반대 비교연산을 찾아 대체시키는 변환
      예)
        ① NOT (sal < 30000 OR comm IS NULL)
        ② NOT  sal < 30000 AND comm IS NOT NULL)
        ③ sal >= 30000 AND comm IS NOT NULL
        
        ① →  ②  →  ③  순서로 변환
    


  10. 서브쿼리에 사용된 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)

  • 조건절에 같은 컬럼을 사용한 두 개의 조건식이 있다면 옵티마이져는 새로운 조건식을 이행성 규칙에 따라 생성하고 이 조건식을 이용하여 최적화를 수행
    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를 사용한 조건절은 그 문장에 대한 변환을 하지 않음
      이행성규칙은 효율적이라고 판된될 때만 변환이 일어난다.


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


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

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을 작성하면 안됨


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

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.