서브쿼리 조인

서브쿼리는 SQL 안에 쓰인 또 하나의 쿼리 블록이다. 옵티마이저는 쿼리 블록 단위로 최적화하므로, 서브쿼리를 어떻게 처리하느냐(필터로 둘지, 조인으로 풀지)가 성능을 크게 좌우한다. 이 문서는 서브쿼리와 뷰가 조인으로 바뀌는 원리와 이를 제어하는 힌트를 다룬다. 쿼리 변환 전반은 쿼리 변환에서 정리한다.

서브쿼리의 종류

문법 차원의 서브쿼리는 서브쿼리에서 다룬다.

필터 오퍼레이션과 캐싱

서브쿼리를 조인으로 풀지 않으면(Unnesting 하지 않으면) 메인쿼리 한 건마다 서브쿼리를 실행하는 필터(FILTER) 오퍼레이션으로 처리된다.

SELECT C.고객번호, C.고객명
FROM   고객 C
WHERE  C.가입일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM')
AND    EXISTS (SELECT /*+ NO_UNNEST */ 'X'
               FROM   거래 T
               WHERE  T.고객번호 = C.고객번호
               AND    T.거래일시 >= TRUNC(SYSDATE, 'MM'));

---------------------------------------------------------
| Id | Operation                     | Name             |
---------------------------------------------------------
|  0 | SELECT STATEMENT              |                  |
|  1 |  FILTER                       |                  |
|  2 |   TABLE ACCESS BY INDEX ROWID | 고객              |
|  3 |    INDEX RANGE SCAN           | 고객_X01          |
|  4 |   INDEX RANGE SCAN            | 거래_X01          |
---------------------------------------------------------

필터 오퍼레이션은 NL 조인과 처리 루틴이 거의 같지만 세 가지가 다르다.

시험 포인트: 필터 오퍼레이션은 "조인 순서 고정 + 첫 건에서 멈춤 + 캐싱"이 NL 조인과의 차이점이다. 캐싱은 서브쿼리 입력 값의 종류가 적을수록 효과가 크다.

서브쿼리 Unnesting

Unnesting은 중첩된 서브쿼리를 풀어 메인쿼리와 같은 레벨의 조인으로 바꾸는 쿼리 변환이다. 풀고 나면 옵티마이저가 조인 순서와 방식(NL·해시·소트 머지)을 자유롭게 고를 수 있다.

IN 서브쿼리의 Unnesting

메인쿼리와 서브쿼리는 1:M 관계일 수 있으므로, 그냥 조인하면 결과 건수가 달라진다. 옵티마이저는 다음 두 방법 중 하나를 쓴다.

SELECT *
FROM   DEPT D
WHERE  D.DEPTNO IN (SELECT E.DEPTNO FROM EMP E WHERE E.SAL >= 3000);

-- (1) 서브쿼리 집합을 먼저 읽고 중복 제거 후 메인 조인
NESTED LOOPS
  VIEW                         VW_NSO_1
    HASH UNIQUE   (또는 SORT UNIQUE)
      TABLE ACCESS FULL        EMP
  TABLE ACCESS BY INDEX ROWID  DEPT
    INDEX UNIQUE SCAN          PK_DEPT

-- (2) 메인쿼리를 먼저 읽으며 세미 조인
HASH JOIN SEMI
  TABLE ACCESS FULL  DEPT
  TABLE ACCESS FULL  EMP

세미 조인과 안티 조인

Unnesting 된 EXISTS·IN 서브쿼리는 세미 조인(Semi Join)으로, NOT EXISTS·NOT IN 서브쿼리는 안티 조인(Anti Join)으로 처리된다. 세미 조인은 메인 한 건당 서브쿼리에서 첫 매칭을 찾으면 멈추고, 안티 조인은 매칭이 하나도 없을 때만 결과로 내보낸다.

-- 사원이 없는 부서: 안티 조인
SELECT D.DEPTNO, D.DNAME
FROM   DEPT D
WHERE  NOT EXISTS (SELECT /*+ UNNEST HASH_AJ */ 'X'
                   FROM   EMP E
                   WHERE  E.DEPTNO = D.DEPTNO);

HASH JOIN ANTI
  TABLE ACCESS FULL  DEPT
  TABLE ACCESS FULL  EMP

