서브쿼리 변환 - Unnesting·필터·세미/안티 조인

이 장에서 배우는 것

온라인 서점의 출고 대기 화면에는 배송 보류 고객의 주문을 제외한 건수가 표시된다. 조회 결과는 숫자 하나지만, 주문마다 보류 고객 목록을 확인한다면 화면을 여는 비용은 작지 않을 수 있다. 이 장에서는 이 조회를 대상으로 서브쿼리를 반복 실행하는 계획과 조인으로 변환한 계획을 비교한다.

앞 장에서 해시 조인의 입력 크기를 살펴봤다면, 이번에는 서브쿼리가 어떤 조건에서 조인 후보가 되는지 살펴본다. SQL의 모양만 바꾸는 데 그치지 않고 결과의 의미, 실제 실행 횟수, 논리 읽기를 함께 확인한다.

• 서브쿼리 펼치기(Unnesting)의 적용 가능성과 실제 선택을 구분한다.
• 필터 방식의 캐싱이 반복 실행 비용을 줄이는 조건을 설명한다.
• 세미 조인(Semi Join)과 안티 조인(Anti Join)의 결과 보존 규칙을 이해한다.
• NOT EXISTS와 NOT IN의 NULL 차이를 데이터로 확인한다.
• 실제 실행계획의 Starts, A-Rows, Buffers로 변환 효과를 판단한다.

문제 상황

운영팀은 출고 대기 화면의 건수 표시가 주문 증가에 따라 느려졌다고 보고했다. 업무 규칙은 “출고 대기 주문 중 배송 보류 고객의 주문을 제외한다”다. 보류 고객을 등록하면서 고객 번호를 아직 연결하지 못한 행도 있어, 보류 목록의 고객 번호에는 NULL이 들어갈 수 있다.

재현 데이터는 출고 대기 주문 120,000건과 고객 6,000명으로 구성한다. 고객마다 주문이 20건씩 있다. 고객 번호가 5의 배수인 1,200명을 보류 목록에 넣고, 고객 번호가 NULL인 행을 하나 추가한다. 따라서 제외할 주문은 24,000건이고 화면에 표시할 건수는 96,000건이다.

기존 SQL에는 이전 운영 대응 과정에서 추가한 NO_UNNEST 힌트가 남아 있다. 이 힌트는 해당 서브쿼리를 펼치지 않도록 요청한다. 재현 코드에서도 이를 사용해 필터 방식의 비교 출발점을 만든다. 모든 NOT EXISTS가 기본적으로 필터 방식으로 실행된다는 뜻은 아니다.

SELECT COUNT(*)
FROM sq5_orders o
WHERE NOT EXISTS (
    SELECT /*+ NO_UNNEST */ 1
    FROM sq5_hold_customers b
    WHERE b.customer_id = o.customer_id
);

보류 고객 번호에는 인덱스가 있다. 그래도 인덱스를 여러 번 탐색하는 비용은 남는다. 이번 변경의 목적은 인덱스 추가가 아니라 반복 확인을 조인 처리로 바꿀 수 있는지 검증하는 데 있다. 화면이 요구하는 값이 건수이므로 COUNT(*)를 그대로 측정한다. 목록을 반환하는 SQL을 임의로 COUNT(*)로 감싸 측정하는 실험과는 다르다.

서브쿼리가 조인으로 바뀌는 조건

서브쿼리를 펼친다는 것은 별도 쿼리 블록에 있던 테이블과 조건을 조인 최적화 대상에 포함하는 변환이다. 변환 후에도 원래 SQL의 결과가 보존되어야 한다. 옵티마이저가 변환할 수 있다는 사실과, 변환된 후보를 실제 실행계획으로 선택한다는 사실은 구분해야 한다.

이번 SQL은 보류 목록과 주문을 고객 번호의 동등 조건으로 연결한다. 서브쿼리에 집계, 행 수 제한, 집합 연산이 없으므로 안티 조인으로 표현하기 쉬운 형태다. 주문 한 건에 대해 일치하는 보류 행이 없으면 그 주문을 남긴다. 일치하는 보류 행이 여러 건이어도 주문을 여러 번 출력하지 않는다.

