차이

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

--- study:oracle:datadb:2week_1:chap4 [2010/05/13 18:25]
starlits
+++ study:oracle:datadb:2week_1:chap4 [2010/05/13 18:52] (현재)
starlits
@@ 줄 139: / 줄 139: @@
   - 컬럼들도 대응되는 원래 테이블의 컬럼들로 병합
-* <code blue>만약 EMP 테이블에 DEPTNO + SAL 로 구성된 인덱스가 있었다면 \\ 변환된 SQL에 의해 완벽한 실행계획을 갖게 된다</color>
+  * <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) =====

adminschool.net wiki

사용자 도구

사이트 도구

차이

문서 도구