🖥️ 들어가며
📌 쿼리 변환이란?
1. 휴리스틱 쿼리 변환
- 결과만 보장된다면 무조건 쿼리 변환을 수행합니다.
- 일종의 규칙기반 최적화 기법입니다.
- 경험적으로 항상 더 나은 성능을 보일 것이라는 옵티마이저 개발팀의 판단이 반영되어 있습니다.
2. 비용 기반 쿼리 변환
- 변환된 쿼리의 비용이 더 낮을 때만 활용합니다.
- 낮지 않을 경우, 원본 쿼리를 이용 최적화합니다.
3. 오라클 버전에 따른 쿼리 변환의 변화
- 8i: 복합 뷰(Complex View) Merging 을 사용자 힌트에 의해서만 수행
- 9i: 결과가 보장될 수 있는 형태라면 무조건 변환 시도
- 10g: CBO 모드로 동작하기 시작 (쿼리 변환의 결과가 더 나은 성능을 낼 것으로 판단될 때만 수행)
✏️ 1. 서브쿼리 Unnesting
- nest: 상자 등을 차곡차곡 포개 넣다, 중첩. ↔ unnest: 중첩된 상태를 풀어낸다.
- DBMS가 서브쿼리를 최적화하기 위해 내부적으로 Join 형태로 변경하는 것을 의미합니다.
- 서브쿼리 분류
- 1. 인라인 뷰
: from 절에 나타나는 서브쿼리 - 2. 중첩된 서브쿼리
: 결과집합을 한정하기 위해 where절에 사용된 서브쿼리 - 3. 스칼라 서브쿼리
: 한 레코드당 정확히 하나의 컬럼값만 리턴하는 것이 특징, 주로 select-list에서 사용되지만 몇 가지 예외사항을 제외하면 컬럼이 올 수 있는 대부분 위치에서 사용 가능
- 1. 인라인 뷰
- 중첩된 서브쿼리(Nested Subquery)
- 메인쿼리와 부모 자식이라는 종속적, 계층적 관계가 존재합니다.
- 따라서 논리적 관점에서 그 처리과정은 IN, Exists를 불문하고 필터 방식이어야 합니다.
- 즉, 메인쿼리에서 읽히는 레코드마다 서브쿼리 반복 수행 → 조건에 맞지 않는 데이터를 골라내는 것입니다.
- 하지만 서브쿼리를 처리할 때 필터 방식이 항상 최적의 수행속도를 보장하지 못합니다.
- 옵티마이저는 둘 중 하나를 선택
- 옵티마이저의 선택 1. 서브쿼리 Unnesting
: 동일한 결과를 보장하는 조인문을 변환하고 나서 최적화합니다. - 옵티마이저의 선택 2. 서브쿼리에 필터(Filter) 오퍼레이션
: 서브쿼리를 Unnesting 하지 않고 원래 상태에서 최적화합니다. 메인쿼리와 서브쿼리를 별도 서브플랜으로 구분해 각각 최적화 수행 → 이때 서브쿼리에 필터 오퍼레이션이 나타납니다.
- 옵티마이저의 선택 1. 서브쿼리 Unnesting
- 서브쿼리 Unnesting의 이점
- 서브쿼리를 메인쿼리와 같은 레벨로 풀어내면 → 다양한 액세스 경로, 조인 메소드를 평가할 수 있습니다.
- 조인 형태로 변환했을 경우, 더 나은 실행계획을 찾을 가능성이 증가합니다.
- 서브쿼리 Unnesting 관련 힌트
- unnest: 서브쿼리를 Unnesting → 조인방식으로 최적화하도록 유도
- no_unnest: 서브쿼리를 그대로 둔 상태 → 필터 방식으로 최적화하도록 유도
- 하지만 서브쿼리를 Unnesting한 결과가 항상 좋은 것은 아닙니다.
- 최근 옵티마이저는 서브쿼리 Unnesing 여부에 대해 Cost를 산정 후 결정하는 방향으로 발전하였습니다. (10g)
- 서브쿼리 기본 예시
- Unnesing 하지 않은 경우
- 옵티마이저가 서브쿼리를 별도의 서브플랜으로 최적화합니다.
- 이처럼 Unnesting 하지 않은 서브쿼리를 수행할 때는 메인쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반속 수행해야 합니다.
- Unnesting 한 경우
- 서브쿼리인데도 일반적인 Nested Loop Join 방식으로 수행되었습니다.
- 1차 쿼리변환: 서브쿼리 Unnesting
select *
from (select deptno from dept) a, emp b
where b.deptno = a.deptno; - 2차 쿼리변환: View Merging -- 본 쿼리문의 경우, View Merging까지 되었기에 실행계획에 View가 없음
select emp.*
from dept, emp
where emp.deptno = dept.deptno;
- Unnesing 하지 않은 경우
-- Unnesting 하지 않은 경우
select *
from emp
where deptno in (select /*+ no_unnest */ deptno from dept)
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 185 | 3 (0)| 00:00:01 |
|* 1 | ☆FILTER☆ | | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT"
"DEPT" WHERE "DEPTNO"=:B1))
3 - access("DEPTNO"=:B1)
-- Unnesting 한 경우
select *
from emp
where deptno in (select /*+ unnest */ deptno from dept)
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 350 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 99 | 1 (0)| 00:00:01 |
| 2 | ☆NESTED LOOPS☆ | | 10 | 350 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_PK | 4 | 8 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | EMP_DEPTNO_IDX | 3 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"="DEPTNO")
- 서브쿼리가 M쪽 집합이거나 Non-Unique 인덱스일 때
select *
from emp
where deptno in (select deptno from dept);- 위 예제의 특성
- 서브쿼리가 1쪽 집합
- dept 테이블에 deptno가 PK
- 즉, 결과집합이 emp 테이블 수준으로 출력됩니다.
- 🤖 옵티마이저의 고민
- 서브쿼리가 M쪽 집합
- dept 테이블에 PK 인덱스가 없어, 1쪽 집합임을 옵티마이저가 확신하지 못할 경우
- 이때는 서브쿼리 Unnesting 시도 시 결과집합이 서브쿼리(M쪽) 수준으로 출력됩니다.
- emp 테이블과 dept 테이블
- 1쪽 집합: 하나의 부서(dept)에 여러 직원(emp)이 소속될 수 있음. 즉, dept는 1쪽.
- M쪽 집합: 여러 직원(emp)이 포함된 테이블. 즉, emp는 M쪽.
select *
from dept
where deptno in (select deptno from emp);
=> 서브쿼리 Unnesting
select *
from (select deptno from emp) a, dept b
where a.deptno = b.deptno;| dept 테이블 | emp 테이블 | ||||
| deptno | deptname | empno | name | deptno | |
| 01 | 인사부 | 1 | 홍길동 | 01 | |
| 02 | 총무부 | 2 | 김길동 | 02 | |
| 03 | 고객관리부 | 3 | 홍길순 | 01 | |
| 09 | 승인부 | 4 | 이순신 | 03 | |
| 5 | 이태백 | 01 | |||
| 6 | 삼태백 | 04 | |||
- 1쪽 결과집합: emp 테이블의 데이터 중 부서번호가 일치하는 직원만 출력
| empno | name | deptno |
| 1 | 홍길동 | 01 |
| 2 | 김길동 | 02 |
| 3 | 홍길순 | 01 |
| 4 | 이순신 | 03 |
| 5 | 이태백 | 01 |
- M쪽 결과집합: dept와 emp를 Join한 결과는, emp 테이블을 기준으로 중복 데이터도 모두 포함
| deptno | deptname | empno | name |
| 01 | 인사부 | 1 | 홍길동 |
| 01 | 인사부 | 3 | 홍길순 |
| 01 | 인사부 | 5 | 이태백 |
| 02 | 총무부 | 2 | 김길동 |
| 03 | 고객관리부 | 4 | 이순신 |
- ✅ 옵티마이저의 선택
- 1쪽 집합임을 확신할 수 없는 서브쿼리쪽이 드라이빙 된다면
- 먼저 Sort Unique 오퍼레이션 수행 → 1쪽 집합으로 만든 뒤 → Join
- 즉, M쪽을 확실한 1쪽으로 만들어주기 위해 Sort Unique 오퍼레이션을 수행하는 것입니다.
- 메인쿼리쪽 테이블이 드라이빙 된다면
- 세미 조인 방식으로 조인 (조인이 성사되는 첫 번째 행을 찾으면, 더 이상 Inner 테이블을 탐색하지 않고, 다음 Outer 테이블의 행으로 넘어감)
- 1쪽 집합임을 확신할 수 없는 서브쿼리쪽이 드라이빙 된다면
- ⭐⭐⭐ Sort Unique 오퍼레이션 수행
- dept 테이블에서 PK 제약 제거, deptno 컬럼에 Non-Unique 인덱스 생성하였습니다.
- 쿼리 분석
- dept 테이블에서 deptno 값을 가져올 때 Sort Unique 중복 데이터를 제거 → 이후 emp 테이블과 조인하는 방식
- 인덱스를 사용해 dept에서 빠르게 deptno을 스캔하고 → 중복 제거 → emp 테이블과 Nested Loops 조인으로 연결됩니다.
- ⭐⭐⭐ 세미 조인 방식으로 수행
- 세미 조인: 기본적으로 NL 조인과 동일한 프로세스로 진행하지만, Outer 테이블의 한 로우가 Inner 테이블의 한 로우와 조인에 성공하는 순간 진행을 멈추고 Outer 테이블의 다음 로우를 계속 처리하는 방식
- 세미 조인 방식으로 변환의 장점: NL 세미조인, Hash 세미조인, Sort Merge 세미조인도 가능하다는 것입니다.
- 쿼리 분석
- emp 테이블에서 deptno가 dept 의 deptno와 일치하는 행만 반환합니다.
- 세미 조인: emp 테이블의 각 해에 대해 dept 탐색 → 첫 번째 매칭이 발견되면 멈추고 다음 emp 행으로 넘어갑니다.
-- Sort Unique 오퍼레이션 수행
alter table dept drop primary key;
create index dept_deptno_idx on dept(deptno);
SQL> explain plan for
2 select /*+ leading(dept@qb1) use_nl(emp) */ * from emp
3 where deptno in (select /*+ unnest qb_name(qb1) */ deptno from dept)
4 ;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 560 | 6 (17)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 560 | 6 (17)| 00:00:01 |
| 2 | ☆SORT UNIQUE☆ | | 4 | 12 | 1 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_DEPTNO_IDX | 4 | 12 | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 4 | 148 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("DEPTNO"="DEPTNO")
-- 세미 조인 방식 수행
SQL> explain plan for
2 select * from emp
3 where deptno in (select deptno from dept)
4 ;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 560 | 3 (0)| 00:00:01 |
| 1 |☆NESTED LOOPS SEMI☆| | 14 | 560 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEPT_DEPTNO_IDX | 4 | 12 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"="DEPTNO")
✏️ 2. Pushing 서브쿼리
- Unnesting되지 않은 서브쿼리는 항상 필터 방식으로 처리되고, 대개 마지막 단계에 처리됩니다.
- 서브쿼리를 먼저 처리하여 다음 수행단계로 넘어가는 Rows 수를 줄일 수 있다면 성능이 향상될 것입니다.
- Pushing 서브쿼리
- 실행계획상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 강제하는 것입니다.
- ⭐⭐⭐ 힌트: push_subq
- Unnesting 되지 않은 서브쿼리에만 작동 (no_unnest 힌트와 같이 기술하는 것이 올바름)
- no_push_subq, push_subq 예시
- no_push_subq
- 서브쿼리를 푸시하지 않고 수행합니다.
- 테이블 스캔: Table Access Full emp1, Table Access By Index RowID emp2
- 서브쿼리: Filter, Nested Loops 방식으로 처리
- 서브쿼리는 dept 테이블에서 deptno와 loc 조건을 확인하면서 exists 조건을 평가합니다.
- 성능: CPU 시간 0.06초 | 디스크 I/O: 37 | 쿼리 시간 14358
- push_subq
- 서브쿼리를 푸시하여 외부 테이블 emp1, emp2에서 필터링 전에 DEPT 테이블을 조회합니다.
- 서브쿼리: dept 테이블에 대해 미리 필터링 수행 → 메인 테이블 조회
- 서브쿼리: Filter, Nested Loops 방식으로 처리
- 성능: CPU 시간 0.01초 | 디스크 I/O: 0 | 쿼리 시간 3356
- no_push_subq
- ⭐⭐⭐ push_subq 정리
- 1. Join과 서브쿼리의 처리 순서
- 기존: 조인 후 서브쿼리 처리
- 변경: 서브쿼리를 먼저 처리한 후 조인 실행
- 2. 인덱스 활용
- 메인쿼리에서 제공하는 인덱스를 포함하여 처리
- 인덱스끼리 연산을 먼저 수행한 후 필요한 경우에만 테이블 Random Access 실행 (I/O 줄임)
- 3. 효과적인 사용 조건
- 서브쿼리의 필터링 효과와 캐싱 효과가 클 때 유용
- 메인쿼리(M):서브쿼리(1)의 비율일 때 효과적, 반대의 경우 효과 없음
- 1. Join과 서브쿼리의 처리 순서
-- emp 테이블을 1000번 복제한 emp1, emp2 테이블 생성
create table emp1 as
select * from emp, (select rownum no from dual connect by level <= 1000);
create table emp2 as select * from emp1;
alter table emp1 add constraint emp1_pk primary key(no, empno);
alter table emp2 add constraint emp2_pk primary key(no, empno);
-- no_push_subq
select /*+ leading(e1) use_nl(e2) */
sum(e1.sal)
, sum(e2.sal)
from emp1 e1
, emp2 e2
where e1.no = e2.no
and e1.empno = e2.empno
and exists (select /*+ NO_UNNEST NO_PUSH_SUBQ */ 'x'
from dept
where deptno = e1.deptno
and loc = 'NEW YORK')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.03 0 10 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.11 37 14348 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.14 37 14358 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=14348 pr=37 pw=37 time=0 us)
3000 FILTER (cr=14348 pr=37 pw=37 time=462 us)
14000 NESTED LOOPS (cr=14342 pr=35 pw=35 time=519 us)
14000 NESTED LOOPS (cr=342 pr=35 pw=35 time=286 us cost=12719 size=2002 card=22)
14000 TABLE ACCESS FULL EMP1 (cr=95 pr=0 pw=0 time=56 us cost=29 size=758472 card=14586)
14000 INDEX UNIQUE SCAN EMP2_PK (cr=247 pr=35 pw=35 time=0 us cost=0 size=0 card=1)(object id 91815)
14000 TABLE ACCESS BY INDEX ROWID EMP2 (cr=14000 pr=0 pw=0 time=0 us cost=1 size=39 card=1)
1 TABLE ACCESS BY INDEX ROWID DEPT (cr=6 pr=2 pw=2 time=0 us cost=2 size=11 card=1)
3 INDEX RANGE SCAN DEPT_DEPTNO_IDX (cr=3 pr=1 pw=1 time=0 us cost=1 size=0 card=1)(object id 91722)
-- push_subq
select /*+ leading(e1) use_nl(e2) */
sum(e1.sal)
, sum(e2.sal)
from emp1 e1
, emp2 e2
where e1.no = e2.no
and e1.empno = e2.empno
and exists (select /*+ NO_UNNEST PUSH_SUBQ */ 'x'
from dept
where deptno = e1.deptno
and loc = 'NEW YORK')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 8 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 3348 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.02 0 3356 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3348 pr=0 pw=0 time=0 us)
3000 NESTED LOOPS (cr=3348 pr=0 pw=0 time=157 us)
3000 NESTED LOOPS (cr=348 pr=0 pw=0 time=95 us cost=663 size=91 card=1)
3000 TABLE ACCESS FULL EMP1 (cr=101 pr=0 pw=0 time=37 us cost=29 size=37908 card=729)
1 TABLE ACCESS BY INDEX ROWID DEPT (cr=6 pr=0 pw=0 time=0 us cost=2 size=11 card=1)
3 INDEX RANGE SCAN DEPT_DEPTNO_IDX (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 91722)
3000 INDEX UNIQUE SCAN EMP2_PK (cr=247 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 91815)
3000 TABLE ACCESS BY INDEX ROWID EMP2 (cr=3000 pr=0 pw=0 time=0 us cost=1 size=39 card=1)
✏️ 3. 뷰 Merging
- 뷰 Merging이란
- 서브쿼리 Unnesting과 함께 가장 중요한 쿼리변환 기법 중 하나입니다.
- 말 그대로 View를 메인쿼리 안에 통합시킨다는 의미입니다.
- 쿼리문 1 vs. 2 비교
- 쿼리 1
- 사람이 읽긴 편하지만 옵티마이저에겐 좋지 않은 쿼리입니다.
- 옵티마이저는 가급적 위와 같은 쿼리 블록을 아래처럼 풀어내려는 습성이 있습니다.
- 쿼리 2
- 쿼리 1의 뷰 쿼리 블록은 액세스 쿼리 블록과 머지 과정을 거쳐 → 쿼리 2와 같은 형태로 변환됩니다. (뷰 머징)
- 이와 같이 쿼리가 풀어지면 옵티마이저는 더욱 다양한 액세스 경로를 탐색할 수 있습니다.
- 쿼리 1
-- 쿼리 1: 뷰
select *
from (select * from emp where job = 'SALESMAN') a
, (select * from dept where loc = 'CHICAGO') b
where a.deptno = b.deptno
-- 쿼리 2: 뷰 머징
select *
from emp a, dept b
where a.deptno = b.deptno
and a.job = 'SALESMAN'
and b.loc = 'CHICAGO'
- 단순 뷰(Simple View) Merging
- ⭐⭐⭐ 조건절과 조인문만을 포함하는 단순 뷰는 no_merge 힌트를 사용하지 않는 한, 언제든 Merge가 일어납니다.
- 단순 뷰는 Merging 해도 성능이 나빠지지 않습니다.
create or replace view emp_salesman
as
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from emp
where job = 'SALESMAN' ;
-- no_merge: 뷰 머징이 일어날 수 없음
select /*+ no_merge(e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
from emp_salesman e, dept d
where d.deptno = e.deptno
and e.sal >= 1500 ;
--------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | |
| 2 | NESTED LOOPS | | 1 |
| 3 | VIEW | EMP_SALESMAN | 1 | --- 뷰 그대로 사용
|* 4 | TABLE ACCESS FULL | EMP | 1 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
--------------------------------------------------------------
-- View Merging
select /*+ merge(e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
from emp_salesman e, dept d
where d.deptno = e.deptno
and e.sal >= 1500 ;
---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | |
| 2 | NESTED LOOPS | | 1 |
|* 3 | TABLE ACCESS FULL | EMP | 1 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
---------------------------------------------------------
- 복합 뷰(Complex View) Merging
- Group by절, Select 절에 Distinct 연산자를 포함합니다.
- 복합 뷰는 \_complex_view_merging 파라미터를 true로 설정할 때만 Merging이 일어납니다.
- ⭐⭐⭐ 이러한 복합 뷰를 포함하는 뷰 Merging은 성능이 나빠질 수 있습니다.
- Group by 절
- Select 절에 Distinct 연산자 포함
- 쿼리 수행 비용 조사 후 적용여부를 판단하는 방향으로 옵티마이저가 발전하고 있습니다.
(9i에서는 뷰를 무조건 머징, 10g부터 비용기반 쿼리 변환 방식으로 전환)
select d.dname, avg_sal_dept
from dept d
, (select deptno, avg(sal) avg_sal_dept
from emp
group by deptno) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
-----------------------------
| Id | Operation |
-----------------------------
| 0 | SELECT STATEMENT |
| 1 | HASH GROUP BY |
|* 2 | HASH JOIN | --- 뷰를 해체하여 일반 조인으로 변경
|* 3 | TABLE ACCESS FULL|
| 4 | TABLE ACCESS FULL|
-----------------------------
-- no_merge
select d.dname, avg_sal_dept
from dept d
, (select /*+no_merge*/deptno, avg(sal) avg_sal_dept
from emp
group by deptno) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
-------------------------------
| Id | Operation |
-------------------------------
| 0 | SELECT STATEMENT |
|* 1 | HASH JOIN |
| 2 | JOIN FILTER CREATE |
|* 3 | TABLE ACCESS FULL |
| 4 | VIEW | --- 뷰 그대로 사용
| 5 | HASH GROUP BY |
| 6 | JOIN FILTER USE |
|* 7 | TABLE ACCESS FULL|
-------------------------------
- ⭐⭐⭐ 집CR 함수는 불가, Group By는 가능
- 뷰 Merging이 불가능한 경우
- 1. 집합(Set) 연산자: UNION, UNION ALL, INTERSECT, MINUS
- 2. Connect By 절
- 3. ROWNUM Pseudo 컬럼
- 4. Select-list에 집계 함수(AVG, COUNT, MAX, MIN, SUM) 사용
- 5. 분석 함수
📒 정리하면
- 서브쿼리 Unnesting: 중첩 서브쿼리를 조인 형태로 변환, 다양한 액세스 경로 탐색이 가능합니다.
(힌트: unnest / no_unnest) - Unnesting 미실행 시, 메인쿼리 레코드마다 서브쿼리를 반복 수행합니다.
- 옵티마이저의 선택
- 1쪽 집합임을 확인할 수 없는 서브쿼리 드라이빙: Sort Unique 오퍼레이션 수행
(Sort Unique: M쪽 집합을 1쪽 변환 시 사용, 중복 제거 후 조인) - 메인쿼리쪽 드라이빙: 세미 조인 방식으로 조인합니다.
(세미 조인: 첫 매칭 시 다음 컬럼으로 넘어감, NL/Hash/Sort Merge 세미조인 방식 적용 가능)
- 1쪽 집합임을 확인할 수 없는 서브쿼리 드라이빙: Sort Unique 오퍼레이션 수행
- Pushing 서브쿼리: push_subq 힌트. 서브쿼리 필터링을 조인 전 수행, I/O 감소 효과가 있습니다.
- 적용 요건: 메인쿼리(M):서브쿼리(1) 비율 시 효과적, 반대의 경우 비효율적입니다.
- 인덱스 활용: 메인쿼리 인덱스 기반으로 연산을 먼저 처리하고, 테이블 Random Access를 최소화합니다.
- 뷰 Merging: 단순 뷰는 기본 머지, 복합 뷰는 _complex_view_merging 파라미터로 제어합니다. (+ 복합 뷰는 CBO 판단 필요)
- 뷰 Merging이 불가한 경우: 집CR 함수는 불가, Group By는 가능
- 집: 집합 연산자, Union, Union All, Intersect, Minus
- C: Connect By 절
- R: Rownum Pseudo 컬럼
- 함수: Select-list에 집계 함수, 분석 함수
반응형