서브쿼리 Pushing

Unnesting 되지 않은 서브쿼리는 기본적으로 실행계획 맨 마지막 단계에서 필터링된다. 조인을 다 끝낸 뒤에야 걸러지므로, 서브쿼리 필터링 효과가 크다면 불필요한 조인을 많이 하게 된다.

서브쿼리 Pushing은 서브쿼리 필터링을 가능한 한 앞 단계로 끌어올려 먼저 처리하게 하는 기능이다.

SELECT /*+ LEADING(P) USE_NL(T) */
       COUNT(DISTINCT P.상품번호), SUM(T.주문금액)
FROM   상품 P, 주문 T
WHERE  P.상품번호 = T.상품번호
AND    T.주문일시 >= TRUNC(SYSDATE - 7)
AND    EXISTS (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 'X'
               FROM   상품분류 C
               WHERE  C.상품분류코드 = P.상품분류코드
               AND    C.상위분류코드 = 'AK');

뷰 Merging

인라인 뷰나 뷰도 하나의 쿼리 블록이다. 뷰 Merging은 뷰를 풀어 메인쿼리와 하나의 쿼리 블록으로 합치는 변환으로, 합치고 나면 옵티마이저가 더 다양한 액세스 경로와 조인 순서를 고려할 수 있다.

-- 변환 전
SELECT *
FROM  (SELECT * FROM EMP WHERE JOB = 'SALESMAN') A,
      (SELECT * FROM DEPT WHERE LOC = 'CHICAGO') B
WHERE  A.DEPTNO = B.DEPTNO;

-- Merging 후
SELECT *
FROM   EMP A, DEPT B
WHERE  A.DEPTNO = B.DEPTNO
AND    A.JOB = 'SALESMAN'
AND    B.LOC = 'CHICAGO';

시험 포인트: 복합 뷰를 Merging 하면 GROUP BY를 조인 이후로 미루게 되어, 조인 결과가 많을 때 오히려 집계 대상이 늘어 느려질 수 있다. 뷰 안 조건이 매우 선택적이면 NO_MERGE로 뷰를 먼저 처리하는 편이 낫다.

조건절 Pushing

뷰를 Merging 하지 못할 때, 메인쿼리의 조건절을 뷰 안으로 밀어 넣어 뷰 안에서 미리 데이터를 걸러내는 변환이다.

-- GROUP BY 뷰라 Merging 불가 → DEPTNO = 30 조건을 뷰 안으로 Pushdown
SELECT D.DNAME, E.AVG_SAL
FROM   DEPT D,
      (SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO) E
WHERE  E.DEPTNO = D.DEPTNO
AND    D.DEPTNO = 30;

조건 전이(Transitive Predicate)로 E.DEPTNO = 30이 생기고, 그 조건이 뷰 안으로 들어가 30번 부서만 집계한다.

조인 조건 Pushdown

SELECT /*+ LEADING(D) USE_NL(E) */ D.DNAME, E.AVG_SAL
FROM   DEPT D,
      (SELECT /*+ NO_MERGE PUSH_PRED */ DEPTNO, AVG(SAL) AVG_SAL
       FROM   EMP GROUP BY DEPTNO) E
WHERE  E.DEPTNO = D.DEPTNO
AND    D.LOC = 'CHICAGO';

NESTED LOOPS
  TABLE ACCESS FULL            DEPT
  VIEW PUSHED PREDICATE        
    SORT GROUP BY
      TABLE ACCESS BY INDEX ROWID  EMP
        INDEX RANGE SCAN           EMP_DEPTNO_IDX

• DEPT에서 읽은 DEPTNO를 뷰 안으로 건네 해당 부서만 집계하므로, EMP 전체를 GROUP BY 할 필요가 없다.
• 실행계획에 VIEW PUSHED PREDICATE가 보이면 조인 조건 Pushdown이 작동한 것이다. PUSH_PRED / NO_PUSH_PRED 힌트로 제어한다(GROUP BY 뷰에 대한 적용은 11g 이상).

스칼라 서브쿼리와 캐싱

스칼라 서브쿼리는 메인쿼리 한 건마다 실행되는 함수처럼 동작하며, 결과가 없으면 NULL을 반환한다. 즉 Outer 조인 + NL 조인처럼 동작한다.

