오라클 성능 고도화 원리와 해법 II (2012년)
- 서브쿼리의 분류
- 서브쿼리 Unnesting의 의미
- 서브쿼리 Unnesting의 이점
- 서브쿼리 Unnesting 기본 예시
- Unnesting된 쿼리의 조인 순서 조정
- 서브쿼리가 M쪽 집합이거나 Nonunique 인덱스일 때
- 필터 오퍼레이션과 세미조인의 캐싱 효과
- Anti 조인
- 집계 서브쿼리 제거
- Pushing 서브쿼리
서브쿼리의 분류
| 인라인 뷰(Inline View) | from 절에 나타나는 서브쿼리 |
| 중첩된 서브쿼리(Nested Subquery) | where 절에 사용된 서브쿼리. 특히, 서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태는 '상관관계 있는(Correlated) 서브쿼리'라고 함. |
| 스칼라 서브쿼리(Scalar Subquery) | 한 레코드당 하나의 컬럼 값만을 리턴하는 서브 쿼리. |
- 옵티마이저는 쿼리 블록 단위로 최적화를 수행하고, 각 서브쿼리를 최적화했다고 쿼리 전체가 최적화됐다고 할 순 없음.
- 서브쿼리 Unnesting 은 중첩된 서브쿼리(Nested Subquery), 뷰 Merging 은 인라인 뷰와 관련이 있음.
서브쿼리 Unnesting의 의미
- 중첩된 서브쿼리를 풀어내는 것을 말함.
- 중첩된 서브쿼리는 메인쿼리와 부모와 자식이라는 종속적이고 계층적인 관계.
- 처리과정은 필터 방식. 즉, 메인 쿼리에서 읽히는 레코드마다 서브쿼리를 반복 수행하면서 조건에 맞지 않는 데이터를 골라내는 것.
- 필터 방식이 항상 최적의 수행속도를 보장하지 못하므로 옵티마이저는 두가지 방식 중 하나를 선택함.
- 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화. 서브쿼리 Unnesting 임.
- 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있게 됨.
- 원래 상태에서 최적화 수행. 메인쿼리와 서브쿼리 각각 최적화 수행. 이때 서브쿼리에 필터 오퍼레이션이 나타남.
- 각각의 최적이 쿼리문 전체의 최적을 달성하지 못할 때가 많음.
서브쿼리의 또 다른 최적화 기법
- where 조건절에 사용된 서브쿼리가 1) 메인쿼리와 상관관계에 있지 않으면서 2) 단일 로우를 리턴하는 형태의 서브쿼리를 처리할 때 나타나는 방식.
- Fetch가 아닌 Execute 시점에 먼저 수행됨. 그 결과 값을 메인 쿼리에 상수로 제공.
서브쿼리 Unnesting의 이점
- 서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있다.
- 서브쿼리 Unnesting과 관련한 힌트 : unnest, no_unnest
서브쿼리 Unnesting 기본 예시
-- 원래 쿼리
select * from emp
where deptno in (select deptno from dept)
;
-- no_unnest
SQL> explain plan for
2 select * from emp
3 where deptno in (select /*+ no_unnest */ deptno from dept)
4 ;
해석되었습니다.
경 과: 00:00:00.14
SQL> @plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1783302997
------------------------------------------------------------------------------
| 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)
21 개의 행이 선택되었습니다.
경 과: 00:00:00.11
- 옵티마이저가 서브쿼리 Unnesting을 선호하므로 no_unnest 힌트 사용
- 필터 방식으로 수행된 서브쿼리의 조건절이 바인드 변수로 처리됨("DEPTNO"=:B1).
- 이것을 통해 서브쿼리를 별도로 최적화한다는 것을 알 수 있음.
- Unnesting하지 않은 서브쿼리를 수행할 때는 메인 쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반복 수행함.
-- unnest
SQL> explain plan for
2 select * from emp
3 where deptno in (select /*+ unnest */ deptno from dept)
4 ;
해석되었습니다.
경 과: 00:00:00.03
SQL> @plan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3074306753
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 560 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 560 | 3 (0)| 00:00:01 |
| 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):
---------------------------------------------------
3 - access("DEPTNO"="DEPTNO")
19 개의 행이 선택되었습니다.
경 과: 00:00:00.03
Unnesting된 쿼리의 조인 순서 조정
- Unnesting에 의해 일반 조인문으로 변환된 후에는 emp, dept 어느 쪽이든 드라이빙 집합으로 선택될 수 있다.
- 메인 쿼리 집합을 먼저 드라이빙 하려면 : leading(emp) 힌트 사용
- 서브 쿼리 집합을 먼저 드라이빙 하려면 : 서브쿼리에서 메인 쿼리에 있는 테이블을 참조할 수는 있지만, 메인 쿼리에서 서브쿼리 쪽 테이블을 참조하지는 못 함.
select /*+ leading(dept) */ * from emp
where deptno in (select /*+ unnest */ deptno from dept)
- 10g 부터는 이상하게도 위처럼 해도 조인 순서가 조정된다고 함.
- leading 힌트 대신 ordered 힌트 사용. 이것을 통해 Unnesting 된 서브쿼리가 from 절에서 앞쪽에 위치함을 알 수 있음.
select /*+ ordered */ * from emp
where deptno in (select /*+ unnest */ deptno from dept)
- 10g부터는 qb_name 힌트 사용하면 됨.
select /*+ leading(dept@qb1) */ * from emp
where deptno in (select /*+ unnest qb_name(qb1) */ deptno from dept)
서브쿼리가 M쪽 집합이거나 Nonunique 인덱스일 때
- 메인 쿼리에 서브쿼리가 종속적인 관계이므로 일반 조인문으로 바뀌더라도 메인 쿼리의 집합이 보장되어야 옵티마이저가 안심하고 쿼리 변환을 실시 할 수 있음.
- 지금까지 예제는 메인 쿼리의 emp 테이블과 서브쿼리의 dept 테이블이 M:1 관계라는 것을 옵티마이저가 dept 테이블의 deptno 컬럼에 PK 제약이 설정되어 있는 것을 보고 알 수 있으므로 조인을 하더라도 메인 쿼리의 집합이 보장되므로 쿼리 변환을 실시.
<사례1 - 서브쿼리가 M쪽 집합일 때>
select * from dept
where deptno in (select deptno from emp)
- dept 테이블 기준으로 집합이 만들어져야 되므로 결과집합은 1 집합이 되야함.
- 아래와 같은 일반 조인문으로 변환한다면 emp 단위의 결과집합(M * 1 = M)이 만들어지므로 결과 오류가 생김
select *
from (select deptno from emp) a, dept b
where b.deptno = a.deptno
<사례2 - 테이블 간의 관계를 알 수 없을 때>
select * from emp
where deptno in (select deptno from dept)
- M쪽 집합을 드라이빙해 1쪽 집합을 서브쿼리로 필터링하므로 조인문으로 바꾸더라도 결과 오류 생기지 않음.
- 하지만 dept 테이블 deptno 컬럼에 PK/Unique 제약 또는 Unique 인덱스가 없다면 두 테이블간의 관계를 알 수 없으므로 옵티마이저는 일반 조인문으로 쿼리 변환을 시도하지 않음.
서브쿼리 쪽 집합을 1집합으로 만들기 위한 옵티마이저는 두가지 방식 중 하나를 선택
- 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면, 먼저 sort unique 오퍼레이션 수행함으로써 1쪽 집합으로 만든 다음 조인.
- 메인 쿼리 쪽 테이블이 드라이빙 된다면 세미 조인(Semi Join) 방식으로 조인.
Sort Unique 오퍼레이션 수행
- dept 테이블에서 PK 제약을 제거, deptno 컬럼에 Nonunique 인덱스 생성
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 ;
해석되었습니다.
경 과: 00:00:00.00
SQL> @plan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2059974117
--------------------------------------------------------------------------------------
| 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")
16 개의 행이 선택되었습니다.
- 아래와 같이 쿼리 변환이 일어난 것임.
select b.*
from (select /*+ no_merge */ distinct deptno from dept order by deptno) a, emp b
where b.deptno = a.deptno
세미 조인 방식으로 수행
- 기본적으로 NL 조인과 동일한 프로세스로 진행하지만, Outer(=Driving) 테이블의 한 로우가 Inner 테이블의 한 로우와 조인에 성공하는 순간 진행을 멈추고 Outer 테이블의 다음 로우를 계속 처리하는 방식.
- 세미 조인 방식으로 변환의 장점은 NL 세미 조인, 해시 세미 조인, 소트머지 세미 조인도 가능하다는 것.
SQL> explain plan for
2 select * from emp
3 where deptno in (select deptno from dept)
4 ;
해석되었습니다.
경 과: 00:00:00.00
SQL> @plan
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3740080709
--------------------------------------------------------------------------------------
| 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")
15 개의 행이 선택되었습니다.
필터 오퍼레이션과 세미조인의 캐싱 효과
- 서브쿼리를 Unnesting 하지 않으면 필터 오퍼레이션을 사용할 수 밖에 없는데 다행히 오라클은 서브쿼리의 수행결과를 내부 캐시에 저장했다가 같은 입력값이 들어오면 저장된 값을 출력하는 필터 최적화 기법을 가지고 있음.
-- emp 테이블을 100번 복제한 t_emp 테이블 생성
create table t_emp
as
select *
from emp
,(select rownum no from dual connect by level <= 100)
;
-- 필터 방식으로 수행
select count(*)
from t_emp t
where exists (select /*+ no_unnest */
'x'
from dept
where deptno = t.deptno
and loc is not null)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 18 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 20 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=18 pr=0 pw=0 time=0 us)
1400 FILTER (cr=18 pr=0 pw=0 time=18 us)
1400 TABLE ACCESS FULL T_EMP (cr=12 pr=0 pw=0 time=4 us cost=5 size=18200 card=1400)
3 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)
- dept 테이블에 대한 필터링을 1,400번 수행했어도 읽은 블록수는 인덱스 3개, 테이블 3개 총 6개뿐임. 리턴 건수도 3개임.
- t_emp 테이블 deptno에 10, 20, 30 세개의 값(입력값)만 있으므로, 서브쿼리도 3번만 수행하고 같은 입력값이 들어오면 캐시에서 저장된 출력값을 읽어서 재사용함.
- 9i 에서는 필터 캐싱 효과 없음
1400 NESTED LOOPS SEMI (cr=1414 r=O w=O time=46453 us)
1400 TABLE ACCESS FULL T_EMP (cr=12 r=O w=O time=6613 us)
1400 TABLE ACCESS BY INDEX ROWID DEPT (cr=1402 r=O w=O time=20490 us)
1400 INDEX UNIQUE SCAN DETP_PK (cr=2 r=O w=O timee=6948 us) (object id 39130)
- NL 조인에서 Inner 쪽 인덱스 루트 블록에 대한 버퍼 Pinning 효과가 나타남을 알 수 있음. (rows 가 1400개인데 cr=2 밖에 안됨) 위 필터 방식에서는 안 나타남.
- 10g 부터는 NL 세미 조인도 캐싱효과를 가짐.
select count(*)
from t_emp t
where exists (select /*+ unnest nl_sj */
'x'
from dept
where deptno = t.deptno
and loc is not null)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 15 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 16 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=15 pr=0 pw=0 time=0 us)
1400 NESTED LOOPS SEMI (cr=15 pr=0 pw=0 time=18 us cost=1406 size=33600 card=1400)
1400 TABLE ACCESS FULL T_EMP (cr=12 pr=0 pw=0 time=6 us cost=5 size=18200 card=1400)
3 TABLE ACCESS BY INDEX ROWID DEPT (cr=3 pr=0 pw=0 time=0 us cost=1 size=44 card=4)
3 INDEX RANGE SCAN DEPT_DEPTNO_IDX (cr=2 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 91722)
Anti 조인
- not exists, not in 서브쿼리도 Unnesting 하지 않으면 필터 방식으로 처리됨.
- 조인에 성공하는 레코드가 하나도 없을 때만 결과집합에 포함됨.
-- Unnesting 하지 않아서 필터 처리됨
SQL> explain plan for
2 select *
3 from dept d
4 where not exists(select /*+ no_unnest */
5 'x'
6 from emp
7 where deptno = d.deptno)
8 ;
해석되었습니다.
경 과: 00:00:00.07
SQL> @plan
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 3547749009
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 60 | 7 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 2 | 6 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "EMP"
WHERE "DEPTNO"=:B1))
3 - filter("DEPTNO"=:B1)
17 개의 행이 선택되었습니다.
-- Unnesting 하면 Anti 조인 방식으로 처리됨.
SQL> explain plan for
2 select *
3 from dept d
4 where not exists(select /*+ unnest nl_aj */
5 'x'
6 from emp
7 where deptno = d.deptno)
8 ;
해석되었습니다.
경 과: 00:00:00.00
SQL> @plan
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 1522491139
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 69 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 3 | 69 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 5 | 15 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"="D"."DEPTNO")
15 개의 행이 선택되었습니다.
-- merge_aj
select *
from dept d
where not exists(select /*+ unnest merge_aj */
'x'
from emp
where deptno = d.deptno)
;
-- hash_aj
select *
from dept d
where not exists(select /*+ unnest hash_aj */
'x'
from emp
where deptno = d.deptno)
;
- NL Anti 조인과 머지 Anti 조인은 기본 처리루틴이 not exists 필터와 같지만, 해시 Anti 조인은 다름.
- dept를 해시 테이블로 빌드
- emp를 스캔하면서 해시 테이블 탐색, 조인 성공 엔트리에 표시해놓음.
- 마지막으로 해시 테이블을 스캔하면서 표시가 없는 엔트리만 결과집합에 담음.
집계 서브쿼리 제거
- 집계 함수를 포함하는 서브쿼리를 Unnesting 하고 분석함수로 대체. 10g 부터 도입됨.
-- 집계 서브쿼리가 있는 쿼리
select d.deptno, d.dname, e.empno, e.ename, e.sal
from emp e, dept d
where d.deptno = e.deptno
and e.sal >= (select avg(sal) from emp where deptno = d.deptno)
- Unnesting 하면 1차적으로 서브쿼리가 인라인뷰로 변환되는데, 옵티마이저는 인라인 뷰를 Merging 하거나 그대로 둔 채 최적화할 수 있음.
- 10g 부터는 인라인 뷰를 제거하고 분석함수를 사용하는 형태로 변환이 가능해짐.
- 비용기반으로 작동함.
-- 분석함수를 사용하는 형태로 변환된 쿼리
select deptno, dname, empno, ename, sal
from (select d.deptno, d.dname, e.empno, e.ename, e.sal
,(case when e.sal >= avg(sal) over (partition by d.deptno)
then e.rowid end) max_sal_rowid
from emp e, dept d
where d.deptno = e.deptno)
where max_sal_rowid is not null
-- 분석함수를 사용하는 형태로 변환되었을 때 실행계획
SQL> explain plan for
2 select d.deptno, d.dname, e.empno, e.ename, e.sal
3 from emp e, dept d
4 where d.deptno = e.deptno
5 and e.sal >= (select avg(sal) from emp where deptno = d.deptno)
6 ;
해석되었습니다.
경 과: 00:00:00.00
SQL> @plan
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 3722278325
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 938 | 6 (17)| 00:00:01 |
|* 1 | VIEW | VW_WIF_1 | 14 | 938 | 6 (17)| 00:00:01 |
| 2 | WINDOW BUFFER | | 14 | 686 | 6 (17)| 00:00:01 |
| 3 | MERGE JOIN | | 14 | 686 | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | DEPT_DEPTNO_IDX | 4 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 14 | 504 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 504 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("VW_COL_6" IS NOT NULL)
6 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
21 개의 행이 선택되었습니다.
- 쿼리에서는 emp 테이블 두 번 참조했지만 실행계획상으로는 한번, 대신 window buffer 오퍼레이션 추가됨.
Pushing 서브쿼리
- Unnesting 되지 않은 서브쿼리는 항상 필터 방식으로 처리되며, 대개 마지막 단계에서 처리됨.
- 서브쿼리를 먼저 처리하여 다음 수행 단계로 넘어가는 로우 수를 줄일 수 있다면 성능 향상이 될 수 있음.
- Pushing 서브쿼리는 실행계획상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 강제하는 것을 말함.
- 힌트 : push_subq
- Unnesting 되지 않은 서브쿼리에만 작동함. no_unnest 힌트와 같이 기술하는 것이 올바름.
-- 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)
서브쿼리 필터와 Pushing 서브쿼리와의 결과 비교
- 필터는 emp1과 emp2의 조인 시도 횟수가 14,000번, Pushing 서브쿼리는 서브쿼리를 먼저 수행해서
- emp1의 결과건수가 3,000건이 됐고 emp2 테이블과의 조인 횟수도 3,000번으로 줌.
- 읽은 블록수도 14,348 에서 3,348로 줌.
"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3285
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.