차이

문서의 선택한 두 판 사이의 차이를 보여줍니다.

--- study:oracle:datadb:2week_1:chap4 [2010/05/13 17:10]
starlits
+++ study:oracle:datadb:2week_1:chap4 [2010/05/13 18:52] (현재)
starlits
@@ 줄 34: / 줄 34: @@
   - OR를 가지는 조건들을 UNION ALL을 사용한 복합 문장으로 변환하는 경우이다
   - UNION ALL로 각각의 인덱스를 경유하는 실행계획을 수립하고 이를 결합
+  - 이런 현상은 IN 을 사용한 상수수식 비교에서도 유사하게 나타난다 \\ 그 이유는 이런한 형태의 IN은 OR로 변환되기 때문이다
 === 변환기준 ===
@@ 줄 45: / 줄 46: @@
   - 함부로 사용하면 액세스가 불필요하게 분기되어 오히려 처리량이 크게 증가
+== SQL ==
+<code sql>
+SELECT *
+FROM emp
+WHERE job = 'CLERK'
+  OR  deptno = 10;
+</code>
+== SQL 변환 ==
+<code sql>
+SELECT * FROM emp WHERE deptno = 10
+UNION ALL
+SELECT * FROM emp WHERE job = 'CLERK‘ and deptno <> 10;
+</code>
 ==== [예3] 이행성 규칙 - 서버쿼리의 변환 ====
+  - 서비쿼리를 가진 복잡한 문장을 조인 문장으로 변환하는 경우
+  - 최적화하기 위해서는 2가지 방법 중 하나를 선택
+<code sql>
+SELECT *
+FROM  emp
+WHERE deptno IN (SELECT deptno
+                 FROM dept
+                 WHERE loc = ‘NEW YORK’) ;
+</code>
+=== 같은 기능의 조인 문장으로 변환하여 다시 이를 최적화 ===
+  - 서브쿼리 <color blue>**SELECT deptno FROM dept WHERE loc = ‘NEW YORK’**</color>는 집합이므로 조인을 해도 메인쿼리에 영향을 주지 않음.
+<code sql>
+SELECT emp.*
+FROM   emp, dept
+WHERE emp.deptno = dept.deptno
+  AND dept.loc = ‘NEW YORK’ ;
+</code>
+=== 현재 문장을 그대로 최적화 ===
+  - 제공자역할
+    - 서브쿼리를 먼저 수행하여 결과를 메인쿼리에 제공
+    - 서브쿼리 실행결과를 메인쿼리의 처리주관 인덱스에 제공할 수 있을 때 적용
+  - 확인자 역할
+    - 메인쿼리가 실행되면서 서브쿼리를 확인하는 필터형 처리
+    - 각의 집합을 액세스한 후 Merge하거나 혹은 Hash Join으로 수행
 ===== 뷰병합(View Merging) =====
