NL 조인 튜닝 - 드라이빙 테이블과 조인 순서

이 장에서 배우는 것

온라인 서점의 일별 결제 주문 화면이 느려졌다. 화면에 표시할 주문은 많지 않은데 실행계획에는 고객 전체를 읽는 작업이 먼저 나타난다. 고객마다 주문을 찾은 뒤 날짜를 검사하므로, 최종 결과에 포함되지 않는 주문까지 반복해서 방문한다. 이 사례에서는 중첩 루프 조인(Nested Loops Join)의 조인 순서와 내부 테이블 인덱스를 함께 조정한다.

앞 장에서 테이블 랜덤 액세스의 비용을 살펴봤다. 이번에는 그 액세스가 조인 안에서 몇 번 반복되는지까지 범위를 넓힌다. 인덱스를 사용한다는 사실만으로는 충분하지 않다. 어떤 집합이 반복을 시작하고, 각 반복에서 어느 범위까지 읽는지를 확인해야 한다.

• 드라이빙 테이블의 전체 크기와 조건 적용 후 행 수를 구분한다.
• 실행계획의 Starts, A-Rows, Buffers로 반복 탐색 비용을 해석한다.
• LEADING과 ORDERED로 조인 순서를 지정하는 방법을 구분한다.
• 조인 조건과 필터 조건을 함께 고려해 내부 테이블 인덱스를 설계한다.
• 동일한 결과를 유지하면서 변경 단계별 논리 읽기를 비교한다.

문제 상황

운영자는 매일 전날의 결제 완료 주문을 조회한다. 화면은 주문 정보와 고객 이름을 함께 표시하며, 같은 조건으로 주문 수와 결제 금액 합계를 계산한다. 고객은 10,000명이고 주문은 200,000건이다. 주문은 100일에 걸쳐 하루 2,000건씩 쌓였으며, 하루에 결제 완료 상태인 주문은 1,600건이다.

기존 SQL은 고객을 먼저 읽고 고객별 주문을 찾는다. 주문 테이블에는 고객 번호 단일 인덱스가 있다. 고객 한 명당 주문이 20건이므로 고객 10,000명을 따라가면 날짜 조건을 확인하기 위해 주문 200,000건을 방문한다. 원하는 날짜의 결제 완료 주문은 그중 1,600건뿐이다.

이 순서가 모든 업무에서 나쁜 것은 아니다. 고객 한 명의 주문 이력을 조회한다면 고객부터 시작하는 것이 자연스럽다. 그러나 이번 화면에는 고객을 줄이는 조건이 없다. 반면 주문 날짜와 상태는 주문을 전체의 0.8%로 줄인다. 기존 순서는 화면의 검색 조건과 맞지 않는다.

실험에서는 화면 결과를 모두 소비하도록 집계 SQL을 사용한다. 주문 수, 금액 합계, 고객 이름 길이 합계를 계산하면 클라이언트가 첫 화면만 가져온 채 실행을 멈추는 문제를 피할 수 있다. 고객 이름과 주문 금액을 실제로 참조하므로 두 테이블의 데이터 접근도 관찰할 수 있다.

드라이빙 집합이 반복 횟수를 결정한다

NL 조인의 외부 입력에서 행 하나가 나오면 내부 입력을 탐색한다. 드라이빙 집합은 이 반복을 시작하는 입력이다. 단순한 두 테이블 조인에서는 먼저 접근하는 테이블을 드라이빙 테이블이라고 부른다. 여러 테이블이 연결되면 앞선 조인의 결과가 다음 조인의 외부 입력이 될 수도 있다.

비용을 생각할 때는 외부 입력을 만드는 비용에 외부 행 수와 내부 탐색 비용의 곱을 더한다. 이는 실행시간을 정확히 계산하는 공식이 아니라 병목을 나누는 기준이다. 외부 입력을 줄이는 변경과 내부 탐색 한 번을 가볍게 만드는 변경은 서로 다른 효과를 낸다.

여기서 작은 테이블부터 읽는다는 규칙은 충분하지 않다. 고객 테이블은 주문 테이블보다 작지만 고객 조건이 없으므로 10,000행이 남는다. 주문은 조건을 적용하면 1,600행만 남는다. 비교 대상은 테이블의 저장 행 수가 아니라 다음 탐색을 시작하는 시점의 실제 행 수다.

