by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

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




I. 스칼라 서브쿼리

1. 스칼라 서브쿼리란?

  • 스칼라 서브쿼리란 Select-List에서 서브쿼리가 사용될 때 이를 스칼라 서브쿼리라 칭함.
  • 스칼라 서브쿼리의 특징은 다음과 같음.
    • 하나의 레코드만 리턴이 가능하며, 두개 이상의 레코드는 리턴할 수 없다.
    • 일치하는 데이터가 없더라도 NULL값을 리턴할 수 있다.
      이는 원래 그룹함수의 특징중에 하나인데 스칼라 서브쿼리 또한 이 특징을 가지고 있다.
      
      -- 1. 그룹함수 사용 안할 때는 리턴하는 값이 없음
      SELECT 1
      FROM   DEPT
      WHERE  1 = 2
      ;
      
      no rows selected
      
      -- 2. 그룹함수를 사용하면 값을 리턴함
      SELECT MIN(DEPTNO)
      FROM   DEPT
      WHERE  1 = 2
      ;
      
      MIN(DEPTNO)     
      -----------     
                      
      
      1 row selected.
      
      -- 3. 스칼라 서브쿼리 테스트(일치하는 값이 없을 경우 NULL 리턴함)
      -- EMP:DEPT = M:1 관계에서 M 대상을 올리다보니 GROUP BY 을 추가함
      SELECT D.DEPTNO,
             (SELECT MIN(EMPNO) 
              FROM   EMP
              WHERE  DEPTNO = D.DEPTNO) EMPNO
      FROM   DEPT D
      ORDER BY D.DEPTNO
      ;
      
          DEPTNO      EMPNO
      ---------- ----------
              10          1
              20         10
              30        100
              40       1000
              50      10000
              60     100000
              70           
              80           
              90           
      
      9 rows selected.
      

2. 스칼라 서브쿼리와 Outer 조인 관계

  • 위와 같이 스칼라 서브쿼리는 일치하지 않더라도 NULL를 리턴하므로 Outer 조인과 같은 형태이며,
    이는 스칼라 서브쿼리와 Outer 조인은 서로 변형이 가능하다는 것을 알 수 있다.
    SELECT D.DEPTNO,
           E.EMPNO
    FROM   DEPT D,
           (SELECT DEPTNO, 
                   MIN(EMPNO) EMPNO
            FROM   EMP
            GROUP BY DEPTNO) E
    WHERE  D.DEPTNO = E.DEPTNO(+)
    ORDER BY D.DEPTNO
    ;     
    
        DEPTNO      EMPNO   
    ---------- ----------   
            10          1   
            20         10   
            30        100   
            40       1000   
            50      10000   
            60     100000   
            70              
            80              
            90              
    
    9 rows selected.
    

