1. HOME
  2. [종료]구루비 DB 스터디
  3. 2016년 상반기 - 오라클 데이터베이스 스터디
  4. 02. 조인 원리와 활용 (06. 스칼라 서브쿼리를 이용한 조인 ~ 08. 고급 조인 테크닉)
  5. 06. 스칼라 서브쿼리를 이용한 조인~

06. 스칼라 서브쿼리를 이용한 조인~ (by manon94) [2016.07.02]

(1) 스칼라 서브쿼리

  • 서브쿼리 : 쿼리안의 쿼리
  • 스칼라 서브쿼리 : 하나의 값만 리턴하는 서브쿼리
    • 주로 Select-List 에서 사용됨 (다른 위치에서도 사용 가능)
스칼라서브쿼리
{code:sql}
SQL> SELECT empno, ename, sal, hiredate
2 , (SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) dname
3 FROM emp e
4 WHERE sal >= 2000
5 ;

EMPNO ENAME SAL HIREDATE DNAME

--
--
--
--
7566 JONES 2975 81/04/02 RESEARCH
7698 BLAKE 2850 81/05/01 SALES
7782 CLARK 2450 81/06/09 ACCOUNTING
7788 SCOTT 3000 87/04/19 RESEARCH
7839 KING 5000 81/11/17 ACCOUNTING
7902 FORD 3000 81/12/03 RESEARCH

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

Execution Plan

--
Plan hash value: 2981343222

---

IdOperationNameRowsBytesCost (%CPU)Time

---

0SELECT STATEMENT61503 (0)00:00:01
1TABLE ACCESS BY INDEX ROWIDDEPT1101 (0)00:00:01
  • 2
INDEX UNIQUE SCANPK_DEPT10 (0)00:00:01
  • 3
TABLE ACCESS FULLEMP61503 (0)00:00:01

---

Predicate Information (identified by operation id):

---

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

Statistics

--
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
903 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>

|

|| 아우터조인 ||
|{code:sql}
SQL> SELECT e.empno, e.ename, e.sal, e.hiredate
  2       , d.dname
  3    FROM emp  e
  4       , dept d
  5   WHERE e.deptno = d.deptno(+)
  6     AND e.sal >= 2000
  7  ;

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

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