실행계획의 Starts는 해당 연산이 시작된 횟수이고, A-Rows는 실제로 반환한 행 수다. 반복되는 내부 연산의 A-Rows는 대개 모든 시작에 걸친 누적값으로 읽는다. 내부 인덱스의 Starts가 10,000이고 A-Rows가 200,000이라면 한 번의 탐색에서 평균 20행이 나왔다는 뜻이다. 최종 조인 결과가 1,600행이어도 그 전에 큰 후보 집합을 거쳤을 수 있다.

Buffers는 논리 읽기를 판단하는 지표다. 하위 연산의 읽기가 상위 연산에 포함될 수 있으므로 계획의 모든 Buffers를 더하면 중복 계산할 수 있다. 문장 전체 비교에는 최상위 집계 연산 등 동일한 위치의 값을 사용하고, 하위 값은 비용이 발생한 경로를 찾는 데 사용한다. A-Rows와 Starts도 계획의 실제 부모·자식 관계를 함께 확인해야 한다.

조인 순서는 힌트로 검증하고 통계로 설명한다

Oracle에서 LEADING은 지정한 별칭을 기준으로 조인의 선행 순서를 지시한다. 두 테이블 사례의 LEADING(c o)는 고객부터, LEADING(o c)는 주문부터 시작하도록 요청한다. USE_NL은 조인 방식에 관한 힌트다. USE_NL(c)는 고객을 내부 입력으로 하는 NL 조인을 요청하며, 고객을 먼저 읽으라는 뜻이 아니다.

SELECT /*+ LEADING(o c) USE_NL(c) */
       o.order_id, c.customer_name
FROM nl3_orders o
JOIN nl3_customers c
  ON c.customer_id = o.customer_id
WHERE o.order_status = 'P'
  AND o.order_date >= DATE '2025-04-10'
  AND o.order_date <  DATE '2025-04-11';

ORDERED는 FROM 절에 나열한 순서에 따라 조인하도록 요청한다. 위와 같이 주문 별칭이 먼저 오는 단순한 내부 조인에서는 ORDERED와 USE_NL(c)를 조합해 같은 의도를 표현할 수 있다. LEADING은 의도한 순서를 별칭으로 드러내므로 FROM 절을 편집했을 때의 영향을 비교하기 쉽다. 두 순서 힌트를 한 문장에 중복해서 사용하지 않는다.

힌트는 실행 결과를 확인해야 하는 요청이다. 별칭이 틀리거나, 쿼리 블록이 다르거나, 외부 조인의 의미상 허용되지 않는 순서를 지정하면 기대한 계획이 나오지 않을 수 있다. 이 사례는 변환의 영향을 줄인 단순한 내부 조인으로 구성한다. 실제 업무에서는 SQL을 읽고 순서를 추정하는 데서 멈추지 말고 실행계획으로 적용 여부를 확인한다.

힌트로 좋은 순서를 찾았더라도 원래 계획이 나온 이유를 조사한다. 조건 적용 후 예상 행 수가 실제보다 크게 어긋났는지, 통계가 오래됐는지, 필요한 접근 경로가 없는지를 확인한다. 날짜별 주문량이 크게 다른 업무에서는 특정 날짜에 좋았던 고정 순서가 다른 날짜에도 적합한지 추가로 측정해야 한다.

Oracle 19c와 MySQL 8.0에서 조인 순서를 검증하는 수단

항목	Oracle 19c	MySQL 8.0
순서 지정	LEADING으로 별칭 순서를 지정하고 ORDERED로 FROM 순서를 따른다.	8.0.18 이상에서 JOIN_ORDER와 JOIN_FIXED_ORDER 등을 사용한다. STRAIGHT_JOIN도 순서를 제한하는 수단이다.
NL 방식 지정	USE_NL로 지정한 테이블을 내부 입력으로 요청한다.	USE_NL을 그대로 옮길 수 없다. 순서와 인덱스 접근을 지정한 뒤 실제 조인 방식을 확인한다.
실제 실행 확인	GATHER_PLAN_STATISTICS와 DBMS_XPLAN으로 행 수와 Buffers를 확인한다.	8.0.18 이상에서 EXPLAIN ANALYZE로 실제 행 수, 반복, 시간을 확인한다.
읽기 수치 비교	실제 커서 계획의 Buffers를 동일 기준으로 비교한다.	Oracle Buffers에 해당하는 값을 같은 계획 형식으로 제공하지 않는다. Handler 계열 카운터를 블록 논리 읽기로 해석하지 않는다.