반대로 EXISTS는 일치하는 행이 하나라도 있으면 바깥 행을 남기는 세미 조인 후보가 된다. 일반 내부 조인으로 바꾸면 내부의 중복 행 수만큼 바깥 행이 늘어날 수 있다. 따라서 “EXISTS를 JOIN으로 고치면 된다”는 문장만으로는 결과 보존을 설명할 수 없다.

존재 여부를 묻는 조건과 조인의 결과 규칙

표현	남기는 바깥 행	내부 중복의 영향	계획에서 찾을 표시
EXISTS	일치하는 내부 행이 있는 행	바깥 행을 증식시키지 않는다	SEMI 또는 FILTER
NOT EXISTS	일치하는 내부 행이 없는 행	바깥 행을 증식시키지 않는다	ANTI 또는 FILTER
내부 조인	일치하는 행의 조합	일치 건수만큼 늘어날 수 있다	일반 조인 연산

변환을 검토할 때는 먼저 상관 조건을 찾는다. 상관 조건은 안쪽 블록이 바깥 블록의 값을 참조하는 조건이다. 이 조건을 조인 조건으로 옮겼을 때 의미가 유지되는지 확인한다. 그다음 집계의 단위, NULL 처리, 행 수 제한이 결과를 달라지게 하는지 확인한다.

예를 들어 서브쿼리에 ROWNUM을 넣으면 변환에 제약이 생길 수 있다. 상관 집계, 집합 연산, 바로 바깥 블록보다 더 먼 블록을 참조하는 조건도 단순한 존재 검사와 같은 규칙으로 판단할 수 없다. 다만 집계가 보인다는 이유만으로 모든 변환이 불가능하다고 결론 내리지 않는다. 구체적인 형태와 적용 가능한 변환이 다르기 때문이다.

UNNEST 힌트는 변환을 요청하는 수단이다. 의미 보존 검사를 무시하는 명령도 아니고, 해시 조인을 지정하는 명령도 아니다. 변환 뒤에는 통계와 비용에 따라 중첩 루프, 해시, 병합 방식 중 하나가 선택될 수 있다. 따라서 힌트가 있다는 사실보다 실제 계획의 ANTI 또는 SEMI 표시를 확인하는 것이 우선이다.

그림의 아래쪽은 해시 안티 조인이 선택된 경우다. 보류 목록으로 해시 구조를 만들고 주문의 고객 번호를 대조한다. 안티 조인 자체가 해시 방식만을 뜻하지는 않는다. 바깥 입력이 적고 내부 인덱스 탐색이 저렴하면 중첩 루프 안티 조인도 합리적인 선택이다.

필터 실행과 캐싱을 읽는 방법

필터 방식이라고 해서 반드시 바깥 행 수만큼 내부를 실행하는 것은 아니다. Oracle은 이 사례처럼 상관 값을 받는 필터 서브쿼리에서 이전에 계산한 결과를 재사용할 수 있다. 같은 고객 번호가 다시 들어오면 보류 여부를 다시 탐색하지 않고 저장된 결과를 활용하는 방식이다.

이 캐싱은 실행 사이에 공유하는 SQL 결과 캐시와 다르다. 또한 고객별 결과를 제한 없이 모두 보관한다고 가정해서도 안 된다. 캐시 충돌, 입력 값의 종류와 순서, 실제 계획에 따라 내부 실행 횟수가 달라진다. 특정 캐시 크기를 튜닝의 전제로 삼거나, 고객 수가 6,000명이므로 내부 실행도 정확히 6,000번이라고 계산하면 안 된다.

재현 데이터는 주문 번호가 증가하면서 고객 번호가 1부터 6,000까지 반복되도록 구성한다. 같은 고객의 주문을 연속 배치한 데이터와는 재사용 양상이 달라질 수 있다. 다만 테이블을 읽는 순서는 SQL의 결과 순서 보장이 아니다. 캐싱을 개선하려는 목적으로 정렬을 추가하면 별도 비용이 생기고 계획도 바뀌므로, 이번 실험에서는 정렬을 추가하지 않는다.

실제 실행계획의 Starts는 해당 행 소스가 시작된 횟수다. 내부 인덱스 행의 Starts가 바깥 입력 행 수보다 작다면 캐싱 등의 영향을 검토할 근거가 된다. 그러나 작은 Starts만으로 캐싱의 존재를 확정하지는 않는다. 앞선 조건에서 행이 제거되거나 다른 변환이 적용된 경우도 있으므로 조건 정보와 부모 연산을 함께 읽는다.