+  - 인라인뷰나 뷰를 최적화하기 위해 사용하는 뷰와 관련된 변환이다.
+  - 뷰를 사용하는 쿼리는 크게 뷰쿼리와 액세스쿼리로 나눈다
+^ 뷰쿼리  | 뷰를 생성할때 사용한 SELECT 문 \\ dictionary에 저장된 select문 |
+^ 액세스쿼리  | 이 뷰를 수행하는 SQL문 |
+^ 뷰병합(View Merging)법  | 뷰쿼리를 액세스쿼리에 병합해 넣는 방식 |
+^ 조건절 진입(Predicate pushing)법  | 뷰병합을 할 수 없는 경우를 대상으로 \\ 뷰쿼리 내부에 액세스쿼리의 조건절을 진입시키는 방식 |
+==== 뷰쿼리를 액세스쿼리로 병합 ====
+== 뷰쿼리 ==
+<code sql>
+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;
+</code>
+== 액세스쿼리 ==
+<code sql>
+SELECT e_no, e_name, salary, deptno
+FROM  emp_10
+WHERE salary > 1000000;
+</code>
+== 뷰쿼리를 액세스쿼리에 병합 ==
+<code sql>
+SELECT empno, ename, sal, hiredate
+FROM emp
+WHERE  deptno = 10
+     AND sal        > 1000000;
+</code>
+  - 액세스쿼리에 있는 뷰를 원래 테이블인 emp로 변환
+  - 남아있는 조건절을 다시 액세스쿼리에 병합
+  - 컬럼들도 대응되는 원래 테이블의 컬럼들로 병합
+  * <color blue>만약 EMP 테이블에 DEPTNO + SAL 로 구성된 인덱스가 있었다면 \\ 변환된 SQL에 의해 완벽한 실행계획을 갖게 된다</color>
+==== View Merging이 불가능한 경우 ====
+  - 집합 연산 (UNION, UNION ALL, INTERSECT, MINUS)
+  - CONNECT BY
+  - ROWNUM을 사용한 경우
+  - SELECT-List의 그룹함수(AVG, COUNT, MAX, MIN, SUM)
+  - GROUP BY (단, Merge 힌트를 사용했거나 관련 파라메터가 Enable 이면 뷰병합가능)
+  - SELECT-List의 DISTINCT (단, Merge 힌트를 사용했거나 관련 파라메터가 Enable 이면 뷰병합가능)
+==== 조건절 진입(Predicate pushing) ====
+  - 뷰병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 액세스쿼리의 조건절을 진입시키는 방식
+== 뷰쿼리 ==
+<code sql>
+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;
+</code>
+== 액세스쿼리 ==
+<code sql>
+SELECT e_no, e_name, mgr, sal
+FROM  emp_union_view
+WHERE deptno = 20;
+</code>
+== 병합쿼리 ==
+<code sql>
+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  );
+</code>
+  - 뷰쿼리가 기준이 되어 뷰쿼리의 구조에 액세스쿼리의 조건들을 진입시키는 변환
+  - 액세스쿼리에 있는 조건들이 뷰쿼리의 각 SELECT문의 조건절 속으로 파고들어감(Pushing)
+  - 액세스쿼리의 컬럼들에 대응되는 뷰쿼리의 컬럼 선택
+  - WHERE 90 = 20 은 항상 거짓이므로 실행되지 않음
+==== GROUP BY 뷰의 병합 ====
+  - Pushing predicate Merge
+== 뷰쿼리 ==
+<code sql>
+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;
+</code>
+  - 전체 테이블 로우에 대해 GROUP BY 한 것처럼 보이는 뷰
+== 액세스쿼리 ==
+<code sql>
+SELECT *
+FROM emp_group_by_deptno
+WHERE deptno = 10;
+</code>
+== 병합 ==
+<code sql>
+SELECT deptno, AVG(sal) avg_sal,
+            MIN(sal) min_sal,MAX(sal) max_sal
+FROM emp
+WHERE deptno = 10
+GROUP BY deptno;
+</code>
+  - Complex_view_merging , Optimizer_secure_view_merging 파라메터가 Enabled 되어 있으면 가능하다
+  - 조건을 만족하는 대상들만 GROUP BY
+  * <color red>만약 액세스쿼리에서 특정일을 조건으로 주었지만 이 조건이 병합되지 못한다면 ...</color>
+==== 액세스 쿼리의 병합 ====
+== 액세스쿼리 ==
+<code sql>
+SELECT d.loc,v.avg_sal
+FROM  dept d, emp_group_by_deptno v
+WHERE d.deptno = v.deptno
+AND d.loc = 'London';
+</code>
+=== 액세스 쿼리의 정상적인 병합 ===
+== 병합된 쿼리 ==
+<code sql>
+SELECT dept.loc, AVG(sal)
+FROM dept, emp
+WHERE dept.deprtno = emp.deptno
+    AND dept.loc = 'London'
+GROUP BY dept.rowid, dept.loc;
+</code>
+== 실행계획 ==
+<code sql>
+SORT (GROUP BY)
+ NESTED LOOPS
+   TABLE ACCESS (FULL) OF 'EMP'
+   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
+     INDEX (RANGE SCAN) OF 'PK_DEPT'(UNIQUE)
+</code>
+  - 조건처리가 완료된 후 GROUP BY
+=== 액세스 쿼리의 병합이 안 일어나는 경우 ===
+== 액세스쿼리 ==
+<code sql>
+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';
+</code>
+== 병합쿼리 ==
+<code sql>
+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' ;
+</code>
+== 실행계획 ==
+<code sql>
+HASH JOIN
+  TABLE ACCESS (FULL) OF 'DEPT'
+   VIEW OF 'EMP_GROUP_BY_DEPTNO'
+     SORT (GROUP BY)
+       TABLE ACCESS (FULL) OF 'EMP'
+</code>
+  - 뷰쿼리를 먼저 처리한 후 조인
+==== 인라인 서브쿼리의 병합 ====
+== 뷰쿼리 ==
+<code sql>
+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;
+</code>
+== 액세스쿼리 ==
+<code sql>
+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' ;
+</code>
+  - 실행계획을 수립하는 입장에서 보면 반드시 평균급여를 미리 구해야 할 필요는 없다
+== MERGE 힌트사용시 ==
+<code sql>
+FILTER
+ SORT (GROUP BY)
+  HASH JOIN
+    TABLE ACCESS (FULL) OF 'EMP'
+    TABLE ACCESS (FULL) OF 'EMP'
+</code>
+== NO_MERGE 힌트사용시==
+<code sql>
+HASH JOIN
+ TABLE ACCESS (FULL) OF 'EMP'
+ VIEW
+   SORT (GROUP BY)
+     TABLE ACCESS (FULL) OF 'EMP'
+</code>
+  - 뷰병합을 하지 않는 것이 유리
+=== 결론 ===
+  - 처리범위를 줄일 수 있는 조건들이 많아 인라인뷰로 파고들어 가는 것이 일 량을 줄일 수 있다면 뷰병합이 유리
+  - 파라메터는 기본값을 ‘TRUE’로 하고, 필요하다면 NO_MERGE 힌트를 적용하는 것이 바람직함
 ===== 사용자 정의 바인드 변수의 엿보기(Peeking) =====