MySQL의 EXPLAIN ANALYZE는 반복 연산의 행 수 표시를 Oracle A-Rows와 같은 누적값으로 간주하면 안 된다. 표시된 반복 횟수와 행 수의 의미를 해당 버전 기준으로 확인한다. 두 제품의 수치를 같은 이름으로 바꿔 직접 비교하기보다 각 제품 안에서 동일한 측정 방법을 유지한다. 사실 확인에는 Oracle SQL 힌트 참조와 MySQL 옵티마이저 힌트 참조를 이용할 수 있다.

내부 인덱스는 탐색 범위와 방문 행 수를 줄인다

기존 주문 인덱스가 고객 번호만 포함하면 내부 탐색은 해당 고객의 주문 20건을 찾는다. 날짜와 상태가 테이블에만 있으므로 주문 행을 방문한 뒤 조건을 검사한다. 인덱스를 사용하지만 반복당 방문량이 많다.

고객을 먼저 읽는 순서를 유지하면서 주문 인덱스를 고객 번호, 주문 상태, 주문 날짜 순으로 구성해 보자. 고객 번호는 조인에서 전달되는 동등 조건이고 상태도 동등 조건이다. 날짜 범위는 그다음에 이어진다. 이 순서에서는 고객별 결제 완료 주문 중 원하는 날짜 구간을 탐색할 수 있다. 결과가 없는 고객도 탐색을 시작하지만 과거 주문을 테이블에서 일일이 검사하는 비용은 줄어든다.

이 인덱스는 주문 금액을 포함하지 않는다. 따라서 조건을 통과한 주문의 금액을 얻으려면 테이블 접근이 남는다. 이번 변경의 목적은 모든 테이블 접근을 없애는 것이 아니라 불필요한 후보 행 접근을 줄이는 데 있다.

이후 순서를 뒤집으면 내부 테이블은 고객으로 바뀐다. 고객 번호 기본키 인덱스는 주문 한 건이 전달한 고객 번호로 고객 한 행을 찾는 데 적합하다. 주문에는 상태, 날짜 순서의 인덱스를 사용해 외부 입력 1,600행을 만든다. 이때 고객 번호 선두 인덱스는 전체 고객의 특정 날짜 주문을 한 구간으로 찾는 접근 경로가 아니다.

따라서 조인 조건 컬럼에 인덱스를 만들라는 조언은 방향을 포함해야 한다. 고객에서 주문으로 들어갈 때는 주문의 고객 번호가 중요하고, 주문에서 고객으로 들어갈 때는 고객의 고객 번호가 중요하다. 양쪽에 같은 이름의 컬럼이 있다는 사실만으로 두 접근의 비용이 같아지지는 않는다.

완성 코드

다음은 Oracle 19c의 실습용 스키마에서 실행하는 SQL*Plus 스크립트다. macOS와 Linux에서 SQL*Plus 클라이언트로 실행할 수 있으며 데이터베이스는 별도 서버에 있어도 된다. 테이블 생성 권한과 테이블스페이스 할당량, 자신의 테이블 통계 수집 권한, DBMS_XPLAN의 DISPLAY_CURSOR에 필요한 동적 성능 뷰 조회 권한이 준비되어 있어야 한다.

스크립트는 NL3_ 접두사의 실습 테이블 두 개를 삭제하고 다시 만든다. 전용 실습 스키마에서 실행한다. 저장 프로그램을 생성하지 않으며 익명 PL/SQL 블록과 SQL로 구성한다. SQL 또는 운영체제 오류가 발생하면 실패 상태로 종료한다. 실행계획은 현재 작업 디렉터리의 nl3_report.txt에 저장한다.

nl_join_case.sql

