View Source

h1. 3.1.4. 질의의 변환(Query Transforming)

* 질의 변환 Case
# 상수 변환
옵티마이져는 가능한 모든 수식의 값을 미리 구함
상수의 연산을 실행될 때마다 이루어지는 것이 아니라 질의 변환단계에서 한 번만 수행
{code:SQL}
예)
① sales_qty > 1200/12
② sales_qty > 100
③ sales_qty*12 > 1200

①,② 는 동일하게 취급 - 사전에 최대한의 연산을 하여 두 번째 조건식이 됨
③은 연산을 하여 ② 조건식을 만들지 않음 - 비교연산자의 추가적 연산은 연산자를 좌우로 이동시켜 단순화를 하지는 않기 때문
{code}
\\
# Like 단순화
LIKE를 사용한 조건절에서 '%' 나 '_'를 사용하지 않는 경우에는 '=' 연산으로 조건절 단순화
가변길이 데이터타입일 때만 이러한 수식의 단순화가 가능
\\
\\
# 조건식에 IN, OR을 이용한 Case
IN 비교 연산자를 사용한 문장에서는 OR 논리 연산자를 이용해 여러개의 '='로 된 같은기능의 조건절로 확장
{code:SQL}
예)
① job IN ('CLERK','MANAGER')
② job = 'CLERK' OR job = 'MANAGER'
{code}
\\
# 조건식에 ANY, SOME 을 이용한 Case
'=' 비교연산자와 OR논리 연산자를 이용해 같은 기능의 조건절로 확장
{code:SQL}
예)
① sales_qty > ANY (:in_qty1, :in_qty2)
② sales_qty > :in_qty1 OR sales_qty > :in_qty2
{code}
\\
# ANY, SOME 뒤에 서브쿼리를 사용한 Case
EXISTS 연산자를 이용한 서브쿼리로 변환
{code:SQL}
예)
① where 100000 > ANY (select sal from emp where job = 'CLERK')
② where EXISTS (select sal from emp where job = 'CLERK' and 100000 > sal)
{code}
\\
# 조건식에 ALL 을 이용한 Case
조건절은 '=' 연산자와 AND 논리 연산자를 사용해 같은 기능의 조건절로 변환
{code:SQL}
예)
① sales_qty > ALL (:in_qty1, :in_qty2)
② sales_qty > :in_qty1 AND sales_qty > :in_qty2
{code}
\\
# ALL 뒤에 서브쿼리를 사용한 Case
NOT ANY로 변환한 후에 다시 EXISTS 연산자를 이용하여 서브쿼리로 변환
{code:SQL}
예)
① WHERE 100000 > ALL (SELECT sal FROM emp WHERE job = 'CLERK')
② WHERE NOT (100000 <= ALL (SELECT sal FROM emp WHERE job = 'CLERK'))
③ WHERE NOT EXISTS (SELECT sal FROM emp WHERE job = 'CLERK' AND 100000 <= sal)
{code}
\\
# 조건식에 BETWEEN 를 이용한 Case
'>=', '<=' 비교 연산자를 사용하여 변환
{code:SQL}
예)
① sales_qty BETWEEN 100 and 200
② sales_qty >= 100 AND sales_qty <= 200
{code}
\\
# 조건식에 NOT를 이용한 Case
논리 연산자를 제거할 수 있는 반대 비교연산을 찾아 대체시키는 변환
{code:SQL}
예)
① NOT (sal < 30000 OR comm IS NULL)
② NOT sal < 30000 AND comm IS NOT NULL)
③ sal >= 30000 AND comm IS NOT NULL

① → ② → ③ 순서로 변환
{code}
\\
# 서브쿼리에 사용된 NOT을 변환하는 Case
NOT를 없애기 위해 반대 비교연산을 찾아 대체시키는 변환
{code:SQL}
예)
① NOT deptno = ( SELECT deptno FROM emp WHERE empno = 7689)
② deptno <> (SELECT deptno FROM emp WHERE empno = 7689)
{code}
\\