3. 성능상 Outer 조인을 스칼라 서브쿼리로 올려야 할 때

  • 만약 Outer 조인을 하는 집합이 인라인뷰이면서 그룹함수를 사용해서 View Merging이 안될 때
    
    -- 1. 2개의 그룹함수 인라인뷰와 Outer 조인이 되어 View Merging이 안된다.
    SELECT T_1.SUM_EMPNO SUM_EMPNO_1,
           T_2.SUM_EMPNO SUM_EMPNO_2,
           E.HIREDATE,
           E.DEPTNO
    FROM   (SELECT E_1.EMPNO,
                   SUM(E_1.EMPNO) SUM_EMPNO
            FROM   EMP E_1
            GROUP BY E_1.EMPNO) T_1,
           (SELECT E_2.EMPNO,
                   SUM(E_2.EMPNO) SUM_EMPNO
            FROM   EMP E_2
            GROUP BY E_2.EMPNO) T_2, 
           EMP E
    WHERE  E.EMPNO = T_1.EMPNO(+)
    AND    E.EMPNO = T_2.EMPNO(+)
    AND    E.DEPTNO = 10
    ;
    
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.000        0.006          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch        2    0.610        0.607          1        811          0          9
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total        4    0.610        0.612          1        811          0          9
    
    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
          9   HASH JOIN OUTER (cr=811 pr=1 pw=0 time=493640 us)
          9    HASH JOIN OUTER (cr=407 pr=1 pw=0 time=203353 us)
          9     TABLE ACCESS BY INDEX ROWID EMP (cr=3 pr=1 pw=0 time=11482 us)
          9      INDEX RANGE SCAN EMP_N1 (cr=2 pr=1 pw=0 time=11433 us)(Object ID 19837982)
     100000     VIEW  (cr=404 pr=0 pw=0 time=289825 us)
     100000      HASH GROUP BY (cr=404 pr=0 pw=0 time=289818 us)
     100000       TABLE ACCESS FULL EMP (cr=404 pr=0 pw=0 time=115 us)
     100000    VIEW  (cr=404 pr=0 pw=0 time=274989 us)
     100000     HASH GROUP BY (cr=404 pr=0 pw=0 time=174985 us)
     100000      TABLE ACCESS FULL EMP (cr=404 pr=0 pw=0 time=115 us)
    ;
    
    -- 2. 이를 극복하기 위해 스칼라 서브쿼리로 올림
    SELECT (SELECT SUM(E_1.EMPNO) SUM_EMPNO
            FROM   EMP E_1
            WHERE  E_1.EMPNO = E.EMPNO) SUM_EMPNO_1,
           (SELECT SUM(E_2.EMPNO) SUM_EMPNO
            FROM   EMP E_2
            WHERE  E_2.EMPNO = E.EMPNO) SUM_EMPNO_2,
           E.HIREDATE,
           E.DEPTNO
    FROM   EMP E
    WHERE  E.DEPTNO = 10
    ;
    
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.010        0.004          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch        2    0.000        0.066          2         27          0          9
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total        4    0.010        0.070          2         27          0          9
    
    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
          9   SORT AGGREGATE (cr=11 pr=1 pw=0 time=50528 us)
          9    INDEX UNIQUE SCAN EMP_U1 (cr=11 pr=1 pw=0 time=50440 us)(Object ID 19837981)
          9   SORT AGGREGATE (cr=11 pr=0 pw=0 time=152 us)
          9    INDEX UNIQUE SCAN EMP_U1 (cr=11 pr=0 pw=0 time=102 us)(Object ID 19837981)
          9   TABLE ACCESS BY INDEX ROWID EMP (cr=5 pr=1 pw=0 time=15032 us)
          9    INDEX RANGE SCAN EMP_N1 (cr=3 pr=1 pw=0 time=15172 us)(Object ID 19837982)
    

4. 성능상 스칼라 서브쿼리를 Outer 조인으로 내려야 할 때

  • 만약 뷰 안에 스칼라 서브쿼리 컬럼이 존재하고 그 뷰 밖에서 서브쿼리 컬럼을 조건으로 사용할 경우.
    이 때는 인덱스가 있다 하더라도 컬럼이 가공되어 있기 때문에 View Merging이 안되므로 이 때는
    스칼라 서브쿼리를 Outer Join으로 내려야 함
    
    -- 1. 인라인뷰 안의 스칼라 서브쿼리를 뷰 밖에서 조건으로 사용할 경우
    SELECT EMPNO,
           DEPT_NO
    FROM   (SELECT E.EMPNO,
                   (SELECT D.DEPTNO
                    FROM   DEPT D
                    WHERE  D.DEPTNO = E.DEPTNO) DEPT_NO
            FROM   EMP E)
    WHERE  DEPT_NO = 10      
    ;
    
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.000        0.004          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch        2    0.110        0.119          0        407          0          9
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total        4    0.110        0.123          0        407          0          9
    
    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
          6   INDEX UNIQUE SCAN DEPT_U1 (cr=2 pr=0 pw=0 time=81 us)(Object ID 19837985)
          9   FILTER  (cr=407 pr=0 pw=0 time=186 us)
     100000    TABLE ACCESS FULL EMP (cr=405 pr=0 pw=0 time=100112 us)
          6    INDEX UNIQUE SCAN DEPT_U1 (cr=2 pr=0 pw=0 time=81 us)(Object ID 19837985)
    ;
    
    -- 2. 스칼라 서브쿼리를 Outer 조인으로 내려서 극복
    SELECT EMPNO,
           DEPT_NO
    FROM   (SELECT E.EMPNO,
                   D.DEPTNO DEPT_NO
            FROM   EMP  E,
                   DEPT D
            WHERE  D.DEPTNO = E.DEPTNO(+))
    WHERE  DEPT_NO = 10      
    ;
    
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.010        0.004          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch        2    0.000        0.017          1          6          0          9
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total        4    0.010        0.021          1          6          0          9
    
    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
          9   NESTED LOOPS OUTER (cr=6 pr=1 pw=0 time=16862 us)
          1    INDEX UNIQUE SCAN DEPT_U1 (cr=1 pr=0 pw=0 time=39 us)(Object ID 19837985)
          9    TABLE ACCESS BY INDEX ROWID EMP (cr=5 pr=1 pw=0 time=16815 us)
          9     INDEX RANGE SCAN EMP_N1 (cr=3 pr=1 pw=0 time=16904 us)(Object ID 19837982)
    