-- 01. 실행 환경과 오류 처리
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE 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 TIMING OFF
SET AUTOTRACE OFF
SET SERVEROUTPUT OFF
SET TERMOUT OFF

ALTER SESSION SET optimizer_adaptive_plans = FALSE;

-- 02. 실습 객체 초기화
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE nl3_orders PURGE';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END;
/

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

-- 03. 테이블 생성
CREATE TABLE nl3_customers (
  customer_id   NUMBER(10) NOT NULL,
  customer_name VARCHAR2(40 CHAR) NOT NULL,
  CONSTRAINT nl3_c_pk PRIMARY KEY (customer_id)
);

CREATE TABLE nl3_orders (
  order_id      NUMBER(10) NOT NULL,
  customer_id   NUMBER(10) NOT NULL,
  order_date    DATE NOT NULL,
  order_status  CHAR(1) NOT NULL,
  amount        NUMBER(12) NOT NULL,
  CONSTRAINT nl3_o_pk PRIMARY KEY (order_id)
);

-- 04. 고객 10,000명, 주문 200,000건 생성
INSERT INTO nl3_customers (customer_id, customer_name)
SELECT LEVEL, '회원' || TO_CHAR(LEVEL, 'FM00000')
FROM dual
CONNECT BY LEVEL <= 10000;

INSERT INTO nl3_orders
  (order_id, customer_id, order_date, order_status, amount)
SELECT LEVEL,
       MOD(LEVEL - 1, 10000) + 1,
       DATE '2025-01-01' + TRUNC((LEVEL - 1) / 2000),
       CASE WHEN MOD(LEVEL, 5) = 0 THEN 'C' ELSE 'P' END,
       10000
FROM dual
CONNECT BY LEVEL <= 200000;

COMMIT;

-- 05. 기존 인덱스와 주문 선행용 인덱스
CREATE INDEX nl3_o_c_ix
  ON nl3_orders (customer_id);

CREATE INDEX nl3_o_sd_ix
  ON nl3_orders (order_status, order_date);

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

COLUMN plan_table_output FORMAT A200
COLUMN order_count FORMAT 999999999
COLUMN total_amount FORMAT 999999999999
COLUMN name_chars FORMAT 999999999

SPOOL nl3_report.txt REPLACE

-- 06. 변경 전: 고객 선행, 고객 번호 단일 인덱스
PROMPT A: CUSTOMER FIRST / SINGLE-COLUMN INDEX

SELECT /*+ GATHER_PLAN_STATISTICS NO_PARALLEL
           LEADING(c o) USE_NL(o)
           FULL(c) INDEX(o nl3_o_c_ix) */
       COUNT(*) AS order_count,
       SUM(o.amount) AS total_amount,
       SUM(LENGTH(c.customer_name)) AS name_chars
FROM nl3_customers c
JOIN nl3_orders o
  ON o.customer_id = c.customer_id
WHERE o.order_status = 'P'
  AND o.order_date >= DATE '2025-04-10'
  AND o.order_date <  DATE '2025-04-11';

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

-- 07. 내부 탐색용 복합 인덱스 추가
CREATE INDEX nl3_o_csd_ix
  ON nl3_orders (customer_id, order_status, order_date);

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

-- 08. 변경 중간: 고객 선행 유지, 내부 인덱스 개선
PROMPT B: CUSTOMER FIRST / COMPOSITE INDEX

SELECT /*+ GATHER_PLAN_STATISTICS NO_PARALLEL
           LEADING(c o) USE_NL(o)
           FULL(c) INDEX(o nl3_o_csd_ix) */
       COUNT(*) AS order_count,
       SUM(o.amount) AS total_amount,
       SUM(LENGTH(c.customer_name)) AS name_chars
FROM nl3_customers c
JOIN nl3_orders o
  ON o.customer_id = c.customer_id
WHERE o.order_status = 'P'
  AND o.order_date >= DATE '2025-04-10'
  AND o.order_date <  DATE '2025-04-11';

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

-- 09. 변경 후: 주문 선행, 고객 기본키로 내부 탐색
PROMPT C: ORDER FIRST / CUSTOMER PRIMARY KEY