Execution Plan
----------------------------------------------------------
Plan hash value: 3387915970

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     6 |   210 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     6 |   210 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     6 |   150 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     6 |    60 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("E"."DEPTNO"="D"."DEPTNO"(+))
   2 - filter("E"."SAL">=2000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        882  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL>

|

아우터조인(힌트사용)
{code:sql}
SQL> SELECT /*+ ordered use_nl(d) */
2 e.empno, e.ename, e.sal, e.hiredate
3 , d.dname
4 FROM emp e
5 , dept d
6 WHERE e.deptno = d.deptno(+)
7 AND e.sal >= 2000
8 ;

EMPNO ENAME SAL HIREDATE DNAME

--
--
--
--
7566 JONES 2975 81/04/02 RESEARCH
7698 BLAKE 2850 81/05/01 SALES
7782 CLARK 2450 81/06/09 ACCOUNTING
7788 SCOTT 3000 87/04/19 RESEARCH
7839 KING 5000 81/11/17 ACCOUNTING
7902 FORD 3000 81/12/03 RESEARCH

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

Execution Plan

--
Plan hash value: 1301846388

IdOperationNameRowsBytesCost (%CPU)Time

0SELECT STATEMENT62109 (0)00:00:01
1NESTED LOOPS OUTER62109 (0)00:00:01
  • 2
TABLE ACCESS FULLEMP61503 (0)00:00:01
3TABLE ACCESS BY INDEX ROWIDDEPT1101 (0)00:00:01
  • 4
INDEX UNIQUE SCANPK_DEPT10 (0)00:00:01

Predicate Information (identified by operation id):

---

2 - filter("E"."SAL">=2000)
4 - access("E"."DEPTNO"="D"."DEPTNO"(+))

Statistics

--
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
903 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>

|

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

- 오라클은 스칼라서브쿼리의 수행횟수를 최소화 하려고 입력값과 출력값을 내부캐시(Query Execution Cache)에 저장해 둔다
- 스칼라 서브쿼리 수행 => "입력값"을 캐시에서 찾아
-- 있으면 => "출력값"을 리턴
-- 없으면 => 쿼리 수행 결과 리턴 => 입출력 값을 캐시에 저장

|| 스칼라서브쿼리 (입력값/출력값) ||
|{code:sql}
SELECT empno, ename, sal, hiredate
     , (SELECT d.dname                -- 출력 값 : d.dname
          FROM dept d
         WHERE d.deptno = e.deptno    -- 입력 값 : e.deptno
        ) dname
  FROM emp e
 WHERE sal >= 2000
;

|

스칼라서브쿼리를 이용한 함수 호출 횟수 줄이기
{code:sql}
SELECT empno, ename, sal, hiredate
, get_dname(deptno) dname1 -- 일반함수 : 캐싱 효과 없음
, get_dname_Determi(deptno) dname2 -- Deterministic 함수 : CGA 에 캐싱, DB Call 내에서만 유효
, (SELECT get_dname(deptno) FROM dual) dname3 -- 스칼라 서브쿼리 : UGA 에 캐싱, 쿼리 끝날때까지 유효
FROM emp e
WHERE sal >= 2000
;
{code}
  • 입출력값을 빠르게 저장하고 찾기 위해 해싱 알고리즘 사용
  • 해시 사이즈 : 8i,9i(256개) 10g(입력과 출력값의 크기, _query_execution_cache_max_size 파라미터에 의해 결정)
  • 해시 충돌 발생시
    • 기존 값 밀어내고 새로운 값 대체(X)
    • 기존 값 유지(O)
  • 결론
    • 스칼라서브쿼리 캐싱효과는 입력값이 적어서 해시 충돌 가능성이 적을 때 효과적
    • 입력값이 적으면 : 해시탐색비용 + 캐싱효과로 쿼리수행비용 감소
    • 입력값이 많으면 : 해시탐색비용 + 해시충돌로 쿼리수행비용 증가 + NL조인의 버퍼 피닝 효과도 없음

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

  • 아래 쿼리는 시카고에 위치한 부서만 급여를 집계 하려는 것인데
  • 해당 부서 사원만 읽으면 좋은데 전체를 다 읽어 집계하는 비효율이 발생한다.
  • 스칼라 서브쿼리를 사용하여 emp 테이블의 풀스캔을 방지할 수 있다.
1. 쿼리 변환(View Merging) 발생 => 예상 했던 emp 풀스캔 비효율이 없음
{code:sql}
SQL> SELECT d.deptno
2 , d.dname
3 , e.avg_sal
4 , e.min_sal
5 , e.max_sal
6 FROM dept d
7 , (SELECT deptno
8 , AVG(sal) avg_sal
9 , MIN(sal) min_sal
10 , MAX(sal) max_sal
11 FROM emp
12 GROUP BY deptno
13 ) e
14 WHERE d.deptno = e.deptno(+)
15 AND d.loc = 'CHICAGO'
16 ;

DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL

--
--
--
--
--
30 SALES 1566.66667 950 2850

Execution Plan

--
Plan hash value: 2291734229

IdOperationNameRowsBytesCost (%CPU)Time

0SELECT STATEMENT2468 (25)00:00:01
1HASH GROUP BY2468 (25)00:00:01
  • 2
HASH JOIN OUTER51157 (15)00:00:01
  • 3
TABLE ACCESS FULLDEPT1163 (0)00:00:01
4TABLE ACCESS FULLEMP14983 (0)00:00:01

Predicate Information (identified by operation id):

---

2 - access("D"."DEPTNO"="DEPTNO"(+))
3 - filter("D"."LOC"='CHICAGO')

Statistics

--
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
711 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

|

|| 2. NO_MERGE 힌트 사용 => emp 풀스캔 비효율, 예상했던 11G의 Join Predicate Pushdown 은 발생 안함 ||
|{code:sql}
SQL> SELECT d.deptno
  2       , d.dname
  3       , e.avg_sal
  4       , e.min_sal
  5       , e.max_sal
  6    FROM dept d
  7       , (SELECT /*+ no_merge */
  8                 deptno
  9               , AVG(sal) avg_sal
 10               , MIN(sal) min_sal
 11               , MAX(sal) max_sal
 12            FROM emp
 13           GROUP BY deptno
 14          ) e
 15   WHERE d.deptno = e.deptno(+)
 16     AND d.loc = 'CHICAGO'
 17  ;

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


Execution Plan
----------------------------------------------------------
Plan hash value: 2939036968

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    68 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN OUTER     |      |     1 |    68 |     8  (25)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | DEPT |     1 |    16 |     3   (0)| 00:00:01 |
|   3 |   VIEW               |      |     3 |   156 |     4  (25)| 00:00:01 |
|   4 |    HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("D"."DEPTNO"="E"."DEPTNO"(+))
   2 - filter("D"."LOC"='CHICAGO')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        711  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

|

3. Index 생성 => Join Predicate Pushdown 발생
{code:sql}
SQL> CREATE INDEX idx_emp_deptno ON emp(deptno);

인덱스가 생성되었습니다.

SQL> SELECT d.deptno
2 , d.dname
3 , e.avg_sal
4 , e.min_sal
5 , e.max_sal
6 FROM dept d
7 , (SELECT /*+ no_merge */
8 deptno
9 , AVG(sal) avg_sal
10 , MIN(sal) min_sal
11 , MAX(sal) max_sal
12 FROM emp
13 GROUP BY deptno
14 ) e
15 WHERE d.deptno = e.deptno(+)
16 AND d.loc = 'CHICAGO'
17 ;

DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL

--
--
--
--
--
30 SALES 1566.66667 950 2850

Execution Plan

--
Plan hash value: 196983862

--

IdOperationNameRowsBytesCost (%CPU)Time

--

0SELECT STATEMENT1555 (0)00:00:01
1NESTED LOOPS OUTER1555 (0)00:00:01
  • 2
TABLE ACCESS FULLDEPT1163 (0)00:00:01
3VIEW PUSHED PREDICATE1392 (0)00:00:01
  • 4
FILTER
5SORT AGGREGATE17
6TABLE ACCESS BY INDEX ROWIDEMP5352 (0)00:00:01
  • 7
INDEX RANGE SCANIDX_EMP_DEPTNO51 (0)00:00:01

--

Predicate Information (identified by operation id):

---

2 - filter("D"."LOC"='CHICAGO')
4 - filter(COUNT(*)>0)
7 - access("DEPTNO"="D"."DEPTNO")

Statistics

--
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
711 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

|

|| 스칼라서브쿼리 오류 발생 => 단일값만 리턴해야 함 ||
|{code:sql}
SQL> SELECT d.deptno
  2       , d.dname
  3       , (SELECT AVG(sal) avg_sal
  4               , MIN(sal) min_sal
  5               , MAX(sal) max_sal
  6            FROM emp e
  7           WHERE e.deptno = d.deptno
  8          ) sal
  9    FROM dept d
 10   WHERE d.loc = 'CHICAGO'
 11  ;
     , (SELECT AVG(sal) avg_sal
        *
3행에 오류:
ORA-00913: 값의 수가 너무 많습니다

SQL>

|

스칼라서브쿼리 반복 사용 비효율
{code:sql}
SQL> SELECT d.deptno
2 , d.dname
3 , (SELECT AVG(sal) FROM emp WHERE deptno = d.deptno) avg_sal
4 , (SELECT MIN(sal) FROM emp WHERE deptno = d.deptno) min_sal
5 , (SELECT MAX(sal) FROM emp WHERE deptno = d.deptno) max_sal
6 FROM dept d
7 WHERE d.loc = 'CHICAGO'
8 ;

DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL

--
--
--
--
--
30 SALES 1566.66667 950 2850

Execution Plan

--
Plan hash value: 4035946398

---

IdOperationNameRowsBytesCost (%CPU)Time

---

0SELECT STATEMENT1163 (0)00:00:01
1SORT AGGREGATE17
2TABLE ACCESS BY INDEX ROWIDEMP5352 (0)00:00:01
  • 3
INDEX RANGE SCANIDX_EMP_DEPTNO51 (0)00:00:01
4SORT AGGREGATE17
5TABLE ACCESS BY INDEX ROWIDEMP5352 (0)00:00:01
  • 6
INDEX RANGE SCANIDX_EMP_DEPTNO51 (0)00:00:01
7SORT AGGREGATE17
8TABLE ACCESS BY INDEX ROWIDEMP5352 (0)00:00:01
  • 9
INDEX RANGE SCANIDX_EMP_DEPTNO51 (0)00:00:01
  • 10
TABLE ACCESS FULLDEPT1163 (0)00:00:01

---

Predicate Information (identified by operation id):

---

3 - access("DEPTNO"=:B1)
6 - access("DEPTNO"=:B1)
9 - access("DEPTNO"=:B1)
10 - filter("D"."LOC"='CHICAGO')

Statistics

--
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
711 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

|

|| 여러 값을 붙여서 스칼라서브쿼리 한번만 사용 ||
|{code:sql}
SQL> SELECT deptno
  2       , dname
  3       , TO_NUMBER(SUBSTR(sal,  1, 7)) avg_sal
  4       , TO_NUMBER(SUBSTR(sal,  8, 7)) min_sal
  5       , TO_NUMBER(SUBSTR(sal, 15, 7)) max_sal
  6    FROM (SELECT d.deptno
  7               , d.dname
  8               , (SELECT LPAD(AVG(sal), 7)
  9                      || LPAD(MIN(sal), 7)
 10                      || LPAD(MAX(sal), 7)
 11                    FROM emp
 12                   WHERE deptno = d.deptno
 13                  ) sal
 14            FROM dept d
 15           WHERE d.loc = 'CHICAGO'
 16          )
 17  ;

    DEPTNO DNAME             AVG_SAL    MIN_SAL    MAX_SAL
---------- -------------- ---------- ---------- ----------
        30 SALES             1566.66        950       2850


Execution Plan
----------------------------------------------------------
Plan hash value: 3370201435

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    16 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                |     1 |     7 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    35 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_EMP_DEPTNO |     5 |       |     1   (0)| 00:00:01 |
|*  4 |  TABLE ACCESS FULL           | DEPT           |     1 |    16 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   3 - access("DEPTNO"=:B1)
   4 - filter("D"."LOC"='CHICAGO')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        694  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

|

  • 10.2.0.4 에서는 실행계획상 3번 수행한 것처럼 보인다고 함.
  • 실제 트레이스를 확인해보면 한번만 수행된다고 함.
Object Type 사용
{code:sql}
SQL> CREATE OR REPLACE TYPE sal_type
2 AS OBJECT(avg_sal NUMBER, min_sal NUMBER, max_sal NUMBER)
3 /

유형이 생성되었습니다.

SQL> SELECT deptno
2 , dname
3 , a.sal.avg_sal avg_sal
4 , a.sal.min_sal min_sal
5 , a.sal.max_sal max_sal
6 FROM (SELECT d.deptno
7 , d.dname
8 , (SELECT sal_type(AVG(sal), MIN(sal), MAX(sal))
9 FROM emp
10 WHERE deptno = d.deptno
11 ) sal
12 FROM dept d
13 WHERE d.loc = 'CHICAGO'
14 ) a
15 ;

DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL

--
--
--
--
--
30 SALES 1566.66667 950 2850

Execution Plan

--
Plan hash value: 3370201435

--

IdOperationNameRowsBytesCost (%CPU)Time
--		0SELECT STATEMENT1163 (0)00:00:011SORT AGGREGATE17
2TABLE ACCESS BY INDEX ROWIDEMP5352 (0)00:00:01
  • 3
INDEX RANGE SCANIDX_EMP_DEPTNO51 (0)00:00:01
  • 4
TABLE ACCESS FULLDEPT1163 (0)00:00:01
--

Predicate Information (identified by operation id):

---

3 - access("DEPTNO"=:B1)
4 - filter("D"."LOC"='CHICAGO')

Statistics

--
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
711 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

|
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2016년 상반기 - 오라클 데이터베이스 스터디
  4. 02. 조인 원리와 활용 (06. 스칼라 서브쿼리를 이용한 조인 ~ 08. 고급 조인 테크닉)
  5. 06. 스칼라 서브쿼리를 이용한 조인~