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

h3.(1) 스칼라 서브쿼리

서브쿼리 : 쿼리에 내장된 또다른 쿼리 블록
스칼라 서브쿼리 : 함수처럼 한 레코드당 정확히 하나의 값만을 리턴하는 서브쿼리

h3.(2) 스칼라 서브쿼리의 캐싱효과

• 오라클은 스칼라 서브쿼리 수행 횟수를 최소화 하기위해 입력 값과 출력 값은 내부캐시에 저장한다.
• 스칼라 서브쿼리가 수행될 때 우선 입력 값을 찾고 있으면 저장된 출력 값을 리턴한다.
• 내부 캐시에서 출력 값을 찾지 못할 경우 뭐리를 수행하며, 결과는 캐시에 저장한다.

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;


     EMPNO ENAME             SAL HIREDATE (SELECTD.DNAME
---------- ---------- ---------- -------- --------------
      7566 JONES            2975 81/04/02
      7698 BLAKE            2850 81/05/01
      7782 CLARK            2450 81/06/09
      7788 SCOTT            3000 87/03/20
      7839 KING             5000 81/11/17
      7902 FORD             3000 81/12/03

6 개의 행이 선택되었습니다.


---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |    11 |   242 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|*  3 |  TABLE ACCESS FULL          | EMP     |    11 |   242 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPTNO"=:B1)
   3 - filter("SAL">=2000)

스칼라 서브쿼리의 특징

• 입력 값과 출력 값을 빠르게 저장하고 찾기 위해 해싱 알고리즘을 이용한다.
• 8i, 9i는 256개의 엔트리를 캐싱하고, 10g는 _qurery_execution_cach_max_size 파라미터에 의해 캐시 사이즈가 결정된다.
• 해시 충돌시 기존 캐시 엔트리는 그대로 둔채 스칼라 서브쿼리만 한 번더 수행하며,
  해시 충돌이 발생한 입력값이 반복적으로 입력 되면 스칼라 서브쿼리도 반복적으로 수행된다.

(3) 두 개 이상의 값을 리턴하고 싶을 때

• 스칼라 서브 쿼리는 한레코드당 하나의 값만 리턴하는 특성이 있다.
• substr 함수 이용 과 type object 를 이용한 방법으로 두개 이상의 값을 리턴 할 수 있다.

1. substr 함수 이용

• 구하고자 하는 값들을 모두 결합하고 바깥쪽 액세스 쿼리에서 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'
  );
 

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    30 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    26 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    26 |     3   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE    |      |     1 |    26 |            |          |
|*  4 |   TABLE ACCESS FULL| EMP  |     1 |    26 |     3   (0)| 00:00:01 |
|   5 |  SORT AGGREGATE    |      |     1 |    26 |            |          |
|*  6 |   TABLE ACCESS FULL| EMP  |     1 |    26 |     3   (0)| 00:00:01 |
|*  7 |  TABLE ACCESS FULL | DEPT |     1 |    30 |     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')

Note
-----
   - dynamic sampling used for this statement

2. type object이용 하는 방법

• 출력하고자 하는 한개 이상의 값들을 type object로 생성하여 출력하는 방법
• substr 함수를 이용하는것보다 보기에 훨씬 깔끔하다.

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 ;



    DEPTNO DNAME          SAL.AVG_SAL SAL.MIN_SAL SAL.MAX_SAL
---------- -------------- ----------- ----------- -----------
        30 SALES           1566.66667         950        2850


Execution Plan
----------------------------------------------------------
Plan hash value: 2018188441

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    30 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    26 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    26 |     3   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE    |      |     1 |    26 |            |          |
|*  4 |   TABLE ACCESS FULL| EMP  |     1 |    26 |     3   (0)| 00:00:01 |
|   5 |  SORT AGGREGATE    |      |     1 |    26 |            |          |
|*  6 |   TABLE ACCESS FULL| EMP  |     1 |    26 |     3   (0)| 00:00:01 |
|*  7 |  TABLE ACCESS FULL | DEPT |     1 |    30 |     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')

Note
-----
   - dynamic sampling used for this statement