A-Rows는 실제로 출력한 누적 행 수다. EXISTS 계열은 일치 여부를 판단하면 추가 탐색을 멈출 수 있다. 그러므로 내부의 A-Rows를 Starts로 나눈 값이 해당 고객의 전체 보류 이력 건수라고 해석해서는 안 된다. Buffers는 해당 실행에서 발생한 논리적 버퍼 접근량을 비교하는 데 사용한다. 상위 연산 값에는 하위 작업이 반영될 수 있으므로 계획의 모든 행을 더하지 않는다.

이번 비교에서는 최상위 집계 연산의 Buffers를 두 실행의 대표값으로 사용한다. 내부 인덱스의 Starts는 비용이 발생한 이유를 설명하는 보조 지표다. 숫자가 줄어든 사실과 왜 줄었는지를 별도로 확인해야 다른 데이터 분포에도 적용할 수 있다.

NOT EXISTS와 NOT IN의 NULL 의미

NOT EXISTS는 조건을 만족하는 행의 존재 여부를 검사한다. 보류 고객 번호가 NULL이면 주문의 고객 번호와 비교한 결과가 참이 아니므로 일치하는 행으로 인정하지 않는다. 반면 NOT IN은 내부 값들과의 비교에 NULL이 끼어들면 결과가 미정이 될 수 있다. WHERE 절은 참인 행만 통과시키므로 미정인 행도 제거한다.

예를 들어 보류 목록이 5와 NULL을 담고 있다고 하자. 고객 7은 NOT EXISTS를 통과한다. 그러나 7 NOT IN (5, NULL)은 참이 아니라 미정이다. 고객 5는 두 조건 모두 통과하지 못한다. NULL이 있으므로 NOT IN의 모든 비교 결과가 미정이라는 설명도 정확하지 않다. 실제 일치 값이 있으면 거짓으로 결정되는 경우가 있다.

보류 목록이 5와 NULL일 때의 조건 결과

주문 고객 번호	NOT EXISTS	NOT IN	WHERE 통과 여부
5	거짓	거짓	둘 다 제외
7	참	미정	NOT EXISTS만 통과
NULL	참	미정	NOT EXISTS만 통과

표의 마지막 행은 개념 비교용이다. 완성 코드의 주문 고객 번호에는 NOT NULL 제약이 있어 그 행이 발생하지 않는다. 이 제약 덕분에 내부 NULL을 제거한 NOT IN은 이번 NOT EXISTS와 같은 결과를 낸다. 바깥 고객 번호도 NULL을 허용한다면 내부 NULL만 제거하는 것으로 두 표현이 항상 같아지지는 않는다.

또한 내부 집합이 비어 있으면 NOT IN은 바깥 값이 NULL이어도 참이 된다. NULL 문제를 “바깥 값이 NULL이면 언제나 제외된다”는 규칙으로 외우기보다 비교 대상 집합까지 확인해야 한다.

Oracle 19c는 필요한 경우 NULL을 고려하는 안티 조인을 사용한다. 계획에 ANTI NA나 ANTI SNA가 나타날 수 있다. 이는 NOT IN을 업무적으로 원하는 의미로 고쳐 준다는 뜻이 아니다. 원래 SQL의 NULL 의미를 유지하면서 실행하는 방법이다. 내부 NULL이 있는 NOT IN의 결과가 0건이라면 빠르게 실행되더라도 이번 업무 규칙에는 맞지 않는다.

Oracle 19c와 MySQL 8.0에서 확인 방법이 다른 부분

항목	Oracle 19c	MySQL 8.0	실무 판단
존재 조건 변환	세미·안티 조인으로 변환할 수 있다	8.0.16부터 EXISTS의 세미 조인 변환 범위가 확대되고, 8.0.17부터 적격 부정 조건의 안티 조인 변환을 지원한다	8.0의 세부 버전까지 확인한다
변환 제어	UNNEST와 NO_UNNEST를 사용한다	세미 조인 관련 힌트와 optimizer_switch 등 제어 체계가 다르다	Oracle 힌트를 그대로 이식하지 않는다
필터 결과 재사용	상관 필터 실행에서 캐싱이 작용할 수 있다	동일한 상관 키 캐싱을 전제로 비용을 계산하지 않는다	각 엔진의 실제 반복 횟수를 확인한다
실제 실행 통계	DBMS_XPLAN으로 Starts, A-Rows, Buffers를 확인한다	8.0.18부터 EXPLAIN ANALYZE로 시간, 행 수, 반복 횟수 등을 확인한다	MySQL 출력에 Oracle의 Buffers가 있다고 가정하지 않는다
NULL과 부정 조건	NULL 의미를 보존하는 안티 조인을 사용할 수 있다	비교식의 NULL 허용 여부가 안티 조인 변환을 제한할 수 있다	SQL 의미와 변환 가능성을 따로 검증한다