h2. 3.1.4.1 이행성규칙(Transitivity principle)

* 조건절에 같은 컬럼을 사용한 두 개의 조건식이 있다면 옵티마이져는 새로운 조건식을 이행성 규칙에 따라 생성하고 이 조건식을 이용하여 최적화를 수행
| WHERE column1 comparison_operators constant
AND column1 = column2|
** comparison_operators : =, !=, ^=, <, <>, >, <=, >= 중의 하나
** constant : 연산, SQL함수, 문자열, 바인드 변수, 상관관계 변수를 포함하는 상수 수식
\\
\\
** *추론 결과 : COLUMN2 comparison_operators constant*
\\
* 비용기준 옵티마이져일때만 적용됨
* *대상이 상수 수식이 아닌 컬럼인 경우는 이행이 일어나지 않는다.*

\\
* WHERE 절에 OR로 연결된 두개의 조건절이 있을 때 발생할 수 있는 경우
-- OR를 사용한 조건절을 분기시켰을 때 각각이 인덱스 접근 경로로 이용할 수 있다면 변환
: UNION ALL에 의해 각각의 인덱스를 경유하는 실행계획을 선택하고 나중에 결합
: 실행계획에서는 'IN-LIST ITERATOR' 나 'CONCATENATION'이라는 표시가 나타남
{code:SQL}
예)
select * from emp where job = 'CLERK' OR deptno = 10 ;
select * from emp where job = 'CLERK'
UNION ALL
SELECT * from emp where deptno = 10 and job <> 'CLERK';
{code}
-- 조건절에 인덱스를 사용할수 없고 전체 테이블을 스캔한다고하면 OR를 사용한 조건절은 그 문장에 대한 변환을 하지 않음
||이행성규칙은 효율적이라고 판된될 때만 변환이 일어난다.||

\\
h2. 3.1.4.2 뷰병합(View Merging)

* 뷰를 사용하는 쿼리
# 뷰쿼리 : 우리가 뷰를 생성할 때 사용한 SELECT 문
# 액세스 쿼리 : 뷰를 수행하는 SQL

* 뷰쿼리와 엑세스쿼리를 병합하는 방법
# 뷰병합(View Merging)법: 뷰쿼리를 액세스쿼리에 병합해 넣는방식
# 조건절 진입(Pushing predicate)법 : 뷰병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 액세스쿼리의 조건절 진입시키는방식
** 조건절 진입의 경우(뷰병합이 불가능한 경우)
■ 집합연산(UNION, UNION ALL, INTERSECT, MINUS)
■ CONNECT BY
■ ROWNUM 을 사용한경우
■ SELECT-List의 그룹함수(AVG,COUNT,MAX,MIN,SUM)
■ GROUP BY(단, Merge 힌트를 사용했거나 관련 파라메터가 Enable이면 뷰병합 가능)
■ SELECT-List 의 DISTINCT (단, Merge 힌트를 사용했거나 관련 파라메터가 Enable이면 뷰병합 가능)

* 뷰쿼리를 액세스 쿼리로 병합시키기
-- 액세스 쿼리에 있는 뷰의 이름을 뷰쿼리의 원래의 테이블로 이름을 바꾸고, 뷰의 WHERE절에 있는 조건절을 액세스 쿼리 WHERE절에 추가
{code:SQL}
예)
CREATE VIEW emp_10 (e_no, e_name, job, manager, hire_date, salary, commission, deptno) AS
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE deptno= 10;

- 액세스 쿼리
SELECT em_no, e_name, salary, hire_date
FROM emp_10
WHERE salary >10000;

- SQL 병합된 쿼리
SELECT empno, ename, sal, hiredate
FROM emp
WHERE deptnl = 10
AND sal > 10000
{code}