SELECT E.EMPNO, E.ENAME, E.SAL,
       (SELECT D.DNAME FROM DEPT D WHERE D.DEPTNO = E.DEPTNO) AS DNAME
FROM   EMP E
WHERE  E.SAL >= 2000;

TABLE ACCESS BY INDEX ROWID  DEPT        -- 스칼라 서브쿼리가 위에 표시
  INDEX UNIQUE SCAN          PK_DEPT
TABLE ACCESS FULL            EMP          -- 메인쿼리

스칼라 서브쿼리 주의점

• 스칼라 서브쿼리는 한 행·한 컬럼만 반환해야 한다. 두 건 이상이면 ORA-01427 오류가 발생한다.
• 여러 컬럼이 필요하다고 같은 스칼라 서브쿼리를 여러 번 쓰면 그만큼 반복 실행된다. 문자열로 결합해 한 번에 가져온 뒤 SUBSTR로 분리하거나, 조인으로 바꾸는 것을 검토한다.

스칼라 서브쿼리를 조인으로 변환

대량 데이터를 처리할 때 스칼라 서브쿼리는 NL 방식으로 고정되어 해시 조인을 쓸 수 없다. 12c부터는 스칼라 서브쿼리도 Unnesting 할 수 있어, 옵티마이저가 Outer 조인으로 변환한 뒤 해시 조인 등을 선택할 수 있다.

-- 스칼라 서브쿼리 Unnesting 유도 (12c~)
SELECT C.고객번호, C.고객명,
       (SELECT /*+ UNNEST */ ROUND(AVG(T.거래금액), 2)
        FROM   거래 T
        WHERE  T.고객번호 = C.고객번호
        AND    T.거래일시 >= TRUNC(SYSDATE, 'MM')) AS 평균거래금액
FROM   고객 C
WHERE  C.가입일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM');

HASH JOIN OUTER
  TABLE ACCESS FULL   고객
  VIEW                VW_SSQ_1
    HASH GROUP BY
      TABLE ACCESS FULL  거래

• UNNEST: 스칼라 서브쿼리를 조인으로 풀도록 유도한다. NO_UNNEST: 스칼라 서브쿼리 그대로 실행(캐싱 활용)하도록 한다.
• 수동으로 바꿀 때는 결과가 없는 행도 남도록 Outer 조인으로 써야 결과가 같다.

시험 포인트: 스칼라 서브쿼리 → 조인 변환 문제에서 Inner 조인으로 바꾸면 매칭이 없는 메인 행이 사라져 결과가 달라진다. 반드시 Outer 조인으로 바꾼다.

핵심 요약

• Unnesting 되지 않은 서브쿼리는 FILTER 오퍼레이션으로 처리되며, NL 조인과 달리 첫 건에서 멈추고, 결과를 캐싱하고, 메인쿼리가 항상 드라이빙이다.
• UNNEST는 서브쿼리를 조인으로 풀고, NO_UNNEST는 필터 방식으로 유지한다. 서브쿼리 쪽이 1쪽임이 보장되지 않으면 SORT/HASH UNIQUE 또는 세미 조인이 쓰인다.
• EXISTS·IN은 세미 조인(NL_SJ·HASH_SJ·MERGE_SJ), NOT EXISTS·NOT IN은 안티 조인(NL_AJ·HASH_AJ·MERGE_AJ)으로 처리된다.
• PUSH_SUBQ는 Unnesting 되지 않은 서브쿼리 필터링을 앞 단계로 당기며, NO_UNNEST와 함께 쓴다.
• 뷰 Merging(MERGE/NO_MERGE)은 뷰를 메인쿼리와 합친다. 집합 연산자·CONNECT BY·ROWNUM·집계 함수(GROUP BY 없음)·분석 함수가 있으면 불가하다.
• 조건절 Pushdown·Pullup·조인 조건 Pushdown(PUSH_PRED, VIEW PUSHED PREDICATE)으로 뷰 안에서 미리 데이터를 걸러낸다.
• 스칼라 서브쿼리는 입력·출력 값을 캐싱하므로 입력 값 종류가 적을 때 유리하고, 많거나 메인 집합이 작으면 손해다.
• 12c부터 스칼라 서브쿼리도 Unnesting 가능하며, 수동 변환 시 반드시 Outer 조인을 쓴다.