SELECT /*+ GATHER_PLAN_STATISTICS NO_PARALLEL
           LEADING(o c) USE_NL(c)
           INDEX(o nl3_o_sd_ix) INDEX(c nl3_c_pk) */
       COUNT(*) AS order_count,
       SUM(o.amount) AS total_amount,
       SUM(LENGTH(c.customer_name)) AS name_chars
FROM nl3_orders o
JOIN nl3_customers c
  ON c.customer_id = o.customer_id
WHERE o.order_status = 'P'
  AND o.order_date >= DATE '2025-04-10'
  AND o.order_date <  DATE '2025-04-11';

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

SPOOL OFF

-- 10. 생성 데이터와 조회 결과 검증
DECLARE
  v_order_count  NUMBER;
  v_total_amount NUMBER;
  v_name_chars   NUMBER;
BEGIN
  SELECT COUNT(*),
         SUM(o.amount),
         SUM(LENGTH(c.customer_name))
  INTO v_order_count, v_total_amount, v_name_chars
  FROM nl3_customers c
  JOIN nl3_orders o
    ON o.customer_id = c.customer_id
  WHERE o.order_status = 'P'
    AND o.order_date >= DATE '2025-04-10'
    AND o.order_date <  DATE '2025-04-11';

  IF v_order_count != 1600
     OR NVL(v_total_amount, -1) != 16000000
     OR NVL(v_name_chars, -1) != 11200 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Unexpected result');
  END IF;
END;
/

-- 11. 성공 시 고정 출력
SET TERMOUT ON
PROMPT RESULT_CHECK=OK
PROMPT ORDER_COUNT=1600
PROMPT TOTAL_AMOUNT=16000000
PROMPT NAME_CHARS=11200
PROMPT PLAN_REPORT=nl3_report.txt

EXIT SUCCESS

줄별 해설

01의 오류 처리는 실패한 중간 단계를 성공한 실험처럼 읽지 않도록 한다. TERMOUT OFF는 스크립트 실행 중 화면 출력을 숨기지만 스풀 파일 기록은 유지한다. 적응형 계획을 끄는 설정은 이번 비교에서 실행계획의 형태를 읽기 쉽게 하기 위한 세션 설정이며 운영 환경의 권장 기본값을 뜻하지 않는다.

02는 테이블이 없다는 오류만 무시한다. 다른 오류는 다시 발생시켜 실행을 중단한다. 03의 고객 기본키는 마지막 단계에서 내부 고객 탐색에 사용된다. 주문의 기본키는 데이터 식별을 위한 것이며 이번 날짜 검색이나 고객별 주문 탐색을 대신하지 않는다.

04는 고객 번호를 1부터 10,000까지 반복하고 날짜를 2,000건마다 하루씩 증가시킨다. 따라서 고객마다 주문은 20건이고 마지막 날짜는 2025년 4월 10일이다. 다섯 건 중 한 건이 C이므로 해당 날짜의 P 주문은 1,600건이다. 고객 이름은 ‘회원’ 두 글자와 다섯 자리 번호로 구성되어 LENGTH가 7이다.

이 생성 규칙에서는 상태와 고객 번호 사이에 상관관계가 생긴다. 이번 실험은 행 수 추정의 정확도를 비교하는 실험이 아니므로 순서와 접근 경로를 명시하고 실제 행 수를 읽는다. 운영 통계 설계를 평가할 때는 이 인공 분포를 그대로 대표 데이터로 사용하지 않는다.

05는 두 접근 경로를 미리 만든다. A 단계에서는 INDEX 힌트가 고객 번호 단일 인덱스를 선택하게 하므로 주문 선행용 인덱스의 존재가 실험 의도를 바꾸지 않는다. 통계 수집은 전체 표본을 사용하고 히스토그램을 생성하지 않아 실험 조건을 단순하게 유지한다.

06의 LEADING(c o), USE_NL(o), INDEX(o nl3_o_c_ix)는 각각 순서, 조인 방식, 내부 접근 경로를 지정한다. GATHER_PLAN_STATISTICS는 실제 실행 통계를 수집하게 한다. 집계 결과를 끝까지 가져온 직후 DISPLAY_CURSOR를 호출하므로 방금 실행한 SQL의 마지막 실행 통계를 확인할 수 있다. 중간에 다른 데이터베이스 SQL을 삽입하면 NULL로 지정한 대상 커서가 달라질 수 있다.