5. 스칼라 서브쿼리를 Outer 조인으로 내릴 때 IN절 처리 방법

  • 만약 스칼라 서브쿼리에 IN절이 있을 경우 Outer 구문이 안먹히는데
    이를 극복하기 위해서 DECODE() 구문을 사용하여 해결 가능
    
    -- 1. 스칼라 서브쿼리에 IN절이 있는 경우
    SELECT EMPNO,
           DEPT_NO
    FROM   (SELECT E.EMPNO,
                   (SELECT D.DEPTNO
                    FROM   DEPT D
                    WHERE  D.DEPTNO = E.DEPTNO
                    AND    D.DNAME IN ('SALES_1', 'SALES_2', 'SALES_3')) DEPT_NO
            FROM   EMP E)
    WHERE  DEPT_NO = 10      
    ;
    
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.000        0.004          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch        2    0.110        0.110          0        413          0          9
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total        4    0.110        0.114          0        413          0          9
    
    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
          3   TABLE ACCESS BY INDEX ROWID DEPT (cr=8 pr=0 pw=0 time=187 us)
          6    INDEX UNIQUE SCAN DEPT_U1 (cr=2 pr=0 pw=0 time=77 us)(Object ID 19837985)
          9   FILTER  (cr=413 pr=0 pw=0 time=201 us)
     100000    TABLE ACCESS FULL EMP (cr=405 pr=0 pw=0 time=100107 us)
          3    TABLE ACCESS BY INDEX ROWID DEPT (cr=8 pr=0 pw=0 time=187 us)
          6    INDEX UNIQUE SCAN DEPT_U1 (cr=2 pr=0 pw=0 time=77 us)(Object ID 19837985)
    ;
    
    -- 2. 스칼라 서브쿼리의 IN절을 Outer 조인으로 내리지만 결국 구문 에러남
    SELECT EMPNO,
           DEPT_NO
    FROM   (SELECT E.EMPNO,
                   D.DEPTNO DEPT_NO
            FROM   EMP  E,
                   DEPT D
            WHERE  D.DEPTNO   = E.DEPTNO(+)
            AND    D.DNAME(+) IN ('SALES_1', 'SALES_2', 'SALES_3'))
    WHERE  DEPT_NO = 10      
    ;
    
            AND    D.DNAME(+) IN ('SALES_1', 'SALES_2', 'SALES_3') )
                              *
    ERROR at line 8:
    ORA-01719: outer join operator (+) not allowed in operand of OR or IN
    
    -- 3. DECODE 구문을 이용하여 해결
    SELECT EMPNO,
           DEPT_NO
    FROM   (SELECT E.EMPNO,
                   D.DEPTNO DEPT_NO
            FROM   EMP  E,
                   DEPT D
            WHERE  D.DEPTNO   = E.DEPTNO(+)
            --AND    D.DNAME(+) IN ('SALES_1', 'SALES_2', 'SALES_3')
            AND    DECODE(D.DNAME(+), 'SALES_1', 1,
                                      'SALES_2', 1,
                                      'SALES_3', 1) = 1)
    WHERE  DEPT_NO = 10      
    ;
    
    Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Parse        1    0.000        0.004          0          0          0          0
    Execute      1    0.000        0.000          0          0          0          0
    Fetch        2    0.000        0.010          1          7          0          9
    ------- ------ -------- ------------ ---------- ---------- ---------- ----------
    Total        4    0.000        0.014          1          7          0          9
    
    Misses in library cache during parse: 1
    Optimizer goal: ALL_ROWS
    Parsing user: APPS (ID=44)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  STATEMENT
          9   NESTED LOOPS OUTER (cr=7 pr=1 pw=0 time=10393 us)
          1    TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=70 us)
          1     INDEX UNIQUE SCAN DEPT_U1 (cr=1 pr=0 pw=0 time=29 us)(Object ID 19837985)
          9    TABLE ACCESS BY INDEX ROWID EMP (cr=5 pr=1 pw=0 time=10316 us)
          9     INDEX RANGE SCAN EMP_N1 (cr=3 pr=1 pw=0 time=10390 us)(Object ID 19837982)
    