* 조건절 진입의 경우
# 집합연산의 경우
{code:SQL}
예)
CREATE VIEW emp_union_view(e_no,e_name, job, mgr, hiredate,sal, comm,deptno) AS
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM regular_emp
UNION ALL
SELECT empno, ename, job, magager, hiredate, salary, comm, 90 FROM temporary_emp;

- 액세스 쿼리
SELECT e_no, e_name, mgr,sal
FROM emp_union_view
WHERE deptnl = 20;

- 변환된 쿼리
SELECT empno, ename, mgr, sal
FROM (SELECT empno, ename, ggr, sal FROM regular_emp WHERE deptno = 20
UNION ALL
SELECT empno, ename, magager, salary FROM temporary_emp WHERE 90 = 20);
{code}
** 액세스쿼리에 있는 조건들이 각 select 문의 조건절속으로 파고 들어갔음을 알 수 있음
\\
# GROUP BY 를 사용한 뷰
{code:SQL}
예)
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 *
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;
{code}
\\
# 복잡한 뷰 내부에 GROUP BY나 DISTINCT 를 사용한 경우
|| 뷰병합과 관련된 파라메터(complex_view_merging, optimizer_secure_view_merging)가 작동상태되어 있으면 복잡한 뷰 내부에 GROUP BY 나 DISTINCT 를 사용했다라도 액세스쿼리의 조건들이 뷰쿼리에 파고들어 갈 수 있다. ||
{code:SQL}
예)
- 액세스 쿼리
SELECT emp.ename, emp.sal
FROM emp, dept
WHERE (emp.deptno, emp.sal) IN (SELECT deptno, avg_sal FROM emp_group_by_deptno)
AND emp.deptno = dept.deptno
AND dept.oc = 'London' ;

- 변환된 쿼리
SELECT e1.ename, e1.sal
FROM emp e1, dept d, emp e2
WHERE e1.deptno = d.deptno
AND d.loc = 'London'
AND e1.deptno = e2.deptno
GROUP BY e1.rowid, d.rowid, e1.ename, e1.dalary
HAVING e1.sal = AVG(e2.sal) ;
{code}


h3. 3.1.4.3 사용자 정의 바인드 변수의 엿보기(Peeking)

- 바인드 변수를 사용한 커리가 처음 실행될 때 옵티마이져는 사용자가 지정한 바인드 변의 값을 '살짝 커닝'함으로써 조건절의 컬럼값이 상수값으로 제공될때와 마찬가지로 선택도를 확인하여 최적화를 수행하도록 한다.
- 최초에 실질적인 파싱이 일어날때만 단 한번 변수의 값을 PEEKING 함
- PEEKING의 적용 여부는 _OPTIM_PEEK_USER_BINDS 파라메터로 결정
\\
- 현실적으로 최초의 단 한 가지 경우의 선택도를 사용하는 것이나 전체를 평균적인 분포로 보는 것이나 논리적으로 오류일 확률은 다르지 않음
: 11g에서는 Adaptive Cursor Sharing 기능으로 이러한 문제를 많이 해결함


\\
h1. 3.1.5 개발자의 역할

* 집합적 사고로의 전환
과거 절차형 프로그래밍의 개념에서 SQL의 근간이 되는 집합적, 비절차형 처리에 적응해야 함

\\
* 수행 결과 뿐만 아니라 성능을 고려한 SQL을 활용
SQL에 대한 활용도가 높아지면서 점차 모든 처리를 SQL로 해결하려는 개발자들이 생겨남
하지만 그 과정에서 요구되는 컬럼의 값이 늘어갈 때마다 조인으로 붙이고 집합의 결과 단위가 달라지면 Group by. 추가 정보가 필요하면 Union all 로 붙이고.. 무한 반복.
옵티마이저에 무지한 상태에서 어설픈 방법으로 복잡한 SQL을 작성하면 안됨

\\
|| 자신이 작성한 SQL이 개략적으로라도 어떤 형식으로 수행하며, 어느정도 처리량으로 수행될 것인지 파악할 수 있는 힘이 바탕이 되어야 함 ||