6. 스칼라 서브쿼리를 이용한 조인
2011.03.28 15:11
(1) 스칼라 서브 쿼리
- 서브쿼리
- 쿼리에 내장된 또다른 쿼리 블록
- 스칼라 서브쿼리
- 함수처럼 한 레코드당 정확히 하나의 값만을 리턴하는 서브 쿼리
스칼라 서브쿼리
select empno,ename, sal, hiredate
,(select d.dname from dept d where d.deptno=e.deptno) dname
from emp e
where sal>=2000
Execution Plan
----------------------------------------------------------
Plan hash value: 2981343222
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 220 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 11 | 220 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEPTNO"=:B1)
3 - filter("SAL">=2000)
동일 결과를 나타내는 Outer 조인
select /*+ ordered use_nl(d) */ e.empno,e.ename, e.sal,e.hiredate, d.dname
from emp e, dept d
where d.deptno(+)=e.deptno
and e.sal >= 2000
Execution Plan
----------------------------------------------------------
Plan hash value: 1301846388
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 341 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 11 | 341 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 11 | 220 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."SAL">=2000)
4 - access("D"."DEPTNO"(+)="E"."DEPTNO")
∴ 결과 및 조인수행 처리경로도 동일하며 스칼라 서브쿼리에는 내부적으로 캐싱 기법이 작용
(2) 스칼라 서브쿼리의 캐싱 효과
- 내부캐시 (Query Exection Cache)
- 스칼라 서브쿼리 수행횟수를 최소하 하려고 입력 값과 출력 값을 저장해 두어 사용
- 일단 입력값을 캐시에서 찾아보고 있으면 저장된 값을, 없을 때는 쿼리를 수항하고 결과를 다시 캐시에 저장
- 입력값
- 메인 쿼리의 컬럼 값
select empno, ename, sal, hiredate
,(
select d.dname -- 출력값 : d.dname
from dept d
where d.deptno=e.empno -- 입력값: e.empno
)
from emp e
where sal>=2000;
- 반복 수행되는 함수때문에 성능이 저하될경우 아래와 같이 캐싱기능을 활용하면 유용함
select empno,ename,sal,hiredate
,(select get_dname(deptno) from dual) dname
from emp e
where sal>=2000
- 입 출력 값을 찾기 위해 해싱알고리즘 사용
- 8i,9i
- 256개 엔트리를 캐싱
- 10g
- 입력과 출력값크기
- _query_execution_cach_max_size 파라미터에 의해 캐시 사이즈가 결정 참조
- 참조 - CBO 오라클 원리 (9장 쿼리 변환 / 스칼라 서브쿼리 PAGE 266 )
- 주의사항
- 해시 충돌 발생시 기존 캐시 앤트리를 유지하고 스칼라 서브쿼리만 한번더 수행(반복수행됨)
- 캐싱효과는 입력값이 소수여서 해시 충돌 가능성이 적어야 하며 반대의 경우라면 비효율
- NL조인에서 inner쪽 인덱스와 테이블에 나타나는 버퍼 pinning효과도 사라짐
(3) 두개 이상의 값을 리턴하고 싶을때
위치가 ‘CHICAGO’인 부서만 대상으로 급여 수준을 집계
select d.deptno, d.dname, avg_sal,min_sal,max_sal
from dept d
,(select deptno, avg(sal) avg_sal,min(sal) min_sal,max(sal) max_sal
from emp group by deptno) e
where e.deptno(+)=d.deptno
and d.loc=’CHICAGO’
Execution Plan
----------------------------------------------------------
Plan hash value: 1236585723
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 7 (29)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 1 | 70 | 7 (29)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 18 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3 | 156 | 5 (40)| 00:00:01 |
| 5 | VIEW | | 3 | 156 | 4 (25)| 00:00:01 |
| 6 | HASH GROUP BY | | 3 | 15 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 70 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("D"."LOC"='CHICAGO')
4 - access("E"."DEPTNO"(+)="D"."DEPTNO")
filter("E"."DEPTNO"(+)="D"."DEPTNO")
∴ 사원 테이블 전체를 읽어야하므로 비효율적
select d.deptno,d.dname
,(select avg(sal),min(sal),max(sal) from emp where deptno=d.deptno)
from dept d
where d.loc=’CHICAGO’
∴ 스칼라 서브 쿼리는 한레코드당 하나의 값만 리턴하므로 사용불가능
select d.deptno,d.dname
,(select avg(sal) from emp where deptno=d.deptno) avg_sal
,(select min(sal) from emp where deptno=d.deptno) min_sal
,(select max(sal) from emp where deptno=d.deptno) max_sal
from dept d
where d.loc='CHICAGO'
Execution Plan
----------------------------------------------------------
Plan hash value: 3540364102
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| EMP | 5 | 25 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 5 | | |
|* 4 | TABLE ACCESS FULL| EMP | 5 | 25 | 3 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 5 | | |
|* 6 | TABLE ACCESS FULL| EMP | 5 | 25 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | DEPT | 1 | 18 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=:B1)
4 - filter("DEPTNO"=:B1)
6 - filter("DEPTNO"=:B1)
7 - filter("D"."LOC"='CHICAGO')
참고 - 트레이스 결과
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 29 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 29 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=145 us)
6 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=105 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=44 us)
6 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=30 us)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=21 us)
6 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=16 us)
1 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=137 us)
∴ emp에서 같은 범위를 반복적으로 액세스 하는 비효율 발생
- 쿼리 튜닝 예
substr 함수로 분리하여 사용
SELECT DEPTNO,DNAME
, to_number(substr(sal,1,7)) avg_sal
,to_number(substr(sal,8,7)) min_sal
,to_number(substr(sal,15)) max_sal
from (
select d.deptno,d.dname
,(select lpad(avg(sal),7) ||lpad(min(sal),7) ||max(sal)
from emp where deptno=d.deptno) sal
from dept d
where d.loc='CHICAGO'
)
Execution Plan
----------------------------------------------------------
Plan hash value: 2317111044
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 | 3 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 51 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 18 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("D"."LOC"='CHICAGO')
참고 - 트레이스 결과
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 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 15 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
1 VIEW (cr=15 pr=0 pw=0 time=105 us)
1 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=41 us)
오브젝트 type을 사용
create or replace type sal_type as object
(avg_sal number,min_sal number,max_sal number)
/
select deptno,dname
,a.sal.avg_sal,a.sal.min_sal,a.sal.max_sal
from (
select d.deptno,d.dname
,(select sal_type(avg(sal),min(sal),max(sal) )
from emp where deptno=d.deptno) sal
from dept d
where d.loc='CHICAGO'
) a
Execution Plan
----------------------------------------------------------
Plan hash value: 2018188441
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| EMP | 5 | 25 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 5 | | |
|* 4 | TABLE ACCESS FULL| EMP | 5 | 25 | 3 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 5 | | |
|* 6 | TABLE ACCESS FULL| EMP | 5 | 25 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | DEPT | 1 | 18 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=:B1)
4 - filter("DEPTNO"=:B1)
6 - filter("DEPTNO"=:B1)
7 - filter("D"."LOC"='CHICAGO')
참고 - 트레이스 결과
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 15 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 30 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=36 us)
6 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=22 us)
1 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=44 us)
- 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
- 작성자: 남송휘 (tofriend)
- 최초작성일: 2011년 3월 27일
- 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
- 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 40 | 2. 서브쿼리 Unnesting | darkbeom | 2011.05.15 | 19773 |
| 39 | 2. 소트를 발생시키는 오퍼레이션 | 휘휘 | 2011.06.12 | 3486 |
| 38 | 2. 파티션 Pruning | 실천하자 | 2011.06.21 | 26075 |
| 37 |
3. 병렬 조인
 | 실천하자 | 2011.06.28 | 7186 |
