서브쿼리란

• WHERE에 사용되는 SELECT쿼리
• 일반적으로 절차적 SQL이므로 작성이 쉬움
• SQL 작성이 쉽다는 이유 하나만으로 서브쿼리를 남용 할 경우, DB 성능 저하가 발생할 수 있음
  SQL에 서브쿼리가 여러 개 존재할 경우 OPTIMIZER가 COST 값을 잘 못 계산하고 부적절한 실행계획으로 실행 될 수 있음
• 서브쿼리를 이용할 경우 성능이 좋아지는 SQL도 존재 하지만 조인으로 작성하게 되면 OPTIMIZER에게 정확한 판단을 할 수 있도록 도와
  성능 문제를 예방 할 수 있음
• 서브쿼리가 어떻게 동작하는지 잘 숙지하여 서브쿼리를 이용하여야 함. (무조건 조인이 좋다는건 아님)

서브쿼리의 사용 패턴에 대해 알아 보자

사용패턴[1]

SELECT *
  FROM emp
 WHERE sal > (SELECT avg(sal)
                FROM emp)

• 위와 같은 서브쿼리의 결과는 반드시 1이어야 함. 한건 이상일 경우 에러 발생
• 위의 패턴은 서브쿼리 부터 먼저 수행된 후, Main SQL의 컬럼 값과 비교하는 형태로 수행되는 것이 일반적

사용패턴[2]

SELECT c1, c2, c3
  FROM SUBQUERY_T2 t2
 WHERE c2 = 'A'
   AND EXISTS (SELECT /*+ NO_UNNEST */ 'X'
                 FROM SUBQYER_T1 tw
                WHERE t1.c5 = t2.c2)

• EXISTS나 IN연산자(또는 NOT EXISTS, NOT IN)를 사용한 경우로 서브쿼리의 결과가 여러건 추출 될 수 있음
• 서브쿼리 내 Main SQL과 연결조건인 T1.C5 = T2.C2가 존재(EXISTS, NOT EXTS의 경우)하여, Main SQL에서 추출한 값을 상속받아 값이 존재하는지 체크하는 방식.
  반대로 서브쿼리가 먼저 수행된 Main SQL에 값을 전달할 수도 있음.
  
  
  
  
• 서브쿼리에서 추출되는 데이터가 중복 값이 많더라도, Main SQL의 데이터를 증가시키지 않음

테스트[1]. 서브쿼리의 기본적인 특성 알아보기

SELECT /*+ QB_NQME(B)*/col1
  FROM (SELECT LEVEL col1
          FROM DUAL CONNECT BY LEVEL<=3) a
 WHERE a.col1 IN (SELECT /*+ QB_NAME(A) */col1
          FROM (SELECT LEVEL col1
                  FROM DUAL CONNECT BY LEVEL <=3
                UNION ALL
                SELECT LEVEL col1
                  FROM DUAL CONNECT BY LEVEL <=3
                UNION ALL
                SELECT LEVEL col1
                  FROM DUAL CONNECT BY LEVEL <=3 ) )
---------------------------------------------------------------------------------------------  
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
---------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                 |          |     1 |    26 |     9  (12)| 00:00:01 |  
|*  1 |  HASH JOIN SEMI                  |          |     1 |    26 |     9  (12)| 00:00:01 |  
|   2 |   VIEW                           |          |     1 |    13 |     2   (0)| 00:00:01 |  
|*  3 |    CONNECT BY WITHOUT FILTERING  |          |       |       |            |          |  
|   4 |     FAST DUAL                    |          |     1 |       |     2   (0)| 00:00:01 |  
|   5 |   VIEW                           | VW_NSO_1 |     3 |    39 |     6   (0)| 00:00:01 |  
|   6 |    VIEW                          |          |     3 |    39 |     6   (0)| 00:00:01 | 
|   7 |     UNION-ALL                    |          |       |       |            |          | 
|*  8 |      CONNECT BY WITHOUT FILTERING|          |       |       |            |          | 
|   9 |       FAST DUAL                  |          |     1 |       |     2   (0)| 00:00:01 | 
|* 10 |      CONNECT BY WITHOUT FILTERING|          |       |       |            |          | 
|  11 |       FAST DUAL                  |          |     1 |       |     2   (0)| 00:00:01 | 
|* 12 |      CONNECT BY WITHOUT FILTERING|          |       |       |            |          | 
|  13 |       FAST DUAL                  |          |     1 |       |     2   (0)| 00:00:01 | 
---------------------------------------------------------------------------------------------  

*QUERY BLOCK A의 결과 값

*QUERY BLOCK B의 결과 값

• 위의 조건을 보면 9건이 추출(서브쿼리가 9건 이므로)될꺼라고 생각하나 결과는 3건만 추출됨 --> 서브쿼리에서는 데이터가 UNIQUE 처리되므로 3건만 처리.
• 위의 이유로 조인을 서브쿼리로 변경시 유의해야 함.

테스트[2]. 서브쿼리를 조인 (뷰)으로 수행되도록 유도

SELECT /*+ ORDERED QB_NQME(B)*/col1
  FROM (SELECT LEVEL col1
          FROM DUAL CONNECT BY LEVEL<=3) a
 WHERE a.col1 IN (SELECT /*+ QB_NAME(A) */col1
          FROM (SELECT LEVEL col1
                  FROM DUAL CONNECT BY LEVEL <=3
                UNION ALL
                SELECT LEVEL col1
                  FROM DUAL CONNECT BY LEVEL <=3
                UNION ALL
                SELECT LEVEL col1
                  FROM DUAL CONNECT BY LEVEL <=3 ) ) ;
 
----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |     1 |    26 |    10  (20)| 00:00:01 |
|*  1 |  HASH JOIN                        |          |     1 |    26 |    10  (20)| 00:00:01 |
|   2 |   VIEW                            | VW_NSO_1 |     3 |    39 |     6   (0)| 00:00:01 |
|   3 |    HASH UNIQUE                    |          |     1 |    39 |            |          |
|   4 |     VIEW                          |          |     3 |    39 |     6   (0)| 00:00:01 |
|   5 |      UNION-ALL                    |          |       |       |            |          |
|*  6 |       CONNECT BY WITHOUT FILTERING|          |       |       |            |          |
|   7 |        FAST DUAL                  |          |     1 |       |     2   (0)| 00:00:01 |
|*  8 |       CONNECT BY WITHOUT FILTERING|          |       |       |            |          |
|   9 |        FAST DUAL                  |          |     1 |       |     2   (0)| 00:00:01 |
|* 10 |       CONNECT BY WITHOUT FILTERING|          |       |       |            |          |
|  11 |        FAST DUAL                  |          |     1 |       |     2   (0)| 00:00:01 |
|  12 |   VIEW                            |          |     1 |    13 |     2   (0)| 00:00:01 |
|* 13 |    CONNECT BY WITHOUT FILTERING   |          |       |       |            |          |
|  14 |     FAST DUAL                     |          |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------- 

• ID 2를 확인하면 VIEW라는 오퍼레이션이 존재 하여 뷰로 변경되었음
• ID 3번의 HASH UNIQUE 오퍼레이션을 통해 중복값을 제거