07과 08은 순서를 유지한 채 내부 인덱스만 바꾼다. A와 B의 차이는 반복당 불필요한 주문 접근 감소로 설명한다. 09는 주문 조건을 먼저 적용하고 고객 기본키로 연결한다. B와 C의 차이는 반복을 시작하는 행 수와 두 테이블의 접근 경로 변화로 설명한다.

10은 데이터와 조건식이 의도대로 구성되었는지 검증한다. A, B, C는 힌트 외의 SELECT 표현식과 조건이 같으며 각 집계 결과도 보고서에 남는다. 11의 성공 문구는 이 검증을 통과한 뒤에만 출력된다. 이 코드의 정확한 실행 여부와 실제 읽기 수치는 실행 환경에서 확인해야 하며, 아래 설명용 수치를 실행 검증 결과로 간주하지 않는다.

실행 결과

다음 명령은 Oracle 지갑에 BOOKSTORE 접속 정보가 등록되어 있고 SQL*Plus 시작 스크립트가 별도 문구를 출력하지 않는 환경을 전제로 한다. 지갑을 사용하지 않는 환경에서는 접속 부분을 자신의 서비스와 계정으로 바꾼다. SQL 파일은 클라이언트 문자 설정에 맞는 인코딩으로 저장한다.

sqlplus -s -L /@BOOKSTORE @nl_join_case.sql

정상 실행의 터미널 출력은 다음과 같다. 성공 출력과 달리 실행계획의 논리 읽기 수치는 고정값이 아니다.

RESULT_CHECK=OK
ORDER_COUNT=1600
TOTAL_AMOUNT=16000000
NAME_CHARS=11200
PLAN_REPORT=nl3_report.txt

실제 집계 결과와 실행계획은 다음 명령으로 읽는다. A, B, C의 ORDER_COUNT, TOTAL_AMOUNT, NAME_CHARS는 각각 1600, 16000000, 11200이어야 한다.

cat nl3_report.txt

아래는 계획을 읽는 방법을 보여 주기 위한 설명용 축약 계획이다. 실측 출력을 옮긴 것이 아니며 Buffers 수치도 비교 연습용 가정값이다. Oracle의 패치 수준, 블록 크기, 행 배치와 NL 구현에 따라 연산의 분리 방식과 읽기 수치는 달라진다. 실제 보고서에서는 ROWID 접근이 배치 형태로 나타나거나 NL 연산이 두 단계로 표현될 수 있다.

A: 고객 선행, 고객 번호 단일 인덱스
Operation                              Starts    A-Rows
SORT AGGREGATE                              1         1
  NESTED LOOPS                              1      1600
    TABLE ACCESS FULL NL3_CUSTOMERS         1     10000
    TABLE ACCESS BY INDEX ROWID             10000      1600
      INDEX RANGE SCAN NL3_O_C_IX       10000    200000
전체 Buffers 가정값: 224000

B: 고객 선행, 내부 복합 인덱스
Operation                              Starts    A-Rows
SORT AGGREGATE                              1         1
  NESTED LOOPS                              1      1600
    TABLE ACCESS FULL NL3_CUSTOMERS         1     10000
    TABLE ACCESS BY INDEX ROWID             10000      1600
      INDEX RANGE SCAN NL3_O_CSD_IX     10000      1600
전체 Buffers 가정값: 33000

C: 주문 선행, 고객 기본키 탐색
Operation                              Starts    A-Rows
SORT AGGREGATE                              1         1
  NESTED LOOPS                              1      1600
    TABLE ACCESS BY INDEX ROWID                 1      1600
      INDEX RANGE SCAN NL3_O_SD_IX          1      1600
    TABLE ACCESS BY INDEX ROWID              1600      1600
      INDEX UNIQUE SCAN NL3_C_PK         1600      1600
전체 Buffers 가정값: 6500

A에서는 내부 인덱스가 200,000개의 후보를 반환하지만 주문 테이블의 날짜·상태 필터를 통과한 행은 1,600개다. B에서는 후보 자체가 1,600개로 줄어든다. 그래도 고객 10,000명에 대한 내부 탐색은 남는다. C에서는 주문 1,600개를 먼저 만들고 고객을 찾으므로 내부 탐색을 시작하는 행 수도 감소한다.

