꿈꾸는 개발자, DBA 커뮤니티 구루비

서브쿼리란

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의 결과 값

COL1
1
2
3
1
2
3
1
2
3

QUERY BLOCK B의 결과 값

COL1
1
2
3

위의 조건을 보면 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 오퍼레이션을 통해 중복값을 제거