질의변환의 목적은 옵티마이져가 보다 양호한 실행계획을 얻을 수 있도록 가능한 최대한 적절하게 SQL 형태를 변환하는 것
예1>
① sales_qty > 1200/12
② sales_qty > 100
③ sales_qty * 12 > 1200
①과 ②는 동일하게 취급.
③은 연산을 통하여 ②와 동일한 조건을 만들지 않는다. (소수점 등의 문제가 있음)
예2>
① job like 'SALESMAN'
② job = 'SALESMAN'
①과 ②는 동일하게 취급 단, 가변길이 타입만 가능하다.
예3>
① job IN ('CLERK', 'MANAGER')
② job = 'CLERK' OR job='MANAGER'
① sales_qty > ANY (:in_qty1, :in_qty2)
② sales_qty > :in_qty1 OR sales_qty > :in_qty2
① WHERE 1000000 > ANY (SELECT sal FROM emp WHERE job='CLERK')
② WHERE EXISTS (SELECT sal FROM emp WHERE job = 'CLERK' AND 1000000 > sal)
①을 ②와 같은 형식의 조건절로 확장시킨다.
예4>
① sales_qty > ALL (:in_qty1, :in_qty2)
② sales_qty > :in_qty1 AND sales_qty > :in_qty2
①을 ②와 같은 형식의 조건절로 확장시킨다.
① WHERE 100000 > ALL (SELECT sal FROM emp WHERE job='CLERK')
② WHERE NOT (1000000 <= ANY (SELECT sal FROM emp WHERE job = 'CLERK'))
③ WHERE NOT EXISTS (SELECT sal FROM emp WHERE job = 'CLERK' AND 1000000 <= sal)
①을 ②, ③과 같은 형식의 조건절로 확장시킨다.
예5>
① sales_qty BETWEEN 100 AND 200
② sales_qty >= 100 AND sales_qty <= 200
①을 ②와 같은 형식의 조건절로 확장시킨다.
① NOT (sal <30000 OR comm IS NULL)
② NOT sal <30000 AND comm IS NOT NULL
③ sal >= 30000 AND comm IS NOT NULL
①을 ②로 변환하고 이것을 다시 ③으로 변환한다.
예6>
① NOT deptno = (SELECT deptno FROM emp WHERE empno=7689)
② deptno <> (SELECT deptno FROM emp WHERE empno=7689)
반대 비교연산을 찾아 대체시키는 변환을 한다.
이행성 규칙 (Transitivity principie)
: 쿼리변환의 한 경우 (비용기준 옵티마이져에서만 가능)
1. A = B and B = C 이면 A = C
WHERE column1 comparision_operators constant AND column1 = column2
=> column2 comparison_operators constant
comparision_operators : =, !=, ^=, <, <>, >, <=, >=
constant : 연산, SQL 함수, 문자열, 바인드 변수, 상관관계 변수를 포함하는 상수 수식
SELECT *
FROM emp e, dept d
WEHRE e.deptno = 20
AND e.deptno = d.deptno;
=> d.deptno = 20 을 추론
만약 e.deptno 인덱스가 없다면 EMP 테이블을 전체 테이블 엑세스를 하면서 매번 dept 테이블 연결
이행성 규칙에 의해 실행계획이 DEPT 테이블을 먼저 인텍스로 엑세스하는 실행계획이 가능
* 비교값이 상수 수식이 아닌 컬럼이라면 이행성 규칙은 적용되지 않는다.
WHERE column1 comparision_operators column3 AND column1 = column2
=> (X) column2 comparison_operators column3
2. OR 조건들의 UNION ALL 분기
: UNION ALL로 각각의 인덱스를 경유하는 실행계획을 수립하고 이를 결합
변환기준
- OR를 사용한 조건절을 분기시켰을 때 각각이 인덱스 접근 경로로 이용할 수 있다면 변환을 수행
- 인덱스 사용불가로 전체 테이블을 스캔하거나, OR 조건이 검증기능만 수행하면 변환하지 않음.
- 즉, 분기된 각각의 엑세스가 불필요한 범위를 엑세스하지 않는다고 판단한 경우에만 적용
실행계획
- 'IN-LIST ITERATOR' (IN사용시)나 'CONCATENATION'(OR사용시)으로 실행계획이 나타남
- 강제적인 적용을 위해 'USE_CONCAT' 힌트를 사용
- 함부로 사용하면 엑세스가 불필요하게 분기되어 오히려 처리량이 크게 증가
* 각각이 독립적인 인덱스가 있을경우
SELECT * FROM emp WHERE job='CLERK' OR deptno = 10;
=>
SELECT * FROM emp WHERE deptno = 10
UNION ALL
SELECT * FROM emp WHERE job='CLERK' and deptno<> 10;
3. 서브쿼리를 가진 복잡한 문장을 조인 문장으로 변환
- 같은 기능의 조인 문장으로 변환하여 최적화 (서브쿼리가 '1' 집합이므로 조인을 해도 메인쿼리에 영향을 주지 않음)
* 서브쿼리의 연결고리가 '1'이 보장되어야 함.
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc='NEW YORK')
=> SELECT emp.* FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.loc = 'NEW YORK'
- 현재의 문장을 그대로 최적화하는 방법
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 20)
제공자 역할
- 서브쿼리를 먼저 수행하여 결과를 메인쿼리에 제공
- 서브쿼리 실행결과를 메인쿼리의 처리주관 인덱스에 제공 할 수 있을 떄 적용
확인자 역할가
- 메인쿼리가 실행되면서 서브쿼리를 확인하는 필터링 처리
- 각의 집합을 엑세스한 후 머지, 혹은 해쉬조인으로 수행
출처 : 새로쓴 대용량 데이터베이스 솔루션 vol.1
