====== 제 3장 SQL의 실행계획 - 질의의 변환(Query Transforming) ====== - 옵티마이져는 가능한 모든 수식의 값을 미리 구한다 - **보다 양호한 실행계획을 얻을 수 있도록 가능한 최대로 적절하게 SQL 형태를 변환하는 것이 중요하다** ===== 이행성 규칙(Transitivity principle) ===== - 조건절에 같은 컬럼을 사용한 두 개의 조건식이 있다면 옵티마이져는 새로운 조건식을 이행성규칙에 따라 생성하고 이 조건식을 이용하여 최적화를 수행 - **원래의 조건식에서는 인덱스를 사용하지 못하게 되었더라도 인덱스를 사용하는 실행계획이 나타날 수 있다** - 조건식 형식 WHERE column1 comparison_operators constant AND column1 = column2 - comparison_operators : =, !=, ^=, <, <>, >, <=, >= 중 하나 - constant : 연산, SQL 함수, 문자열, 바인드 변수, 상관관계 변수를 포함하는 상수 수식 ==== [예1] 이행성 규칙 ==== - 비용기준 업티마이져를 사용할 때만 적용 - SELECT * FROM emp e, dept d WHERE e.deptno = 20 AND e.deptno = d.deptno; - 만약 e.deptno 인덱스가 없었다면, EMP테이블을 전체 테이블 액세스를 하면서 매번 dept 테이블 연결 - 이행성 규칙에 의해 실행계획이 DEPT 테이블을 먼저 인덱스로 액세스하는 실행계획이 가능 === 비교하는 대상이 상수 수식이 아닌 컬럼인 경우는 이행이 일어나지 않음 === WHERE column1 comparison_operators column3 AND column1 = column2 ==== [예2] 이행성 규칙 - OR조건을 가진 UNION ALL 사용 ==== - OR를 가지는 조건들을 UNION ALL을 사용한 복합 문장으로 변환하는 경우이다 - UNION ALL로 각각의 인덱스를 경유하는 실행계획을 수립하고 이를 결합 - 이런 현상은 IN 을 사용한 상수수식 비교에서도 유사하게 나타난다 \\ 그 이유는 이런한 형태의 IN은 OR로 변환되기 때문이다 === 변환기준 === - OR를 사용한 조건절을 분기시켰을 때 각각이 인덱스 접근 경로로 이용할 수 있다면 변환을 수행 - 인덱스 사용불가로 전체 테이블을 스캔 하거나, OR 조건이 검증기능만 수행하면 변환하지 않음 - 즉, 분기된 각각의 액세스가 불필요한 범위를 액세스하지 않는다고 판단한 경우에만 적용 === 실행계획 === - **IN-LIST ITERATOR**나 **CONCATENATION**으로 실행계획이 나타남 - 강제적인 적용을 위해 ‘USE_CONCAT’ 힌트를 사용. - 함부로 사용하면 액세스가 불필요하게 분기되어 오히려 처리량이 크게 증가 == SQL == SELECT * FROM emp WHERE job = 'CLERK' OR deptno = 10; == SQL 변환 == SELECT * FROM emp WHERE deptno = 10 UNION ALL SELECT * FROM emp WHERE job = 'CLERK‘ and deptno <> 10; ==== [예3] 이행성 규칙 - 서버쿼리의 변환 ==== - 서비쿼리를 가진 복잡한 문장을 조인 문장으로 변환하는 경우 - 최적화하기 위해서는 2가지 방법 중 하나를 선택 SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc = ‘NEW YORK’) ; === 같은 기능의 조인 문장으로 변환하여 다시 이를 최적화 === - 서브쿼리 **SELECT deptno FROM dept WHERE loc = ‘NEW YORK’**는 집합이므로 조인을 해도 메인쿼리에 영향을 주지 않음. SELECT emp.* FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.loc = ‘NEW YORK’ ; === 현재 문장을 그대로 최적화 === - 제공자역할 - 서브쿼리를 먼저 수행하여 결과를 메인쿼리에 제공 - 서브쿼리 실행결과를 메인쿼리의 처리주관 인덱스에 제공할 수 있을 때 적용 - 확인자 역할 - 메인쿼리가 실행되면서 서브쿼리를 확인하는 필터형 처리 - 각의 집합을 액세스한 후 Merge하거나 혹은 Hash Join으로 수행 ===== 뷰병합(View Merging) ===== - 인라인뷰나 뷰를 최적화하기 위해 사용하는 뷰와 관련된 변환이다. - 뷰를 사용하는 쿼리는 크게 뷰쿼리와 액세스쿼리로 나눈다 ^ 뷰쿼리 | 뷰를 생성할때 사용한 SELECT 문 \\ dictionary에 저장된 select문 | ^ 액세스쿼리 | 이 뷰를 수행하는 SQL문 | ^ 뷰병합(View Merging)법 | 뷰쿼리를 액세스쿼리에 병합해 넣는 방식 | ^ 조건절 진입(Predicate pushing)법 | 뷰병합을 할 수 없는 경우를 대상으로 \\ 뷰쿼리 내부에 액세스쿼리의 조건절을 진입시키는 방식 | ==== 뷰쿼리를 액세스쿼리로 병합 ==== == 뷰쿼리 == 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; == 액세스쿼리 == SELECT e_no, e_name, salary, deptno FROM emp_10 WHERE salary > 1000000; == 뷰쿼리를 액세스쿼리에 병합 == SELECT empno, ename, sal, hiredate FROM emp WHERE deptno = 10 AND sal > 1000000; - 액세스쿼리에 있는 뷰를 원래 테이블인 emp로 변환 - 남아있는 조건절을 다시 액세스쿼리에 병합 - 컬럼들도 대응되는 원래 테이블의 컬럼들로 병합 * 만약 EMP 테이블에 DEPTNO + SAL 로 구성된 인덱스가 있었다면 \\ 변환된 SQL에 의해 완벽한 실행계획을 갖게 된다 ==== 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) ==== - 뷰병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 액세스쿼리의 조건절을 진입시키는 방식 == 뷰쿼리 == 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; == 액세스쿼리 == SELECT e_no, e_name, mgr, sal FROM emp_union_view WHERE deptno = 20; == 병합쿼리 == 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 == 뷰쿼리 == 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; - 전체 테이블 로우에 대해 GROUP BY 한 것처럼 보이는 뷰 == 액세스쿼리 == 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; - Complex_view_merging , Optimizer_secure_view_merging 파라메터가 Enabled 되어 있으면 가능하다 - 조건을 만족하는 대상들만 GROUP BY * 만약 액세스쿼리에서 특정일을 조건으로 주었지만 이 조건이 병합되지 못한다면 ... ==== 액세스 쿼리의 병합 ==== == 액세스쿼리 == SELECT d.loc,v.avg_sal FROM dept d, emp_group_by_deptno v WHERE d.deptno = v.deptno AND d.loc = 'London'; === 액세스 쿼리의 정상적인 병합 === == 병합된 쿼리 == SELECT dept.loc, AVG(sal) FROM dept, emp WHERE dept.deprtno = emp.deptno AND dept.loc = 'London' GROUP BY dept.rowid, dept.loc; == 실행계획 == SORT (GROUP BY) NESTED LOOPS TABLE ACCESS (FULL) OF 'EMP' TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' INDEX (RANGE SCAN) OF 'PK_DEPT'(UNIQUE) - 조건처리가 완료된 후 GROUP BY === 액세스 쿼리의 병합이 안 일어나는 경우 === == 액세스쿼리 == 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'; == 병합쿼리 == 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' ; == 실행계획 == HASH JOIN TABLE ACCESS (FULL) OF 'DEPT' VIEW OF 'EMP_GROUP_BY_DEPTNO' SORT (GROUP BY) TABLE ACCESS (FULL) OF 'EMP' - 뷰쿼리를 먼저 처리한 후 조인 ==== 인라인 서브쿼리의 병합 ==== == 뷰쿼리 == 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 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' ; - 실행계획을 수립하는 입장에서 보면 반드시 평균급여를 미리 구해야 할 필요는 없다 == MERGE 힌트사용시 == FILTER SORT (GROUP BY) HASH JOIN TABLE ACCESS (FULL) OF 'EMP' TABLE ACCESS (FULL) OF 'EMP' == NO_MERGE 힌트사용시== HASH JOIN TABLE ACCESS (FULL) OF 'EMP' VIEW SORT (GROUP BY) TABLE ACCESS (FULL) OF 'EMP' - 뷰병합을 하지 않는 것이 유리 === 결론 === - 처리범위를 줄일 수 있는 조건들이 많아 인라인뷰로 파고들어 가는 것이 일 량을 줄일 수 있다면 뷰병합이 유리 - 파라메터는 기본값을 ‘TRUE’로 하고, 필요하다면 NO_MERGE 힌트를 적용하는 것이 바람직함 ===== 사용자 정의 바인드 변수의 엿보기(Peeking) ===== - 바인드 변수를 사용한 쿼리는 먼저 파싱과 최적화가 이루어진 후에 바인드 변수에 바인딩이 이루어진다 - 최적화가 이루어지는 시점에는 변수로 제공되는 컬럼은 바인딩 값에 대한 통계정보를 사용할 수 없다는 것을 의미 - **분포도가 균일하지 못한 컬럼에 바인드 변수를 사용하면 최악의 실행계획이 생성될 수도 있다** ==== Peeking ==== - 바인드 변수를 사용한 쿼리가 처음 실행될 때 \\ 옵티마이져는 사용자가 지정한 바인드 변수의 값을 살짝 커닝함으로써 \\ 조건절의 컬럼값이 상수값으로 제공될 때와 마찬가지로 선택도를 확인하여 최적화를 수행 - 첫번째 파싱 : 공유 SQL 영역에 처음 등록될 때 ==== Peeking의 비효율성 ==== === 성별 컬럼에 M 이 99% 이고, F가 1% 들어있는 경우 === SELECT name FROM patients WHERE sex = :input_sex_value ; == 평균적용 == - 인덱스 미사용 == M이 첫번째일때 == - 인덱스 미사용 WHERE sex =‘M’; == F가 첫번째일때 == - 인덱스 사용 WHERE sex =‘F’; === === * **한가지 별도로 쿼리의 실행계획을 확인했을때와 실제로 수행된 실행계획은 다를 수 있다.**