| 36 |
3. 다양한 인덱스 스캔 방식
| 멋진넘 | 2011.02.18 | 33849 |
| 35 |
3. 해시 조인
| darkbeom | 2011.03.20 | 21572 |
| 34 | 3. 옵티마이저의 한계 | 멋진넘 | 2011.04.19 | 7883 |
| 33 | 3. 뷰 Merging | 실천하자 | 2011.05.15 | 23488 |
| 32 |
3. 데이터 모델 측면에서의 검토
| 멋진넘 | 2011.06.13 | 5901 |
| 31 | 3. 인덱스 파티셔닝 | darkbeom | 2011.06.19 | 54061 |
| 30 |
4. 테이블 Random 액세스 부하
[1] | darkbeom | 2011.02.23 | 14722 |
| 29 | 4. 조인 순서의 중요성 | 운영자 | 2011.03.27 | 14328 |
| 28 | 4. 통계정보 Ⅰ | darkbeom | 2011.04.25 | 18187 |
| 27 | 4. 조건절 Pushing | 실천하자 | 2011.05.31 | 7086 |
| 26 |
4. 소트가 발생하지 않도록 SQL 작성
| 멋진넘 | 2011.06.12 | 7924 |
| 25 | 5. 테이블 Random 액세스 최소화 튜닝 | suspace | 2011.02.24 | 7003 |
| » | 6. 스칼라 서브쿼리를 이용한 조인 [1] | 휘휘 | 2011.03.28 | 6661 |
| 23 | 5. Outer 조인 | 실천하자 | 2011.03.29 | 5059 |
| 22 | 5. 카디널리티 | suspace | 2011.04.26 | 5975 |
| 21 | 5. 조건절 이행 | 휘휘 | 2011.05.29 | 5453 |
create or replace function get_dname(v_deptno number) return varchar2
as
v_dname dept.DNAME%TYPE;
begin
select dname
into v_dname
from dept
where deptno = v_deptno;
return v_dname;
end;
********************************************************************************
select empno, ename, sal, hiredate
, get_dname(deptno) dname
from emp e
where sal > 2000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 9 0 6
Rows Row Source Operation
------- ---------------------------------------------------
6 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=0 us cost=3 size=330 card=6)
********************************************************************************
select empno, ename, sal, hiredate
, (select dname from dept d where d.deptno = e.deptno) dname
from emp e
where sal > 2000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.06 3 4 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 6 13 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.06 9 17 0 6
Rows Row Source Operation
------- ---------------------------------------------------
3 TABLE ACCESS BY INDEX ROWID DEPT (cr=5 pr=6 pw=0 time=0 us cost=2 size=22 card=1)
3 INDEX UNIQUE SCAN PK_DEPT (cr=2 pr=1 pw=0 time=0 us cost=1 size=0 card=1)(object id 73195)
6 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=0 us cost=3 size=330 card=6)
********************************************************************************
select empno, ename, sal, hiredate
, (select get_dname(e.deptno) from dual) dname
from emp e
where sal > 2000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 10 0 6
Rows Row Source Operation
------- ---------------------------------------------------
3 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
6 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=0 us cost=3 size=330 card=6)
********************************************************************************
SQL ID: 56r5sd49t3jrv - Function SQL 부분
Plan Hash: 2852011669
SELECT DNAME
FROM
DEPT WHERE DEPTNO = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9 0.00 0.00 0 0 0 0
Fetch 9 0.00 0.00 0 18 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.00 0.00 0 18 0 9
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
1 INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 73195)
********************************************************************************
이렇게 test하는게 맞는지 모르겠지만... ^^;;;
교재의 예제처럼 Function은 매번 실행되고 Scalar SubQuery 및 Function을 Scalar SubQuery 처럼
사용하면 캐싱효과가 발생함(파라미터 : _query_execution_cache_max_size)
select a.ksppinm
, b.ksppstvl
, a.ksppdesc
from x$ksppi a
, x$ksppsv b
where 1 = 1
and a.indx = b.indx
and a.inst_id = b.inst_id
and a.ksppinm like '%query%cach%max%'
order by ksppinm
;
_query_execution_cache_max_size ▶ 65536