변환의 세부 제약은 Oracle 19c 쿼리 변환 문서, 실행 통계의 해석은 DBMS_XPLAN 문서, MySQL의 적용 조건은 MySQL 8.0 세미·안티 조인 문서에서 확인할 수 있다. 여기의 데이터, SQL, 도식은 이 사례를 위해 구성했다.

완성 코드

다음 파일은 Oracle 19c에 연결하는 SQL*Plus용 SQL·PL/SQL 스크립트다. macOS 또는 Linux에는 SQL*Plus 클라이언트가 필요하고, 데이터베이스는 별도 서버에 있어도 된다. 익명 PL/SQL 블록을 사용하므로 별도의 프로그램 빌드 과정은 없다.

실습 계정에는 테이블 생성 권한과 저장 공간, DBMS_STATS 및 DBMS_XPLAN 실행 권한이 필요하다. DISPLAY_CURSOR에 필요한 V_$SQL, V_$SQL_PLAN, V_$SESSION, V_$SQL_PLAN_STATISTICS_ALL 조회 권한도 준비한다. 스크립트는 자신의 스키마에 있는 sq5_orders와 sq5_hold_customers를 삭제하고 다시 만든다. 같은 이름의 업무 테이블이 없는 실습 스키마에서 실행한다.

실제 통계는 작업 디렉터리의 sq5_report.txt에 저장한다. 콘솔에는 성공 문구만 출력한다. 이 구분은 가변적인 실행계획 숫자를 고정된 예상 출력처럼 제시하지 않기 위한 것이다. SQL 오류는 종료 코드에 반영되므로 보고서와 함께 확인한다.

sq5_subquery.sql

-- 01. Configure SQL*Plus and capture the report.
WHENEVER OSERROR EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING ON
SET PAGESIZE 50000
SET LINESIZE 220
SET TRIMSPOOL ON
SET TAB OFF
SET SERVEROUTPUT OFF
SET AUTOTRACE OFF
SET TIMING OFF
SET TERMOUT OFF
SPOOL sq5_report.txt REPLACE

-- 02. Reset only the two lab tables.
BEGIN
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE sq5_orders PURGE';
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -942 THEN
                RAISE;
            END IF;
    END;

    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE sq5_hold_customers PURGE';
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -942 THEN
                RAISE;
            END IF;
    END;
END;
/

-- 03. Create the schema.
CREATE TABLE sq5_orders (
    order_id    NUMBER(10) NOT NULL,
    customer_id NUMBER(10) NOT NULL,
    CONSTRAINT sq5_orders_pk PRIMARY KEY (order_id)
);

CREATE TABLE sq5_hold_customers (
    hold_id     NUMBER(10) NOT NULL,
    customer_id NUMBER(10),
    CONSTRAINT sq5_hold_customers_pk PRIMARY KEY (hold_id)
);

CREATE INDEX sq5_hold_customer_ix
    ON sq5_hold_customers (customer_id);

-- 04. Load deterministic data.
INSERT INTO sq5_orders (order_id, customer_id)
SELECT LEVEL, MOD(LEVEL - 1, 6000) + 1
FROM dual
CONNECT BY LEVEL <= 120000;

INSERT INTO sq5_hold_customers (hold_id, customer_id)
SELECT LEVEL, LEVEL * 5
FROM dual
CONNECT BY LEVEL <= 1200;

INSERT INTO sq5_hold_customers (hold_id, customer_id)
VALUES (1201, NULL);

COMMIT;

