[Oracle] SQL튜닝 - 7: SUBQUERY TUNNING

[서브쿼리 3가지 유형]

 

 1) 일반 서브쿼리 (Nested Subquery)

    - WHERE 절이나 HAVING 절에 위치

 2) 인라인 뷰 (Inline View)

    - FROM 절 내부에 위치하여 하나의 가상 테이블처럼 동작

 3) 스칼라 서브쿼리 (Scalar Subquery)

    - SELECT 절의 컬럼 위치에 사용되며, 메인쿼리의 한 컬럼처럼 동작

---

 

[서브쿼리 Unnesting]

 

 -  둘 이상의 행을 출력하는 Subquery는 Optimizer가 실행계획을 생성할 때 Query Transforemr를 통해 Join Operation으로 최적화될 수 있음

 -  서브쿼리를 메인쿼리와 같은 레벨로 풀어내면 다양한 액세스 경로와 조인 방법을 사용하여 더 나은 실행계획을 찾을 수 있음

 -  12c 부터는 SELECT 절의 서브쿼리도 unnest로 실행 될 수 있음 

     ○ /*+ unnest */ : 서브쿼리를 Unnesting 함으로써 조인 방식으로 최적화하도록 유도하는 힌트

                ① semi join : 서브쿼리가 IN이나 EXISTS 형태일 때, 메인쿼리와 조인하되 중복을 제거하여 매칭 여부만 확인하는 조인 방식 (플랜에 SEMI 표기)

                ② anti join : 서브쿼리가 NOT IN이나 NOT EXISTS 형태일 때, 일치하지 않는 집합만 골라내는 방식 (플랜에 ANTI 표기)

     ○  /*+ no_unnest */ : 서브쿼리 사용시 성능이 더 좋을 경우, 원래 작성된 서브쿼리 형태 그대로 유지시켜 필터 방식으로 최적화하도록 유도하는 힌트

---

 

[View Merging]

 

 -   /*+ merge */ : 인라인 뷰(FROM 절 서브쿼리)를 메인쿼리와 하나의 쿼리 블록으로 합쳐져 최적화하는 힌트

 -   /*+ no_merge */ : 인라인 뷰 구조를 독립적으로 유지시키는 힌트 (실행 계획 창에 VIEW 오퍼레이션이 직접 명시됨)

-- 인라인 뷰 예시
select * 
from (select * from emp  where job = 'SALESMAN') a  
   , (select * from dept where loc = 'CHICAGO' ) b
where a.deptno = b.deptno
;

-- 예시 뷰 Merging으로 변환
select * 
from emp a, dept b 
where a.deptno = b.deptno 
and a.job = 'SALESMAN' 
and b.loc = 'CHICAGO'
;

 

 

[WITH절 제어]

 

 - 오라클은 문장 내에서 WITH절 서브쿼리가 1번 사용되면 Inline View 형태로 변환되어 계획이 수립되고,

    2번 이상 사용되면 TEMP TABLE 형태로 생성됨

 - 아래 힌트를 통해 위 원칙 변경이 가능함

     ○ /*+ inline */ : WITH 절 집합을 메인쿼리에 가상 인라인 뷰 형태로 풀어헤쳐서 처리 (최대한 메모리에서 수행되어 효율적임)

     ○ /*+ materialize */ : WITH 절 집합을 임시 테이블(Temporary Table) 형태로 메모리/디스크 임시 세그먼트에 물리적으로 적재 후 재사용 (Physical I/O가 발생할 수 있는 장단점이 존재)

---

 

[쿼리 블록 명명]

 

- Subquery 사용이 많아지고 Query Transformation으로 문장이 변화되면 사용자가 원하는 힌트가 제대로 적용되지 않는 경우가 발생함

 - 10g 버전부터 QB_NAME 힌트를 이용하여 Query Block의 식별 및 제어 가능

 - /*+ qb_name(alias_name) */