(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





  • 입 출력 값을 찾기 위해 해싱알고리즘 사용
  • 주의사항
    • 해시 충돌 발생시 기존 캐시 앤트리를 유지하고 스칼라 서브쿼리만 한번더 수행(반복수행됨)
    • 캐싱효과는 입력값이 소수여서 해시 충돌 가능성이 적어야 하며 반대의 경우라면 비효율
    • 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
  • 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^