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