문서의 선택한 두 판 사이의 차이를 보여줍니다.
양쪽 이전 판 이전 판 다음 판 | 이전 판 | ||
study:oracle:datadb:2week_1:chap4 [2010/05/13 17:53] starlits |
study:oracle:datadb:2week_1:chap4 [2010/05/13 18:52] (현재) starlits |
||
---|---|---|---|
줄 76: | 줄 76: | ||
=== 같은 기능의 조인 문장으로 변환하여 다시 이를 최적화 === | === 같은 기능의 조인 문장으로 변환하여 다시 이를 최적화 === | ||
- | - 서브쿼리가 <color blue> | + | - 서브쿼리 <color blue> |
<code sql> | <code sql> | ||
줄 98: | 줄 98: | ||
===== 뷰병합(View Merging) ===== | ===== 뷰병합(View Merging) ===== | ||
+ | |||
+ | - 인라인뷰나 뷰를 최적화하기 위해 사용하는 뷰와 관련된 변환이다. | ||
+ | - 뷰를 사용하는 쿼리는 크게 뷰쿼리와 액세스쿼리로 나눈다 | ||
+ | |||
+ | ^ 뷰쿼리 | ||
+ | ^ 액세스쿼리 | ||
+ | |||
+ | ^ 뷰병합(View Merging)법 | ||
+ | ^ 조건절 진입(Predicate pushing)법 | ||
+ | |||
+ | |||
+ | ==== 뷰쿼리를 액세스쿼리로 병합 ==== | ||
+ | |||
+ | == 뷰쿼리 == | ||
+ | <code sql> | ||
+ | CREATE VIEW emp_10 | ||
+ | (e_no, e_name, job, manager, salary, commission, deptno) | ||
+ | | ||
+ | SELECT empno, ename, job, mgr, sal, comm, deptno | ||
+ | FROM emp | ||
+ | WEHRE deptno = 10; | ||
+ | </ | ||
+ | |||
+ | == 액세스쿼리 == | ||
+ | <code sql> | ||
+ | SELECT e_no, e_name, salary, deptno | ||
+ | FROM emp_10 | ||
+ | WHERE salary > 1000000; | ||
+ | </ | ||
+ | |||
+ | == 뷰쿼리를 액세스쿼리에 병합 == | ||
+ | <code sql> | ||
+ | SELECT empno, ename, sal, hiredate | ||
+ | FROM emp | ||
+ | WHERE deptno = 10 | ||
+ | AND sal > 1000000; | ||
+ | </ | ||
+ | - 액세스쿼리에 있는 뷰를 원래 테이블인 emp로 변환 | ||
+ | - 남아있는 조건절을 다시 액세스쿼리에 병합 | ||
+ | - 컬럼들도 대응되는 원래 테이블의 컬럼들로 병합 | ||
+ | |||
+ | * <color blue> | ||
+ | |||
+ | |||
+ | ==== View Merging이 불가능한 경우 ==== | ||
+ | - 집합 연산 (UNION, UNION ALL, INTERSECT, MINUS) | ||
+ | - CONNECT BY | ||
+ | - ROWNUM을 사용한 경우 | ||
+ | - SELECT-List의 그룹함수(AVG, | ||
+ | - 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 | ||
+ | | ||
+ | FROM regular_emp | ||
+ | UNION ALL | ||
+ | | ||
+ | FROM temporary_emp; | ||
+ | </ | ||
+ | |||
+ | == 액세스쿼리 == | ||
+ | <code sql> | ||
+ | SELECT e_no, e_name, mgr, sal | ||
+ | FROM emp_union_view | ||
+ | WHERE deptno = 20; | ||
+ | </ | ||
+ | |||
+ | |||
+ | == 병합쿼리 == | ||
+ | <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 ); | ||
+ | </ | ||
+ | - 뷰쿼리가 기준이 되어 뷰쿼리의 구조에 액세스쿼리의 조건들을 진입시키는 변환 | ||
+ | - 액세스쿼리에 있는 조건들이 뷰쿼리의 각 SELECT문의 조건절 속으로 파고들어감(Pushing) | ||
+ | - 액세스쿼리의 컬럼들에 대응되는 뷰쿼리의 컬럼 선택 | ||
+ | - WHERE 90 = 20 은 항상 거짓이므로 실행되지 않음 | ||
+ | |||
+ | |||
+ | |||
+ | ==== GROUP BY 뷰의 병합 ==== | ||
+ | - Pushing predicate Merge | ||
+ | |||
+ | == 뷰쿼리 == | ||
+ | <code sql> | ||
+ | CREATE VIEW emp_group_by_deptno | ||
+ | AS | ||
+ | | ||
+ | | ||
+ | FROM emp | ||
+ | GROUP BY deptno; | ||
+ | </ | ||
+ | - 전체 테이블 로우에 대해 GROUP BY 한 것처럼 보이는 뷰 | ||
+ | |||
+ | == 액세스쿼리 == | ||
+ | <code sql> | ||
+ | SELECT * | ||
+ | FROM emp_group_by_deptno | ||
+ | WHERE deptno = 10; | ||
+ | </ | ||
+ | |||
+ | == 병합 == | ||
+ | <code sql> | ||
+ | SELECT deptno, AVG(sal) avg_sal, | ||
+ | MIN(sal) min_sal, | ||
+ | FROM emp | ||
+ | WHERE deptno = 10 | ||
+ | GROUP BY deptno; | ||
+ | </ | ||
+ | - Complex_view_merging , Optimizer_secure_view_merging 파라메터가 Enabled 되어 있으면 가능하다 | ||
+ | - 조건을 만족하는 대상들만 GROUP BY | ||
+ | |||
+ | * <color red> | ||
+ | |||
+ | |||
+ | |||
+ | ==== 액세스 쿼리의 병합 ==== | ||
+ | |||
+ | == 액세스쿼리 == | ||
+ | <code sql> | ||
+ | SELECT d.loc, | ||
+ | FROM dept d, emp_group_by_deptno v | ||
+ | WHERE d.deptno = v.deptno | ||
+ | AND d.loc = ' | ||
+ | </ | ||
+ | |||
+ | === 액세스 쿼리의 정상적인 병합 === | ||
+ | |||
+ | == 병합된 쿼리 == | ||
+ | <code sql> | ||
+ | SELECT dept.loc, AVG(sal) | ||
+ | FROM dept, emp | ||
+ | WHERE dept.deprtno = emp.deptno | ||
+ | AND dept.loc = ' | ||
+ | GROUP BY dept.rowid, dept.loc; | ||
+ | </ | ||
+ | == 실행계획 == | ||
+ | <code sql> | ||
+ | SORT (GROUP BY) | ||
+ | | ||
+ | TABLE ACCESS (FULL) OF ' | ||
+ | TABLE ACCESS (BY INDEX ROWID) OF ' | ||
+ | INDEX (RANGE SCAN) OF ' | ||
+ | </ | ||
+ | - 조건처리가 완료된 후 GROUP BY | ||
+ | |||
+ | === 액세스 쿼리의 병합이 안 일어나는 경우 === | ||
+ | == 액세스쿼리 == | ||
+ | <code sql> | ||
+ | SELECT /*+ NO_MERGE(v) */ d.loc, | ||
+ | FROM dept d, emp_group_by_deptno v | ||
+ | WHERE d.deptno = v.deptno | ||
+ | AND d.loc = ' | ||
+ | </ | ||
+ | |||
+ | == 병합쿼리 == | ||
+ | <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 = ' | ||
+ | </ | ||
+ | |||
+ | == 실행계획 == | ||
+ | <code sql> | ||
+ | HASH JOIN | ||
+ | TABLE ACCESS (FULL) OF ' | ||
+ | VIEW OF ' | ||
+ | SORT (GROUP BY) | ||
+ | TABLE ACCESS (FULL) OF ' | ||
+ | </ | ||
+ | - 뷰쿼리를 먼저 처리한 후 조인 | ||
+ | |||
+ | |||
+ | ==== 인라인 서브쿼리의 병합 ==== | ||
+ | |||
+ | |||
+ | == 뷰쿼리 == | ||
+ | <code sql> | ||
+ | CREATE VIEW emp_group_by_deptno | ||
+ | AS | ||
+ | | ||
+ | | ||
+ | | ||
+ | FROM emp | ||
+ | GROUP BY deptno; | ||
+ | </ | ||
+ | |||
+ | == 액세스쿼리 == | ||
+ | <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 = ' | ||
+ | </ | ||
+ | - 실행계획을 수립하는 입장에서 보면 반드시 평균급여를 미리 구해야 할 필요는 없다 | ||
+ | |||
+ | == MERGE 힌트사용시 == | ||
+ | <code sql> | ||
+ | FILTER | ||
+ | SORT (GROUP BY) | ||
+ | HASH JOIN | ||
+ | TABLE ACCESS (FULL) OF ' | ||
+ | TABLE ACCESS (FULL) OF ' | ||
+ | </ | ||
+ | |||
+ | == NO_MERGE 힌트사용시== | ||
+ | <code sql> | ||
+ | HASH JOIN | ||
+ | TABLE ACCESS (FULL) OF ' | ||
+ | | ||
+ | SORT (GROUP BY) | ||
+ | TABLE ACCESS (FULL) OF ' | ||
+ | </ | ||
+ | - 뷰병합을 하지 않는 것이 유리 | ||
+ | |||
+ | === 결론 === | ||
+ | - 처리범위를 줄일 수 있는 조건들이 많아 인라인뷰로 파고들어 가는 것이 일 량을 줄일 수 있다면 뷰병합이 유리 | ||
+ | - 파라메터는 기본값을 ‘TRUE’로 하고, 필요하다면 NO_MERGE 힌트를 적용하는 것이 바람직함 | ||
===== 사용자 정의 바인드 변수의 엿보기(Peeking) ===== | ===== 사용자 정의 바인드 변수의 엿보기(Peeking) ===== | ||
+ | - 바인드 변수를 사용한 쿼리는 먼저 파싱과 최적화가 이루어진 후에 바인드 변수에 바인딩이 이루어진다 | ||
+ | - 최적화가 이루어지는 시점에는 변수로 제공되는 컬럼은 바인딩 값에 대한 통계정보를 사용할 수 없다는 것을 의미 | ||
+ | - **분포도가 균일하지 못한 컬럼에 바인드 변수를 사용하면 최악의 실행계획이 생성될 수도 있다** | ||
+ | |||
+ | ==== Peeking ==== | ||
+ | - 바인드 변수를 사용한 쿼리가 처음 실행될 때 \\ 옵티마이져는 사용자가 지정한 바인드 변수의 값을 살짝 커닝함으로써 \\ 조건절의 컬럼값이 상수값으로 제공될 때와 마찬가지로 선택도를 확인하여 최적화를 수행 | ||
+ | - 첫번째 파싱 : 공유 SQL 영역에 처음 등록될 때 | ||
+ | |||
+ | ==== Peeking의 비효율성 ==== | ||
+ | === 성별 컬럼에 M 이 99% 이고, F가 1% 들어있는 경우 === | ||
+ | <code sql> SELECT name FROM patients WHERE sex = : | ||
+ | == 평균적용 == | ||
+ | - 인덱스 미사용 | ||
+ | == M이 첫번째일때 == | ||
+ | - 인덱스 미사용 <code sql> WHERE sex =‘M’; </ | ||
+ | == F가 첫번째일때 == | ||
+ | - 인덱스 사용 <code sql> WHERE sex =‘F’; </ | ||
+ | === === | ||
+ | * <color red> |