동일 결과를 반환하는 세 단계의 비교 예시이며 읽기 수치는 가정값이다

단계	외부 행 수	내부 인덱스 반환 행 수	전체 Buffers
A: 기존 경로	10,000	200,000	224,000
B: 내부 인덱스 개선	10,000	1,600	33,000
C: 주문 선행	1,600	1,600	6,500

이 가정값에서는 A 대비 C의 논리 읽기가 약 97.1% 감소한다. 실제 개선율은 보고서의 동일한 최상위 연산에서 읽은 값으로 다시 계산한다. 수치가 예시와 다르다는 이유로 실패라고 판단하지 않는다. 내부 후보 행이 줄었는지, 외부 행이 줄었는지, 전체 논리 읽기가 줄었는지를 순서대로 확인한다.

운영 검증에서는 동일한 바인드 값으로 각 후보 SQL을 충분히 가져오고 반복 측정한다. 캐시가 따뜻해지면 물리 읽기와 경과시간이 크게 달라질 수 있다. 논리 읽기도 실행 방식과 환경의 영향을 받으므로 한 번의 시간 측정만으로 결론을 내리지 않는다. 공유 환경에서 캐시를 강제로 비우는 방식은 사용하지 않는다.

실무에서 자주 틀리는 것

USE_NL을 조인 순서 힌트로 해석한다

다음 SQL은 주문을 먼저 읽고 싶다는 의도와 힌트가 맞지 않는다. USE_NL(o)는 주문을 내부 입력으로 요청한다.

SELECT /*+ USE_NL(o) */ o.order_id, c.customer_name
FROM nl3_orders o
JOIN nl3_customers c ON c.customer_id = o.customer_id
WHERE o.order_status = 'P'
  AND o.order_date >= DATE '2025-04-10'
  AND o.order_date <  DATE '2025-04-11';

순서는 LEADING으로, 내부 테이블의 조인 방식은 USE_NL로 표현한다.

SELECT /*+ LEADING(o c) USE_NL(c) */ o.order_id, c.customer_name
FROM nl3_orders o
JOIN nl3_customers c ON c.customer_id = o.customer_id
WHERE o.order_status = 'P'
  AND o.order_date >= DATE '2025-04-10'
  AND o.order_date <  DATE '2025-04-11';

내부 탐색 인덱스에서 조인 키를 뒤로 밀어낸다

고객 한 명씩 전달받는 탐색에 아래 순서를 사용하면 날짜 범위 안에서 고객 번호를 추가로 검사하는 형태가 될 수 있다. 날짜 선두 인덱스 자체가 잘못된 것이 아니라 해당 내부 탐색과 맞지 않는 것이 문제다.

CREATE INDEX nl3_trial_ix
  ON nl3_orders (order_date, customer_id, order_status);

고객 선행을 유지하는 실험에서는 동등 조건인 고객 번호와 상태를 먼저 두고 날짜 범위를 뒤에 둔다. 다음 코드는 앞 코드의 대체 정의다.

CREATE INDEX nl3_trial_ix
  ON nl3_orders (customer_id, order_status, order_date);

날짜에 함수를 적용해 탐색 구간을 흐린다

일반적인 상태·날짜 인덱스를 만들고도 날짜를 문자열로 바꾸면 원래 날짜 컬럼의 범위 접근을 활용하기 어려워진다.

WHERE o.order_status = 'P'
  AND TO_CHAR(o.order_date, 'YYYY-MM-DD') = '2025-04-10'

시각이 포함된 주문도 포함하도록 다음 날 미만의 범위를 사용한다. 애플리케이션에서는 날짜 자료형의 시작값과 종료값을 바인드한다.

WHERE o.order_status = 'P'
  AND o.order_date >= DATE '2025-04-10'
  AND o.order_date <  DATE '2025-04-11'

예상 계획을 실제 읽기 기록으로 사용한다

다음 코드는 예상 접근 경로를 확인하는 데 사용할 수 있지만 SQL을 실행한 논리 읽기를 제공하지 않는다.

