🖥️ 들어가며
📌 쿼리 변환 종합
- 서브쿼리 Unnesting (+ push_subq)
- 뷰 Merging (불가한 경우: 집CR 함수는 불가, Group By는 가능)
- 조건절 Pushing (조인절 Pushdown, 조인절 Pullup, 조인조건 Pushdown)
- 조건절 이행
- 불필요한 조인 제거
- OR 조건을 Union으로 변환 (OR-Expansion)
- 기타
✏️ 1. 조건절 Pushing
- 옵티마이저의 뷰 처리
- 1차적으로 뷰 Merging을 수행하지만
- 뷰 Merging 실패 시 옵티마이저는 조건절(Predicate) Pushing을 시도합니다.
- 조건절 Pushing
- 뷰를 참조하는 메인쿼리 블록의 조건절을 → 뷰 쿼리 블록 안으로 Pushing하는 기능입니다.
- 조건절 Pushing 종류
- 1. 조건절 Pushdown: 쿼리 블록 밖에 있는 조건을 → 쿼리 블록 안쪽으로 밀어넣는 것입니다.
- 2. 조건절 Pullup: 쿼리 블록 안에 있는 조건을 → 쿼리 블록 밖으로 꺼내오는 것입니다.
(다리 다른 쿼리블록 안으로 Pushdown 하는 데 활용 가능) - 3. 조인 조건 Pushdown: NL 조인 수행 중 Outer의 조건을 Inner쪽 뷰 쿼리 안으로 밀어 넣는 것입니다.
- 조건절 Pushing 불가 조건
- 뷰 안에 ROWNUM, 분석함수가 있을 경우 Push가 불가합니다.
- 관련 힌트와 파라미터
- select /*+ opt_param('_optimizer_push_pred_cost_based', 'false') */ * from ...
- alter system set "_optimizer_push_pred_cost_based" = false;
✏️ 1-1. 조건절(Predicate) Pushdown
- Group By 절을 포함한 뷰 - 조건절 Pushdown (1)
- NOSORT, TABLE ACCESS BY INDEX ROWID
: 뷰 Merging에 실패했지만, 옵티마이저가 조건절을 뷰 안쪽으로 밀어넣어 인덱스가 스캔한 것을 알 수 있습니다. - access("DEPTNO"=30)
: 만약 조건절 Pushdown이 동작하지 않았다면 필터링 조건으로 dept = 30이 실행되었을 것입니다.
- NOSORT, TABLE ACCESS BY INDEX ROWID
select deptno, avg_sal
from (select /*+no_merge */ deptno, avg(sal) avg_sal from emp group by deptno) a
where deptno = 30
--------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | VIEW | | 1 | --- View
| 2 | SORT GROUP BY NOSORT | | 1 | --- Nosort
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 6 |
|* 4 | INDEX RANGE SCAN | EMP_DEEPTNO | 6 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"=30) --- Filter가 아닌 Access
- 조건절 Pushdown (2)
- b.deptno = 30 조건이 인라인 뷰 a로 푸쉬 → a의 결과를 필터링하는 데 사용되었음을 보여줍니다.
select /*+ no_merge(a)*/
b.deptno, b.dname, a.avg_sal
from (select deptno, avg(sal) avg_sal from emp group by deptno) a
, dept b
where a.deptno = b.deptno
and b.deptno = 30
------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED LOOPS | | 1 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | | | |
|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | | |
| 4 | VIEW | | 1 | | | |
| 5 | SORT GROUP BY | | 1 | 2048 | 2048 | 2048 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 118 | | | |
|* 7 | INDEX RANGE SCAN | EMP_DEEPTNO | 118 | | | |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."DEPTNO"=30)
7 - access("DEPTNO"=30)
✏️ 1-2. 조건절(Predicate) Pullup
- 쿼리 블록 안에 있는 조건을 → 쿼리 블록 밖으로 꺼내오는 것입니다.
- 조건절 Pullup (1)
- e1의 deptno = 1의 조건이 e2의 액세스 조건으로 사용되었습니다.
- 최적화된 쿼리: 조건절 Pullup 적용
- 5, 9: 두 개의 인라인뷰(e1, e2)가 모두 deptno = 10 조건을 포함하고 있습니다.
- 비효율적인 쿼리: Pullup 비활성화
- _pred_move_around 파라미터를 flase로 설정하여 조건절 Pullup 비할성화 했습니다.
- 그 결과, e2 뷰에서 deptno = 10 조건이 외부로 끌어올려지지 않았습니다.
- 7-10: deptno = 10 조건이 적용되지 않아 모든 부서의 데이터를 처리합니다.
- filter(COUNT(*) >0): 불필요한 추가 필터링 단계입니다.
-- 최적화된 쿼리: 조건절 Pullup 적용
select * from
(select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1
,(select deptno, min(sal), max(sal) from emp group by deptno) e2
where e1.deptno = e2.deptno ;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN | | 1 | 963K| 963K| 405K (0)|
| 2 | VIEW | | 1 | | | |
| 3 | HASH GROUP BY | | 1 | 899K| 899K| 466K (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 98 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 | | | |
| 6 | VIEW | | 1 | | | |
| 7 | HASH GROUP BY | | 1 | 838K| 838K| 465K (0)|
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 98 | | | |
|* 9 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 | | | |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."DEPTNO"="E2"."DEPTNO")
5 - access("DEPTNO"=10)
9 - access("DEPTNO"=10)
-- 비효율적인 쿼리: Pullup 비활성화
select /*+ opt_param('_pred_move_around', 'false') */ * from --- 파라미터 false 설정
(select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1
,(select deptno, min(sal), max(sal) avg_sal from emp group by deptno) e2
where e1.deptno = e2.deptno;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED LOOPS | | 1 | | | |
| 2 | VIEW | | 1 | | | |
| 3 | HASH GROUP BY | | 1 | 899K| 899K| 728K (0)|
| 4 | TABLE ACCESS BY INDEX ROWID | EMP | 98 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 | | | |
| 6 | VIEW PUSHED PREDICATE | | 1 | | | |
|* 7 | FILTER | | | | | |
| 8 | SORT AGGREGATE | | 1 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| EMP | 98 | | | |
|* 10 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 | | | |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPTNO"=10)
7 - filter(COUNT(*)>0)
10 - access("DEPTNO"="E1"."DEPTNO")
✏️ 1-3. 조인조건(Join Predicate) Pushdown
- NL 조인 수행 중 Outer의 조건을 Inner쪽 뷰 쿼리 안으로 밀어 넣는 것입니다.
- 조인조건절 Pusdown 제어 힌트
- push_pred: 조인조건 Pushdown을 유도합니다.
- no_push_pred: 조인조건 Pushdown을 방지합니다.
- 제어 파라미터
- _push_join_predicate: 뷰 Merging에 실패한 뷰 안쪽으로, 조인조건을 Pushdown하는 기능을 활성화합니다.
- _push_join_union_view: Union all을 포함하는 Non-mergeable view 안쪽으로, 조인조건을 Pushdown하는 기능을 활성화합니다.
- _push_join_union_view2: Union을 포함하는 Non-mergeable view 안쪽으로, 조인조건을 Pushdown하는 기능을 활성화합니다.
- 조인조건 Pushdown (1)
- push_pred(e) 힌트: 조인조건을 인라인 뷰 내부로 밀어넣도록 옵티마이저에게 지시합니다.
- VIEW PUSHED PREDICATE: 조인조건이 뷰 내부로 푸쉬되었음을 안내합니다.
- access("DEPTNO" = "D"."DEPTNO"): emp 내부 인덱스 스캔 + 인라인 뷰 내부에서 조인조건이 적용되었음을 보여줍니다.
select /*+ no_merge(e) push_pred(e) */ *
from dept d, (select empno, ename, deptno from emp) e
where e.deptno(+) = d.deptno
and d.loc = 'CHICAGO';
-----------------------------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS OUTER | | 98 |
|* 2 | TABLE ACCESS FULL | DEPT | 1 |
| 3 | VIEW PUSHED PREDICATE | | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 98 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("D"."LOC"='CHICAGO')
5 - access("DEPTNO"="D"."DEPTNO")- 조인조건 Pushdown (2)
- push_pred(e) 힌트: 옵티마이저에게 조인조건을 인라인 뷰 내부로 밀어넣도록 지시합니다.
- 조인조건: e.deptno = d.deptno → 이 조건이 인라인 뷰 내부로 pushdown → emp 테이블에서 필요한 데이터만 추출합니다.
- 이로 인해 그룹화, 집계 연산을 수행하기 전 데이터 양을 줄일 수 있습니다.
- 결과적으로 처리해야 할 데이터 양이 감소해 쿼리 성능이 향상됩니다.
- 실행계획: ix_emp_deptno_sal 인덱스에 대한 Range Scan이 발생합니다.
이는 조인조건이 뷰 내부로 Pushdown 되어 인덱스를 효과적으로 사용할 수 있게 되었음을 나타냅니다.
select /*+ leading(d) use_nl(e) push_pred(e) */ *
from (select /*+ no_merge */ deptno, sum(sal) sal
from emp
-- where deptno = d.deptno 이것과 동일한 효과 (풀면 에러남)
group by deptno
) e, dept d
where d.loc = 'New York'
and e.deptno = d.deptno;
-- Id: 6 | Operation: INDEX RANGE SCAN | Name: IX_EMP_DEPTNO_SAL
-- Rows: 4| Bytes: 28 | Cost (%CPU): 1 (0) | Time: 00:00:01- 조인조건 Pushdown과 LATERAL IN-LINE VIEW
- LATERAL: Oracle 12c에서 도입된 기능, 상관 서브쿼리를 from절에서 인라인 뷰처럼 사용할 수 있게 해 주는 키워드입니다.
- dept 테이블과 emp 테이블을 조인할 때 LATERAL 키워드를 사용합니다.
- LATERAL 내부 서브쿼리에서 where deptno = d.deptno과 같이 외부테이블(dept) 컬럼을 직접 참조합니다.
- LATERAL: Oracle 12c에서 도입된 기능, 상관 서브쿼리를 from절에서 인라인 뷰처럼 사용할 수 있게 해 주는 키워드입니다.
select *
from dept d,
lateral (select /*+ no_merge */ deptno, sum(sal) sal
from emp
where deptno = d.deptno
group by deptno
) e
where d.loc = 'New York';
✏️ 2. 조건절 이행
- 연역법 추리를 통해 새로운 조건절을 내부적으로 생성해 주는 쿼리입니다.
- 연역법 추리: A=B이고, B=C이면, A=C이다
- 조건절 이행 (1)
- access("D"."DEPTNO"=10): 조건에 없던 d.deptno 값이 추가된 이유는 다음과 같습니다.
1) e.deptno = 10이고 → 2) d.deptno = e.deptno 이면 → 3) d.deptno = 10 라는 결론
- access("D"."DEPTNO"=10): 조건에 없던 d.deptno 값이 추가된 이유는 다음과 같습니다.
select * from dept d, emp e
where e.job='MANAGER'
and e.deptno=10
and d.deptno=e.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 35793385
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 57 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_IDX | 2 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"=10) --- 조건에 없던 d.deptno 값 추가
5 - access("E"."DEPTNO"=10 AND "E"."JOB"='MANAGER')
✏️ 3. 불필요한 조인 제거
- 1:M 관계인 두 테이블을 조인하는 쿼리문에서. 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는다면?
- 옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환하는 기능입니다. (10g부터 작동)
- 조인 제거를 위한 조건
- PK 존재: 조인을 하려는 두 테이블간 조인 카디널리티를 파악하기 위해 필수입니다.
- FK 존재: Null을 제외한 레코드 중 조인이 실패하는 레코드가 없다는 것을 보장해야 합니다.
(Null에 대한 처리를 위해 옵티마이저 내부적으로 IS NOT NULL 조건을 추가)
- 조인 제거 관련 파리미터 및 힌트 (조인 제거 기능 ON/OFF)
- alter session set "_optimizer_join_elimination_enabled" = true;
- /*+ eliminate_join(d) */
- /*+ no_eliminate_join(d) */
- 조인제거 예시 (1) 등치조인
- 1:M 관계에서 1쪽 테이블을 읽지 않고
- dept에 pk 제약조건 + emp에 fk(deptno) 제약조건이 존재하며
- 1쪽 테이블 집합 Join이 불필요하다면 => "불필요한 조인 제거"
select e.empno, e.ename, e.deptno, e.sal, e.hiredate
from dept d, emp e
where d.deptno = e.deptno;- 조인제거 예시 (2) M쪽 Outer 조인 시 조인제거 (11g부터 기능 구현)
- 1:M 관계에서 1쪽 테이블을 읽지 않고
- 1쪽 테이블 집합 Join이 불필요하다면 => "불필요한 조인 제거"
- Outer Join시 조인제거는 11g부터 기능 구현
+) FK 및 emp.deptno에 Not null 제약 조건이 없어도 가능합니다.
select e.empno, e.ename, e.sal, e.hiredate
from emp e, dept d
where d.deptno(+) = e.deptno;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 588 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 588 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
✏️ 4. OR 조건을 Union으로 변환 (OR-Expansion)
- OR 연산자나 IN 연산자를 사용했을 때,
내부적으로 Concatnation 실행계획으로 처리되어 마치 2개 쿼리로 나누어져서 실행되는 쿼리 변환 기능입니다. - 제어힌트
- use_concat: OR-Expansion을 유도합니다.
- ⭐⭐⭐ no_expand: OR-Expansion을 방지합니다.
- alter session set "_no_or_expansion"=true;로도 설정 가능합니다.
- OR-Expansion (1)
- 기본 쿼리
- Table Access Full: 인덱스를 효과적으로 사용하지 못합니다.
- use_concat
- index range scan: 분기된 쿼리마다 별도 인덱스 스캔이 이루어집니다.
- 하지만 emp 테이블의 액세스가 두 번 일어나므로, 중복 액세스되는 영역의 비중이 작을수록 효과적입니다.
(그 반대의 경우 오히려 쿼리수행 비용 증가 / 따라서 OR-Expansion은 CBO로 작동) - concatenation: 실행계획에서 concatenation 연산이 나타납니다.
- LNNVL 함수: 중복 액세스되더라도 결과 집합에는 중복이 없게 하려고 사용한 함수입니다.
- job <> 'clerk' 이거나 job IS NULL인 집합만 읽으려는 것
- 이 함수는 조건식이 False이거나 알 수 없는 값일 때 True를 리턴합니다.
- LNNVL (1=1): False / LNNVL(1=2): True / LNNVL(Null=1): True
- no_expand: OR-Expansion 연산 방지, alter session set "_no_or_expansion"=true;로도 설정 가능합니다.
- 기본 쿼리
-- 기본 쿼리
select * from emp where job = 'CLERK' or deptno = 20;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 259 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 7 | 259 | 3 (0)| 00:00:01 | -- 인덱스 효과적 X
--------------------------------------------------------------------------
-- use_concat: or 조건을 union all 형태로 변환하도록 지시
select /*+ use_concat */ *
from emp
where job = 'CLERK' or deptno = 20;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 304 | 4 (0)| 00:00:01 |
| 1 | ☆CONCATENATION☆ | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 4 | 152 | 2 (0)| 00:00:01 |
|* 3 | ☆INDEX RANGE SCAN☆ | EMP_JOB_IDX | 4 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 4 | 152 | 2 (0)| 00:00:01 |
|* 5 | ☆INDEX RANGE SCAN☆ | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("JOB"='CLERK')
4 - filter(LNNVL("JOB"='CLERK')) -- LNNVL: 중복 제거를 위해 사용
5 - access("DEPTNO"=20)
-- no_expand: alter session set "no_or_expansion"=true;로도 설정 가능
select /*+ no_expand */ *
from emp
where job = 'CLERK' or deptno = 20;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 259 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 7 | 259 | 3 (0)| 00:00:01 | -- 인덱스 효과적 X
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=20 OR "JOB"='CLERK')
✏️ 5. 기타 쿼리 변환
- Count(컬럼명) 시 Null 제외
- Count(컬럼명) 시 Null은 포함되지 않습니다.
⚠️ 단, Count(*) 함수는 Null을 포함한 모든 행 카운트 - 따라서 조건절에 IS NOT NULL 을 추가하여 결과집합에서 제외한 후 Count 합니다.
- Count(컬럼명) 시 Null은 포함되지 않습니다.
-- 기본 쿼리
select count(e.empno), count(d.dname)
from emp e, dept d
where d.deptno = e.deptno
and d.sal <= 2000;
-- 쿼리 변환: Count 시 Null 제외
select count(e.empno), count(d.dname)
from emp e, dept d
where d.deptno = e.deptno
and e.sal <= 2000
and e.deptno is not null --- IS NOT NULL 추가
and d.deptno is not null; --- IS NOT NULL 추가- 비정상적 조건을 위한 필터 조건 추가
- 정상적 조건: MIN <= MAX
- 비정상적 조건: MIN > MAN
select *
from emp
where sal between :max and :min; --- 2000부터 10? 이상하죠?- 조건절 순서 비교
- 과거 DBMS: 조건절 기술 순서에 따라 실행계획 수립했으나
- 최신 DBMS: CBO로 최적화하여 결정합니다.
- 비교 연산을 적게 처리하는 효과가 있습니다.
select *
from t
where c1 = 1
and c2 = 999; --- c2 조건을 먼저 검색하는 것이 유리| C1 먼저 | ▶ 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 (9번) |
| C2 먼저 | 991 | 992 | 993 | 994 | 995 | 996 | 997 | 998 | 999 ◀ (1번) |
📒 정리하면
- 옵티마이저의 뷰 처리: 1차적으로 뷰 Merging 수행 → 실패 시 조건절 Pushing을 시도합니다. (뷰 안에 ROWNUM, 분석함수가 있을 경우 불가)
- 조건절 Pushdown: 조건절을 뷰 안으로 밀어넣어 처리합니다.
- 조건절 Pullup: 뷰 내부 조건을 외부로 끌어올려 최적화합니다.
- 조인조건 Pushdown: NL 조인 시 Outer 조건을 Inner쪽 뷰 쿼리 안으로 밀어넣습니다. (힌트: push_pred)
- LATERAL 키워드: 상관 섭즈쿼리를 FROM 절에서 인라인 뷰처럼 사용할 수 있게 합니다. (12c 도입)
- 조건절 이행: 연역법을 통해 새로운 조건을 생성합니다. (A=B이고, B=C이면 "A=C이다")
- 불필요한 조인 제거: 1:M 관계에서 1쪽 테이블 참조가 불필요할 때 조인을 제거합니다. (PK, FK 존재)
- OR-Expansion: OR 조건을 Union으로 변환하여 인덱스 사용을 최적화합니다. (힌트: use_concat ↔ no_expand)
- Count(컬럼명) 함수 최적화: Null 값 처리를 위해 IS NOT NULL 조건을 추가합니다.
- 비정상적 조건 처리: MIN > MAX 같은 비정상 조건에 대한 필터를 추가합니다.
- 조건절 순서 최적화: CBO 기반 최적화를 통해 효율적인 조건 처리 순서를 결정합니다.
반응형
