[ SQL ] 서브쿼리에 대한 기본내용 이해하기

이 글은 'SQL 튜닝의 시작' 이란 책을 읽고 내용을 정리하는 글입니다.

---

[ 서브쿼리 기본개념 ]

( 서브쿼리란 ? )

• where 절에 비교조건으로 사용되는 select 쿼리

( 특징 )

• 장점 : SQL 작성시에 모든 테이블을 join으로 작성하는 것보다 메인 집합을 만들고 서브쿼리를 사용하여 값을 비교하는 절차적 SQL 작성이 비교적 간편하다.
• 단점 : 하지만, join으로 처리가 가능한 SQL임에도, 서브쿼리를 남용할 경우 Optimizer가 최적화 과정에서 잘못된 Cost 계산을 하는 경우가 발생하여 DB 서버에 치명적인 성능 문제가 발생할 수 있다.

[ 서브쿼리 사용패턴 ]

ex 1 )

select * 
from emp
where sal > ( select AVG (sal) from emp )

: 추출 결과가 반드시 한 건 이여야 하는 경우 ( 만약 두 건 이상 추출된다면, 에러발생 )

위같은 경우 보통 서브쿼리 먼저 수행 후 Main SQL의 컬럼값과 비교하는 형태로 수행된다.

ex 2 )

select c1, c2, c3
from SUBQUERY_T2 t2
where c2 = 'A'
AND EXISTS (
            select /*+ NO_UNNEST*/
            'x'
            from SUBQUERY_T1 t1
            where t1.c5 = t2.c2
            )

: EXISTS 나 IN 연산자 ( 또는 NOT EXISTS, NOT IN ) 을 사용한 경 우로 서브쿼리의 결과가 여러 건 추출될 수 있다.

※ 실제 서브쿼리 사용시 성능 문제를 발생시키는 대부분의 유형은 (ex2) 형태이다.

• 그러므로 위와 같은 형태의 SQL 을 작성시에 서브쿼리가 사용 의도에 맞게 수행되는지 실행계획을 반드시 확인해야 한다.

( 서브쿼리의 기본적인 특성 )

TEST 1 )

select /*+ QB_NAME(B)*/ coll
from (
    select level coll
        from DUAL
    connect by level <= 3
    ) a
where a.coll IN (
                select /*+ QB_NAME(A)*/ coll -- 1,2,3 각각 3개씩 출력
                    from(
                select level coll
                    from DUAL
                connect by level <= 3
                        union all
                select level
                    from DUAL
                connect by level <= 3
                        union all
                select level
                    from DUAL
                connect by level <= 3
                )
    );

: 서브쿼리만 수행할 시 결과값

COL1 - 1 2 3 1 2 3 1 2 3

: 전체 SQL 수행할 시 결과값

COL1 - 1 2 3

• 서브쿼리에서 추출되는 데이터가 중복 값이 많더라도 Unique 값만 처리하므로, 서브쿼리를 조인으로 변경하는 SQL 작성시 서브쿼리의 중복된 데이터는 제거된다는 특성을 SQL에 반드시 반영해야 한다.

( 서브쿼리를 조인 (뷰) 으로 수행되도록 유도 )

TESE 2 )

select /*+ ORDERED QB_NAME(B)*/ coll
    from (
                select level coll
                from DUAL
                connect by level <= 3
                ) a
    where a.coll IN (
                    select /*+ QB_NAME(A)*/ coll -- 1,2,3 각각 3개씩 출력
                        from (
                    select level coll
                        from DUAL
                    connect by level <= 3
                            union all
                    select level
                        from DUAL
                    connect by level <= 3
                            union all                                                
                    select level
                        from DUAL
                    connect by level <= 3
                    )
)

: 실행계획을 살펴보게되면, VIEW 라는 오퍼레이션이 존재하여 서브쿼리가 뷰로 변경되었으며,

HASH UNIQUE 오퍼레이션을 통해 서브쿼리를 뷰로 변경시 내부적으로 중복값을 제거 (DISTINICT 처리) 하였음을 알 수 있다. [ Oracle 기준 ]