-- 05. Gather statistics.
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => USER,
        tabname          => 'SQ5_ORDERS',
        estimate_percent => 100,
        method_opt       => 'FOR ALL COLUMNS SIZE 1',
        cascade          => TRUE
    );

    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => USER,
        tabname          => 'SQ5_HOLD_CUSTOMERS',
        estimate_percent => 100,
        method_opt       => 'FOR ALL COLUMNS SIZE 1',
        cascade          => TRUE
    );
END;
/

-- 06. Check semantics before measuring.
DECLARE
    l_exists_count NUMBER;
    l_raw_in_count NUMBER;
    l_safe_in_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO l_exists_count
    FROM sq5_orders o
    WHERE NOT EXISTS (
        SELECT 1
        FROM sq5_hold_customers b
        WHERE b.customer_id = o.customer_id
    );

    SELECT COUNT(*)
    INTO l_raw_in_count
    FROM sq5_orders o
    WHERE o.customer_id NOT IN (
        SELECT b.customer_id
        FROM sq5_hold_customers b
    );

    SELECT COUNT(*)
    INTO l_safe_in_count
    FROM sq5_orders o
    WHERE o.customer_id NOT IN (
        SELECT b.customer_id
        FROM sq5_hold_customers b
        WHERE b.customer_id IS NOT NULL
    );

    IF l_exists_count != 96000
       OR l_raw_in_count != 0
       OR l_safe_in_count != 96000 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Unexpected semantic result');
    END IF;
END;
/

-- 07. Execute the filter candidate and read its cursor immediately.
PROMPT BEFORE: NO_UNNEST
SELECT /*+ GATHER_PLAN_STATISTICS NO_PARALLEL */ COUNT(*) AS eligible_orders
FROM sq5_orders o
WHERE NOT EXISTS (
    SELECT /*+ NO_UNNEST */ 1
    FROM sq5_hold_customers b
    WHERE b.customer_id = o.customer_id
);

SELECT plan_table_output
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        NULL,
        NULL,
        'ALLSTATS LAST +PREDICATE +ALIAS'
    )
);

-- 08. Execute the unnest candidate and read its cursor immediately.
PROMPT AFTER: UNNEST
SELECT /*+ GATHER_PLAN_STATISTICS NO_PARALLEL */ COUNT(*) AS eligible_orders
FROM sq5_orders o
WHERE NOT EXISTS (
    SELECT /*+ UNNEST */ 1
    FROM sq5_hold_customers b
    WHERE b.customer_id = o.customer_id
);

SELECT plan_table_output
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        NULL,
        NULL,
        'ALLSTATS LAST +PREDICATE +ALIAS'
    )
);

-- 09. Close the report and print stable console output.
SPOOL OFF
SET TERMOUT ON
PROMPT semantic_check=PASS
PROMPT expected_eligible_orders=96000
PROMPT report=sq5_report.txt
EXIT SUCCESS

줄별 해설

01은 오류 발생 시 실패 코드로 종료하고 보고서를 연다. TERMOUT OFF는 파일로 실행하는 SQL의 화면 출력을 억제하지만 스풀 기록은 유지한다. SERVEROUTPUT OFF와 AUTOTRACE OFF는 측정 SQL과 DISPLAY_CURSOR 사이에 클라이언트가 추가 작업을 끼워 넣는 상황을 줄인다. 이 코드는 SQL*Plus에서 파일로 실행하는 조건을 기준으로 한다.

02는 재실행을 위해 실습 테이블을 제거한다. 테이블이 없다는 오류만 무시하고 나머지는 다시 발생시킨다. DDL은 트랜잭션 롤백으로 복원되지 않으므로 오류 종료에 ROLLBACK이 있더라도 앞서 삭제한 테이블이 돌아오지는 않는다.

03은 주문 고객 번호에 NOT NULL을 선언한다. 이는 업무 의미와 변환 판단에 모두 필요한 정보다. 보류 고객 번호는 NULL을 허용하고 일반 인덱스를 둔다. Oracle의 단일 컬럼 B-tree 인덱스에는 키 전체가 NULL인 행이 저장되지 않는다. 고객 번호의 동등 조건을 찾는 이번 인덱스 탐색에는 문제가 없지만, 이 인덱스만 보고 테이블에 NULL이 없다고 판단해서는 안 된다.

04는 주문 120,000건과 보류 행 1,201건을 넣는다. MOD 식으로 각 고객에게 주문을 20건씩 배정한다. 보류 고객 번호를 5의 배수로 생성하므로 결과 건수를 별도 데이터 파일 없이 계산할 수 있다.