II. 스칼라 서브쿼리의 캐싱 효과

1. 스칼라 서브쿼리는 입력값이 같을 경우 반복수행 안함

  • 오라클은 스칼라 서브쿼리에 수행횟수를 최소화하려고 그 입력 값과 출력 값을 내부 캐시에 저장함.
  • 입력 값이 같을 경우 출력 출력 값도 같을 것으로 보고 캐싱된 출력 값을 보내주므로 입력 값의
    NDV가 작으면 작을수록 펑션의 호출빈도를 많이 줄일 수 있다.
  • 사용방법은 펑션에 SELECT DUAL을 사용하면 된다.
    SELECT EMPNO,
           ENAME,
           HIREDATE,
           (SELECT GET_DNAME(DEPTNO) FROM DUAL) DNAME
    FROM   EMP E
    WHERE  HIREDATE :1 AND :2
    ;      
    

III. 두 개 이상의 값을 리턴하고 싶을 때

1. 결합연산자를 사용하는 방법

SELECT DEPTNO,
       DNAME,
       TO_NUMBER(SUBSTR(EMPNO, 1, 7))  AVG_EMPNO,
       TO_NUMBER(SUBSTR(EMPNO, 8, 15)) MIN_EMPNO,
       TO_NUMBER(SUBSTR(EMPNO, 15))    MAX_EMPNO
FROM   (SELECT D.DEPTNO,
               D.DNAME,
               (SELECT LPAD(AVG(EMPNO), 7) || LPAD(MIN(EMPNO), 7) || MAX(EMPNO)
                FROM   EMP
                WHERE  DEPTNO = D.DEPTNO) EMPNO
        FROM  DEPT D)
;

    DEPTNO DNAME    AVG_EMPNO  MIN_EMPNO  MAX_EMPNO
---------- ------- ---------- ---------- ----------
        10 SALES_1          5         19          9
        20 SALES_2       54.5       1099         99
        30 SALES_3      549.5     100999        999
        40 SALES_4     5499.5   10009999       9999
        50 SALES_5    54999.5 1000099999      99999
        60 SALES_6     100000 1.0000E+11     100000
        70 SALES_7                                 
        80 SALES_8                                 
        90 SALES_9                                 

9 rows selected.

2. 오브젝트 타입을 사용하는 경우


-- 1. 오브젝트 타입 생성
CREATE OR REPLACE TYPE EMPNO_TYPE AS OBJECT
(AVG_EMPNO NUMBER, MIN_EMPNO NUMBER, MAX_EMPNO NUMBER)
/

Type created.

-- 2. 오브젝트 타입 활용하여 멀티 컬럼 리턴
SELECT DEPTNO,
       DNAME,
       A.EMPNO.AVG_EMPNO,
       A.EMPNO.MIN_EMPNO,
       A.EMPNO.MAX_EMPNO
FROM   (SELECT D.DEPTNO,
               D.DNAME,
               (SELECT EMPNO_TYPE(AVG(EMPNO), MIN(EMPNO), MAX(EMPNO))
                FROM   EMP
                WHERE  DEPTNO = D.DEPTNO) EMPNO
        FROM  DEPT D) A
;

    DEPTNO DNAME   EMPNO.AVG_EMPNO EMPNO.MIN_EMPNO EMPNO.MAX_EMPNO
---------- ------- --------------- --------------- ---------------
        10 SALES_1               5               1               9
        20 SALES_2            54.5              10              99
        30 SALES_3           549.5             100             999
        40 SALES_4          5499.5            1000            9999
        50 SALES_5         54999.5           10000           99999
        60 SALES_6          100000          100000          100000
        70 SALES_7                                                
        80 SALES_8                                                
        90 SALES_9                                                

9 rows selected.

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.