문서의 이전 판입니다!
옵티마이져는 가능한 모든 수식의 값을 미리 구한다
<color red>보다 양호한 실행계획을 얻을 수 있도록 가능한 최대로 적절하게 SQL 형태를 변환하는 것이 중요하다</color>
이행성 규칙(Transitivity principle)
조건절에 같은 컬럼을 사용한 두 개의 조건식이 있다면 옵티마이져는 새로운 조건식을 이행성규칙에 따라 생성하고 이 조건식을 이용하여 최적화를 수행
<color blue>원래의 조건식에서는 인덱스를 사용하지 못하게 되었더라도 인덱스를 사용하는 실행계획이 나타날 수 있다</color>
조건식 형식
WHERE column1 comparison_operators constant
AND column1 = column2
comparison_operators : =, !=, ^=, <, <>, >, ⇐, >= 중 하나
constant : 연산, SQL 함수, 문자열, 바인드 변수, 상관관계 변수를 포함하는 상수 수식
[예1] 이행성 규칙
<color blue>비용기준 업티마이져를 사용할 때만 적용</color>
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’) ;
같은 기능의 조인 문장으로 변환하여 다시 이를 최적화
서브쿼리 <color blue>SELECT deptno FROM dept WHERE loc = ‘NEW YORK’</color>는 집합이므로 조인을 해도 메인쿼리에 영향을 주지 않음.
SELECT emp.*
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND dept.loc = ‘NEW YORK’ ;
현재 문장을 그대로 최적화
제공자역할
서브쿼리를 먼저 수행하여 결과를 메인쿼리에 제공
서브쿼리 실행결과를 메인쿼리의 처리주관 인덱스에 제공할 수 있을 때 적용
확인자 역할
메인쿼리가 실행되면서 서브쿼리를 확인하는 필터형 처리
각의 집합을 액세스한 후 Merge하거나 혹은 Hash Join으로 수행
뷰병합(View Merging)
사용자 정의 바인드 변수의 엿보기(Peeking)
바인드 변수를 사용한 쿼리는 먼저 파싱과 최적화가 이루어진 후에 바인드 변수에 바인딩이 이루어진다
최적화가 이루어지는 시점에는 변수로 제공되는 컬럼은 바인딩 값에 대한 통계정보를 사용할 수 없다는 것을 의미
분포도가 균일하지 못한 컬럼에 바인드 변수를 사용하면 최악의 실행계획이 생성될 수도 있다
Peeking
바인드 변수를 사용한 쿼리가 처음 실행될 때
옵티마이져는 사용자가 지정한 바인드 변수의 값을 살짝 커닝함으로써
조건절의 컬럼값이 상수값으로 제공될 때와 마찬가지로 선택도를 확인하여 최적화를 수행
첫번째 파싱 : 공유 SQL 영역에 처음 등록될 때
Peeking의 비효율성
성별 컬럼에 M 이 99% 이고, F가 1% 들어있는 경우
SELECT name FROM patients WHERE sex = :input_sex_value ;
평균적용
M이 첫번째일때
F가 첫번째일때
* <color red>한가지 별도로 쿼리의 실행계획을 확인했을때와 실제로 수행된 실행계획은 다를 수 있다.</color>