05는 두 테이블과 관련 인덱스의 통계를 수집한다. 표본 오차를 줄이고 히스토그램을 만들지 않아 실험 조건을 단순화한다. 그래도 데이터베이스의 패치 수준, 블록 크기, 시스템 통계, 세션 설정까지 같아지는 것은 아니다.

06은 세 가지 표현의 결과를 검증한다. NOT EXISTS는 96,000건, 원래 NOT IN은 0건, 내부 NULL을 제거한 NOT IN은 96,000건이어야 한다. 이 데이터 검증은 의미 차이를 보여 주는 장치이며, 모든 입력에 대한 동치 증명은 아니다. 동치 판단에는 앞서 확인한 NOT NULL 제약도 필요하다.

07과 08은 같은 SQL에 서로 다른 변환 힌트를 적용한다. GATHER_PLAN_STATISTICS로 실행 통계를 수집하고, 직후에 마지막 커서의 통계를 읽는다. NO_PARALLEL은 병렬 실행의 영향을 줄이기 위한 실험 설정이다. COUNT(*) 결과가 반환될 때 전체 집계가 끝나므로 일부 행만 가져온 상태를 측정하는 문제도 피한다.

09의 성공 문구는 의미 검증과 스크립트 실행 완료를 뜻한다. 특정 조인 방식이 선택되었거나 성능이 개선되었다는 보증은 아니다. DISPLAY_CURSOR가 권한이나 커서 조회 문제를 안내하는 문자열을 반환할 수도 있으므로 보고서에 실제 행 소스 통계가 있는지 확인한다.

실행 결과

파일을 저장한 디렉터리에서 다음과 같이 실행한다. BOOKLAB과 접속 문자열은 실습 환경에 맞게 바꾼다. 비밀번호는 명령행에 쓰지 않고 SQL*Plus의 입력 요청에 응답한다.

sqlplus -s -L BOOKLAB@//localhost:1521/FREEPDB1 @sq5_subquery.sql

비밀번호 입력 이후 정상 종료 시 스크립트가 콘솔에 출력하는 내용은 다음과 같다.

semantic_check=PASS
expected_eligible_orders=96000
report=sq5_report.txt

실행계획 보고서는 다음 명령으로 읽는다. 두 조회의 ELIGIBLE_ORDERS 값은 모두 96000이어야 한다. 계획 번호, 비용, 시간, 논리 읽기는 환경에 따라 달라지므로 고정된 예상 출력으로 제시하지 않는다.

cat sq5_report.txt

보고서의 BEFORE 구간에서는 FILTER와 내부 인덱스 탐색을 찾는다. AFTER 구간에서는 HASH JOIN ANTI, HASH JOIN RIGHT ANTI 또는 다른 안티 조인 연산을 찾는다. RIGHT 표시는 계획의 입력 배치와 관련되므로 테이블 별칭까지 확인한다. AFTER에도 FILTER가 남아 있다면 변환 요청이 적용되지 않은 이유부터 조사한다.

다음 표와 계획은 읽는 방법을 설명하기 위한 가정 수치다. 실제 Oracle에서 수집한 결과나 스크립트가 출력할 숫자를 주장하는 자료가 아니다. 실제 비교표는 sq5_report.txt의 값으로 채워야 한다. 가정에서는 주문 읽기에 400, 반복 인덱스 탐색에 200,000, 보류 목록 일괄 읽기에 8의 논리 읽기가 들었다고 놓는다.

설명용 BEFORE 요약
Operation                    Starts    A-Rows    Buffers
SORT AGGREGATE                    1         1     200400
  FILTER                          1     96000     200400
    TABLE ACCESS FULL O           1    120000        400
    INDEX RANGE SCAN B        100000     20000     200000

설명용 AFTER 요약
Operation                    Starts    A-Rows    Buffers
SORT AGGREGATE                    1         1        408
  HASH JOIN ANTI                  1     96000        408
    TABLE ACCESS FULL O           1    120000        400
    TABLE ACCESS FULL B           1      1201          8

가정 수치로 살펴본 전후 비교와 실제 확인 위치