+  - 바인드 변수를 사용한 쿼리는 먼저 파싱과 최적화가 이루어진 후에 바인드 변수에 바인딩이 이루어진다
+  - 최적화가 이루어지는 시점에는 변수로 제공되는 컬럼은 바인딩 값에 대한 통계정보를 사용할 수 없다는 것을 의미
+  - **분포도가 균일하지 못한 컬럼에 바인드 변수를 사용하면 최악의 실행계획이 생성될 수도 있다**
+==== Peeking ====
+  - 바인드 변수를 사용한 쿼리가 처음 실행될 때 \\ 옵티마이져는 사용자가 지정한 바인드 변수의 값을 살짝 커닝함으로써 \\ 조건절의 컬럼값이 상수값으로 제공될 때와 마찬가지로 선택도를 확인하여 최적화를 수행
+  - 첫번째 파싱 : 공유 SQL 영역에 처음 등록될 때
+==== Peeking의 비효율성 ====
+=== 성별 컬럼에 M 이 99% 이고, F가 1% 들어있는 경우 ===
+<code sql> SELECT name FROM patients WHERE sex = :input_sex_value ;</code>
+== 평균적용 ==
+  - 인덱스 미사용
+== M이 첫번째일때 ==
+  - 인덱스 미사용 <code sql> WHERE sex =‘M’; </code>
+== F가 첫번째일때 ==
+  - 인덱스 사용 <code sql> WHERE sex =‘F’; </code>
+=== ===
+* <color red>**한가지 별도로 쿼리의 실행계획을 확인했을때와 실제로 수행된 실행계획은 다를 수 있다.**</color>

adminschool.net wiki

사용자 도구

사이트 도구

차이

문서 도구