6. 스칼라 서브쿼리를 이용한 조인

(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
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^

댓글 (1개)

멋

멋진넘 2011.03.29 13:44

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