비교 항목	변경 전 가정	변경 후 가정	실제 확인 위치
결과 건수	96,000	96,000	각 SELECT 결과
처리 형태	FILTER	HASH JOIN ANTI	Operation
보류 목록 접근 Starts	100,000	1	별칭 B의 접근 연산
전체 논리 읽기	200,400	408	최상위 집계의 Buffers

이 가정에서는 논리 읽기가 약 99.8% 줄어든다. 원인은 주문 읽기 감소가 아니라 반복 인덱스 탐색의 제거다. 변경 전 내부 Starts가 120,000보다 작다는 점은 일부 반복 확인이 생략된 상황을 표현한다. 캐싱으로 감소한 비용이 있더라도 일괄 처리와의 차이가 남을 수 있다는 뜻이다.

실제 측정에서 비슷한 차이가 나와야 성공이라고 판단하지 않는다. 필터 캐싱이 잘 작동하면 변경 전 비용이 이미 작을 수 있고, 작은 입력에서는 중첩 루프 안티 조인이 선택될 수 있다. 보고서에 A-Rows나 Buffers가 없다면 추정치로 대체하지 말고 통계 수집과 커서 조회부터 바로잡는다.

시간까지 비교하려면 데이터 생성 부분을 제외하고 07과 08의 조회·계획 출력 쌍을 같은 세션에서 여러 차례 실행한다. 실행 순서도 바꾸어 본다. 앞선 의미 검증이 이미 데이터를 읽었으므로 이 스크립트를 차가운 캐시의 첫 실행 실험으로 해석하지 않는다. 각 SQL은 통계를 출력하기 전에 다른 SQL을 끼워 넣지 않는다.

실무에서 자주 틀리는 것

NOT EXISTS를 NULL 검토 없이 NOT IN으로 바꾼다

다음 변경은 보류 고객 번호에 NULL이 하나만 있어도 이번 데이터의 결과를 0건으로 만든다.

SELECT COUNT(*)
FROM sq5_orders o
WHERE o.customer_id NOT IN (
    SELECT b.customer_id
    FROM sq5_hold_customers b
);

업무 규칙을 직접 표현하는 NOT EXISTS를 유지한다. 내부 NULL을 제거한 NOT IN을 선택하려면 바깥 값의 NULL 가능성까지 함께 검토한다.

SELECT COUNT(*)
FROM sq5_orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM sq5_hold_customers b
    WHERE b.customer_id = o.customer_id
);

EXISTS를 내부 조인으로 바꾸면서 중복을 무시한다

보류 대상 주문의 건수를 조회한다고 하자. 다음 내부 조인은 현재 실습 데이터에서는 맞지만, 고객별 보류 사유가 여러 행으로 등록되면 건수를 부풀린다. 고객 번호 인덱스는 유일 인덱스가 아니다.

SELECT COUNT(*)
FROM sq5_orders o
JOIN sq5_hold_customers b
  ON b.customer_id = o.customer_id;

필요한 것은 보류 행의 개수가 아니라 존재 여부다. EXISTS로 표현하면 내부 중복과 관계없이 주문당 한 번만 센다.

SELECT COUNT(*)
FROM sq5_orders o
WHERE EXISTS (
    SELECT 1
    FROM sq5_hold_customers b
    WHERE b.customer_id = o.customer_id
);

존재 검사에 불필요한 ROWNUM을 넣는다

다음 코드는 한 행만 찾으면 되므로 더 빠를 것이라는 의도로 작성되기 쉽다. 하지만 EXISTS 자체가 존재 여부를 묻고 있으며, ROWNUM 조건은 서브쿼리 변환에 제약을 줄 수 있다.

SELECT COUNT(*)
FROM sq5_orders o
WHERE EXISTS (
    SELECT 1
    FROM sq5_hold_customers b
    WHERE b.customer_id = o.customer_id
      AND ROWNUM = 1
);

이 사례에서 불필요한 행 수 조건을 제거하고 실제 계획을 비교한다. 업무적으로 필요한 상위 행 선택까지 같은 이유로 삭제해서는 안 된다.

SELECT COUNT(*)
FROM sq5_orders o
WHERE EXISTS (
    SELECT 1
    FROM sq5_hold_customers b
    WHERE b.customer_id = o.customer_id
);

EXPLAIN PLAN의 비용만으로 효과를 판정한다

