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




멋진넘

2011.03.29 13:44:24
*.241.147.22

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