EXPLAIN PLAN FOR
SELECT COUNT(*) FROM nl3_orders WHERE order_status = 'P';

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

실제 SQL을 끝까지 실행하고 마지막 실행 통계를 확인한다. SQL*Plus에서 AUTOTRACE와 SERVEROUTPUT를 끈 상태로 연속 실행한다.

SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*)
FROM nl3_orders
WHERE order_status = 'P';

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

한눈에 보기

NL 조인 비용을 두 방향으로 나누어 점검하는 기준

점검 대상	확인할 증거	이번 사례의 조치
외부 입력 크기	조건 적용 후 실제 행 수	고객 10,000행 대신 주문 1,600행을 먼저 만든다.
내부 탐색 범위	인덱스 A-Rows와 접근 조건	고객 선행에서는 고객 번호·상태·날짜를 연결한다.
조인 방향	내부 테이블과 사용 인덱스	주문 선행에서는 고객 기본키를 사용한다.
변경 효과	동일 결과와 전체 Buffers	인덱스 변경과 순서 변경을 단계별로 비교한다.
적용 범위	날짜별 조회량과 반복 측정	하루 조회의 결론을 장기간 조회에 그대로 적용하지 않는다.

실험용 인덱스를 운영에 모두 남길 필요는 없다. 고객별 주문 조회와 일별 주문 조회의 빈도, 기존 인덱스와의 중복, 저장 공간과 변경 비용을 함께 확인한 뒤 필요한 구성을 선택한다. 조회 기간이 길어져 외부 입력이 크게 증가하면 이번 순서에서도 고객 탐색이 반복된다. 다음 장에서는 입력 규모가 커졌을 때 다른 조인 방식으로 비용을 줄이는 사례를 다룬다.

연습 문제

1. A에서 B로 바꾼 뒤 내부 인덱스의 Starts는 그대로이고 A-Rows와 전체 Buffers는 줄었다. 어떤 비용이 감소했으며 어떤 비용이 남았는지 설명하라.
2. 검색 조건에 고객 번호 하나가 추가되었다. 이때도 주문 선행이 유리하다고 단정할 수 있는가. 비교할 순서와 인덱스를 제시하라.
3. FROM 절이 nl3_customers c, nl3_orders o 순서일 때 ORDERED와 USE_NL(c)를 함께 지정했다. 두 힌트가 의도하는 역할을 설명하고 주문 선행을 명확히 요청하는 힌트로 고쳐라.
4. 실제 측정에서 변경 전 Buffers가 180,000이고 변경 후가 12,000이었다. 감소율을 구하고, 이 결과만으로 응답시간도 같은 비율로 줄었다고 말할 수 없는 이유를 설명하라.

정답과 해설

1. 인덱스 안에서 날짜와 상태를 함께 적용하면서 반환 후보와 불필요한 주문 테이블 방문이 감소했다. 외부 고객 10,000행은 그대로이므로 고객마다 내부 인덱스를 탐색하는 비용은 남았다. 결과가 없는 고객에 대한 탐색도 공짜가 아니다.
2. 단정할 수 없다. 고객 기본키로 외부 입력을 한 행으로 줄인 뒤 주문의 고객 번호·상태·날짜 인덱스를 탐색하는 순서를 비교한다. 날짜 조건으로 주문 1,600행을 만든 뒤 고객 조건을 검사하는 경로보다 적은 일을 할 가능성이 있다. 실제 접근 조건과 Buffers로 확인한다.
3. ORDERED는 고객 선행을 요청하지만 USE_NL(c)는 고객을 내부 입력으로 요청한다. 단순한 두 테이블 조인에서 의도가 맞지 않으므로 기대한 방식이 적용되지 않을 수 있다. ORDERED를 제거하고 LEADING(o c) USE_NL(c)를 사용하면 주문 선행과 고객 내부 탐색을 함께 표현할 수 있다.
4. 감소율은 (180,000 - 12,000) / 180,000 × 100으로 약 93.3%다. 응답시간에는 물리 읽기, CPU 사용, 대기, 결과 전송 등이 함께 영향을 준다. 논리 읽기 감소는 수행한 블록 접근 작업이 줄었다는 증거이며 응답시간의 동일 비율 감소를 보장하지 않는다.