다음 명령은 SQL을 실행하지 않는다. 따라서 필터 내부가 몇 번 시작되었는지, 논리 읽기가 얼마였는지 알려 주지 못한다.

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM sq5_orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM sq5_hold_customers b
    WHERE b.customer_id = o.customer_id
);

SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY());

실행 통계를 수집한 뒤 해당 커서의 마지막 실행을 읽는다. 이 명령 쌍도 SQL*Plus의 SERVEROUTPUT과 AUTOTRACE를 끈 상태에서 연속 실행한다.

SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*)
FROM sq5_orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM sq5_hold_customers b
    WHERE b.customer_id = o.customer_id
);

SELECT plan_table_output
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')
);

한눈에 보기

서브쿼리 튜닝에서 판단해야 할 것

질문	확인 대상	판단 기준
결과가 같은가	NULL, 중복, 빈 집합	속도 비교보다 먼저 업무 의미를 검증한다
변환할 수 있는가	상관 조건과 결과 제한 요소	의미를 보존하는 조인 표현이 가능한지 살핀다
변환되었는가	실제 커서의 Operation	힌트 존재가 아니라 SEMI·ANTI 등을 확인한다
반복 확인이 많은가	내부 Starts와 바깥 A-Rows	캐싱과 다른 필터의 영향을 함께 읽는다
비용이 줄었는가	같은 위치의 Buffers	계획의 모든 Buffers를 합산하지 않는다
운영에도 유리한가	입력 크기와 키 분포	작은 입력과 반복 키가 많은 조건도 확인한다

이번 사례의 변경 후보는 과거의 NO_UNNEST 제약을 제거하는 것이다. 실험의 UNNEST는 변환 후보를 확인하기 위해 사용했다. 운영 반영 전에는 힌트를 모두 제거한 SQL도 측정하여 원하는 계획이 자연스럽게 선택되는지 확인한다. 다음 장에서는 서브쿼리의 존재 검사에서 범위를 넓혀, 쿼리 블록의 경계를 바꾸는 변환을 살펴본다.

연습 문제

1. 실습 데이터에서 고객 번호가 NULL인 보류 행만 삭제했다. NOT EXISTS와 원래 NOT IN의 결과 건수는 각각 얼마인가. 그 이유를 설명하라.
2. 필터 계획에서 주문 입력은 120,000건이고 내부 인덱스의 Starts는 8,000이었다. 내부 실행이 고객 수인 6,000과 다르다는 이유로 캐싱이 없다고 결론 내릴 수 있는가.
3. 고객 5의 보류 행을 하나 더 추가했다. 보류 대상 주문을 세는 EXISTS와 일반 내부 조인의 결과는 각각 어떻게 달라지는가.
4. 변경 전 최상위 집계의 Buffers가 48,000이고 변경 후 600이다. 감소율을 계산하고, 이 값만으로 응답 시간도 같은 비율로 감소한다고 말할 수 없는 이유를 두 가지 제시하라.

정답과 해설

1. 둘 다 96,000건이다. 내부 목록에서 NULL이 사라지고 바깥 고객 번호에는 NOT NULL 제약이 있으므로 두 조건은 같은 고객을 제외한다. 고객 번호가 5의 배수인 주문 24,000건은 계속 제외된다.
2. 그렇게 결론 내릴 수 없다. 캐싱은 서로 다른 키마다 한 번의 실행을 보장하는 장치가 아니다. 충돌과 입력 순서 등에 따라 같은 키를 다시 실행할 수 있다. 내부 Starts가 줄어든 원인을 판단하려면 실제 FILTER 구조와 다른 조건의 영향을 함께 확인해야 한다.
3. EXISTS의 결과는 24,000건으로 유지된다. 일반 내부 조인은 고객 5의 주문 20건이 한 번씩 더 결합되어 24,020건이 된다. 이 차이는 존재 검사와 행 조합 생성의 차이에서 발생한다.
4. 감소율은 (48,000 - 600) / 48,000 × 100으로 98.75%다. 논리 읽기는 CPU 연산이나 해시 처리 비용 전체를 나타내지 않는다. 또한 물리 읽기, 동시 실행 부하, 메모리 부족에 따른 임시 영역 사용 등이 응답 시간에 영향을 준다. 같은 부하 조건에서 시간과 실행 통계를 함께 비교해야 한다.