문서의 선택한 두 판 사이의 차이를 보여줍니다.
양쪽 이전 판 이전 판 다음 판 | 이전 판 마지막 판 양쪽 다음 판 | ||
study:oracle:datadb:2week_1:chap4 [2010/05/13 15:11] starlits |
study:oracle:datadb:2week_1:chap4 [2010/05/13 18:47] starlits |
||
---|---|---|---|
줄 5: | 줄 5: | ||
===== 이행성 규칙(Transitivity principle) ===== | ===== 이행성 규칙(Transitivity principle) ===== | ||
+ | - 조건절에 같은 컬럼을 사용한 두 개의 조건식이 있다면 옵티마이져는 새로운 조건식을 이행성규칙에 따라 생성하고 이 조건식을 이용하여 최적화를 수행 | ||
+ | - <color blue> | ||
+ | - 조건식 형식< | ||
+ | WHERE column1 comparison_operators | ||
+ | AND column1 = column2 | ||
+ | </ | ||
+ | - comparison_operators : =, !=, ^=, <, <>, >, <=, >= 중 하나 | ||
+ | - constant : 연산, SQL 함수, 문자열, 바인드 변수, 상관관계 변수를 포함하는 상수 수식 | ||
+ | |||
+ | ==== [예1] 이행성 규칙 ==== | ||
+ | - <color blue> | ||
+ | - <code sql> | ||
+ | SELECT * | ||
+ | FROM emp e, dept d | ||
+ | WHERE e.deptno = 20 | ||
+ | AND e.deptno = d.deptno; | ||
+ | </ | ||
+ | - 만약 e.deptno 인덱스가 없었다면, | ||
+ | - 이행성 규칙에 의해 실행계획이 DEPT 테이블을 먼저 인덱스로 액세스하는 실행계획이 가능 | ||
+ | |||
+ | === 비교하는 대상이 상수 수식이 아닌 컬럼인 경우는 이행이 일어나지 않음 === | ||
+ | <code sql> | ||
+ | WHERE column1 comparison_operators | ||
+ | AND column1 = column2 | ||
+ | </ | ||
+ | |||
+ | ==== [예2] 이행성 규칙 - OR조건을 가진 UNION ALL 사용 ==== | ||
+ | - OR를 가지는 조건들을 UNION ALL을 사용한 복합 문장으로 변환하는 경우이다 | ||
+ | - UNION ALL로 각각의 인덱스를 경유하는 실행계획을 수립하고 이를 결합 | ||
+ | - 이런 현상은 IN 을 사용한 상수수식 비교에서도 유사하게 나타난다 \\ 그 이유는 이런한 형태의 IN은 OR로 변환되기 때문이다 | ||
+ | |||
+ | === 변환기준 === | ||
+ | - OR를 사용한 조건절을 분기시켰을 때 각각이 인덱스 접근 경로로 이용할 수 있다면 변환을 수행 | ||
+ | - 인덱스 사용불가로 전체 테이블을 스캔 하거나, OR 조건이 검증기능만 수행하면 변환하지 않음 | ||
+ | - 즉, 분기된 각각의 액세스가 불필요한 범위를 액세스하지 않는다고 판단한 경우에만 적용 | ||
+ | |||
+ | === 실행계획 === | ||
+ | - **IN-LIST ITERATOR**나 **CONCATENATION**으로 실행계획이 나타남 | ||
+ | - 강제적인 적용을 위해 ‘USE_CONCAT’ 힌트를 사용. | ||
+ | - 함부로 사용하면 액세스가 불필요하게 분기되어 오히려 처리량이 크게 증가 | ||
+ | |||
+ | == SQL == | ||
+ | <code sql> | ||
+ | SELECT * | ||
+ | FROM emp | ||
+ | WHERE job = ' | ||
+ | OR deptno = 10; | ||
+ | </ | ||
+ | |||
+ | == SQL 변환 == | ||
+ | <code sql> | ||
+ | SELECT * FROM emp WHERE deptno = 10 | ||
+ | UNION ALL | ||
+ | SELECT * FROM emp WHERE job = ' | ||
+ | </ | ||
+ | |||
+ | ==== [예3] 이행성 규칙 - 서버쿼리의 변환 ==== | ||
+ | |||
+ | - 서비쿼리를 가진 복잡한 문장을 조인 문장으로 변환하는 경우 | ||
+ | - 최적화하기 위해서는 2가지 방법 중 하나를 선택 | ||
+ | |||
+ | <code sql> | ||
+ | SELECT * | ||
+ | FROM emp | ||
+ | WHERE deptno IN (SELECT deptno | ||
+ | FROM dept | ||
+ | WHERE loc = ‘NEW YORK’) ; | ||
+ | </ | ||
+ | |||
+ | |||
+ | === 같은 기능의 조인 문장으로 변환하여 다시 이를 최적화 === | ||
+ | - 서브쿼리 <color blue> | ||
+ | |||
+ | <code sql> | ||
+ | SELECT emp.* | ||
+ | FROM emp, dept | ||
+ | WHERE emp.deptno = dept.deptno | ||
+ | AND dept.loc = ‘NEW YORK’ ; | ||
+ | </ | ||
+ | |||
+ | === 현재 문장을 그대로 최적화 === | ||
+ | - 제공자역할 | ||
+ | - 서브쿼리를 먼저 수행하여 결과를 메인쿼리에 제공 | ||
+ | - 서브쿼리 실행결과를 메인쿼리의 처리주관 인덱스에 제공할 수 있을 때 적용 | ||
+ | - 확인자 역할 | ||
+ | - 메인쿼리가 실행되면서 서브쿼리를 확인하는 필터형 처리 | ||
+ | - 각의 집합을 액세스한 후 Merge하거나 혹은 Hash Join으로 수행 | ||
+ | |||
+ | |||
+ | |||
+ | |||
===== 뷰병합(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 ' | ||
+ | </ | ||
+ | - 뷰쿼리를 먼저 처리한 후 조인 | ||
+ | |||
+ | |||
+ | ==== 인라인 서브쿼리의 병합 ==== | ||
===== 사용자 정의 바인드 변수의 엿보기(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> |