해시 조인 튜닝 - Build 입력과 대용량 조인

이 장에서 배우는 것

앞 장에서 소수의 주문을 찾아 관련 행을 반복 조회하는 방식을 다뤘다. 이번에는 온라인 서점의 일별 매출 집계 배치를 살펴본다. 주문 상세를 넓게 읽어야 하는 집계에서는 해시 조인(hash join)이 적합할 수 있다. 그러나 같은 테이블을 같은 양만큼 읽더라도 어느 입력으로 해시 테이블을 만들었는지에 따라 메모리 사용과 임시 공간 입출력이 달라진다.

이번 사례는 조인 방향만 바꾼 두 SQL을 비교한다. 조인 조건과 업무 결과를 유지한 채 메모리에 올리는 입력을 줄이는 것이 목적이다. 실행계획의 예상 비용만 비교하지 않고 실제 처리 행 수, 논리 읽기, 작업영역 사용을 함께 해석한다.

• 빌드(Build) 입력과 프로브(Probe) 입력을 실제 실행계획에서 구분한다.
• 필터를 적용한 뒤의 행 수와 필요한 열의 크기로 빌드 입력을 판단한다.
• 메모리가 부족할 때 발생하는 임시 공간 사용을 논리 읽기와 구분한다.
• 대용량 집계 조인의 결과를 유지하면서 조인 입력을 줄이고 전후 효과를 비교한다.

문제 상황

온라인 서점 운영팀은 매일 새벽 전날의 판매 채널별 매출을 집계한다. 최근 주문 상세가 늘어나면서 배치 시간이 길어졌다. 같은 시간에 다른 정산 작업도 실행되므로 임시 테이블스페이스의 사용량과 저장장치 부하까지 함께 증가한다.

재현 데이터에는 주문 10만 건과 주문 상세 100만 건이 있다. 주문 하나마다 상세가 10개씩 존재하며, 주문일은 100일에 균등하게 분포한다. 하루를 조회하면 주문 1,000건과 상세 10,000건이 집계 대상이다. 집계 결과는 판매 채널 세 개의 행으로 줄어든다.

문제가 된 계획은 주문 상세 100만 건으로 해시 테이블을 만든다. 날짜로 걸러진 주문 1,000건은 그 해시 테이블을 조회하는 쪽이다. 업무에 필요한 주문은 적지만 메모리에 올리는 쪽은 크다. 상세 전체를 읽는 비용에 큰 해시 테이블의 관리 비용까지 더해진다.

실습에서는 이 상태를 힌트로 재현하고, 두 번째 SQL에서 날짜 조건을 통과한 주문을 빌드 입력으로 지정한다. 운영 환경에서는 먼저 기존 계획과 통계를 확인해야 한다. 옵티마이저가 이미 작은 주문 집합을 빌드 입력으로 선택했다면 같은 변경을 적용할 이유가 없다.

아래 수치 비교는 실행계획을 읽기 위한 설명용 가정이다. 실제 Oracle 서버에서 측정한 결과로 제시하지 않는다. 완성 코드는 실행한 서버의 실제 통계를 별도 보고서에 남기며, 데이터와 무관하게 고정되는 업무 결과만 예상 출력으로 제시한다.

작은 테이블보다 작은 빌드 입력을 찾는다

해시 조인은 한 입력의 조인 키를 해시 값으로 변환하여 해시 테이블을 만든다. 이어서 다른 입력을 읽으며 대응하는 후보를 찾고 조인 조건을 검사한다. 해시 값이 같다는 사실만으로 조인이 성립하지는 않는다. 충돌한 후보 사이에서 실제 키도 비교한다.

빌드 입력이 작으면 해시 테이블에 필요한 메모리가 줄어드는 경향이 있다. 여기서 작다는 말은 원본 테이블의 전체 행 수만을 뜻하지 않는다. 필터를 통과한 행 수, 다음 연산으로 전달할 열의 폭, 해시 버킷과 행 관리에 필요한 부가 공간까지 고려해야 한다.

이번 사례에서 주문 테이블은 10만 건이지만 날짜 조건을 적용하면 1,000건이다. 주문 번호와 채널 번호만 조인 이후에 필요하다. 반면 상세는 필터 없이 100만 건을 읽으며 주문 번호, 수량, 단가가 필요하다. 주문 쪽을 빌드 입력으로 삼는 판단은 테이블 이름이나 저장 용량보다 이 차이에서 출발한다.

직렬 실행의 일반적인 Oracle 해시 조인 계획에서는 해시 조인 바로 아래의 첫 번째 자식이 빌드 입력이고 두 번째 자식이 프로브 입력이다. 첫 번째 자식이 또 다른 조인이면 그 하위 트리의 출력 전체가 빌드 입력이다. SQL의 FROM 절에 먼저 적힌 테이블만 보고 판단해서는 안 된다.

조인 순서와 빌드 방향도 구분해야 한다. LEADING 힌트는 조인 순서를 지정하는 데 쓰고, USE_HASH 힌트는 조인 방식을 유도한다. 이것만으로 빌드 방향까지 확정했다고 판단하지 않는다. 실습에서는 SWAP_JOIN_INPUTS와 NO_SWAP_JOIN_INPUTS를 함께 사용하며 실제 자식 순서로 적용 여부를 확인한다. 이 입력 교환 힌트는 공개 문서에 충분히 설명된 일반 인터페이스로 간주하기보다 재현과 진단을 위한 수단으로 다루는 편이 적절하다.

메모리가 부족하면 임시 공간도 읽고 쓴다

해시 테이블은 SQL 작업영역(work area)의 메모리를 사용한다. 필요한 데이터를 메모리에 유지할 수 있으면 빌드가 끝난 뒤 프로브 입력을 읽으며 조인을 진행한다. 메모리가 부족하면 일부 데이터를 분할하여 임시 공간에 기록하고, 나중에 다시 읽어 처리할 수 있다. 같은 입력 행 수라도 이 추가 작업이 있으면 수행 시간이 늘어날 수 있다.

메모리에서 처리가 끝나는 실행, 디스크로 내려간 분할을 한 차례 더 처리하는 실행, 추가 분할과 처리가 필요한 실행을 구분해야 한다. 작은 빌드 입력은 메모리 요구량을 줄일 뿐 아니라 이러한 추가 처리의 가능성을 낮춘다. 다만 실제 결과는 메모리 정책, 동시 실행 수, 행 폭, 키 분포에 영향을 받는다.

여기서 논리 읽기의 해석을 주의해야 한다. Oracle 실행계획의 Buffers는 버퍼 접근을 보여 주는 지표다. 임시 공간으로 직접 수행하는 입출력 전체를 그 숫자 하나로 설명할 수는 없다. 따라서 빌드 방향을 바꾼 뒤 Buffers가 거의 같아도 임시 공간 사용과 경과 시간이 줄었다면 개선일 수 있다.

ALLSTATS LAST 출력에서는 A-Rows와 Starts를 먼저 읽고, 메모리 연산의 OMem, 1Mem, Used-Mem 등 제공되는 열을 함께 살펴본다. OMem과 1Mem은 필요한 메모리에 관한 추정치이며 실제로 사용한 메모리와 같은 의미가 아니다. 임시 공간 통계도 최대 점유량인지 누적 읽기·쓰기량인지 구분해야 한다. 두 값을 모두 단순히 ‘디스크 사용량’이라고 적으면 비교가 흐려진다.

완성 코드는 차이를 관찰하기 쉽도록 전용 서버 실습 세션에서 수동 작업영역 정책과 1MB의 HASH_AREA_SIZE를 사용한다. 이는 작업영역의 크기를 제한하는 실험 조건이며, 프로세스 전체 메모리를 1MB로 제한한다는 뜻이 아니다. 운영 환경의 자동 메모리 정책을 이 설정으로 대체하라는 권고도 아니다.

조인 전에 거르고, 집계할 위치를 판단한다

이번 SQL의 날짜 조건은 주문 테이블만으로 평가할 수 있다. 내부 조인이므로 주문 스캔에서 먼저 조건을 적용해도 결과가 같다. 계획의 주문 액세스 행에 날짜 필터가 표시되고 A-Rows가 1,000인지 확인하면, 해시 조인에 들어가기 전에 주문이 줄었는지 알 수 있다.

WHERE 절에서 조건을 앞줄에 배치하는 것과 실제로 일찍 평가되는 것은 별개다. 단순한 인라인 뷰로 주문을 감싼다고 필터 순서가 강제되는 것도 아니다. SQL의 배치 모양보다 실행계획의 조건 위치와 실제 행 수를 근거로 판단한다.

프로브 입력도 구분해서 읽어야 한다. 개선 후 해시 조인의 출력은 10,000건이지만 상세 테이블 스캔은 여전히 100만 건이다. 해시 테이블에 없는 주문 번호는 조인에서 탈락하지만, 그 상세 행을 읽지 않은 것은 아니다. 이번 실습은 상세에 접근용 인덱스를 만들지 않으며 두 SQL 모두 전체 스캔을 수행한다.

대용량 집계에서는 상세를 먼저 주문 단위로 합산하는 방안도 생각할 수 있다. 상세 100만 건을 주문 10만 건으로 줄이면 조인 입력은 작아진다. 그러나 하루치 주문만 필요할 때는 탈락할 주문의 상세까지 먼저 집계하는 비용이 생긴다. 이번에는 주문을 먼저 거르고 조인 결과 10,000건을 채널 세 개로 집계한다.

반대로 대부분의 주문을 조회하고 주문별 상세가 매우 많다면 선집계가 유리할 수 있다. 선집계를 검토할 때는 집계 키에 조인에 필요한 키가 남아 있는지, 조인 대상이 그 키로 유일한지, 집계식이 분해 가능한지도 확인해야 한다. 평균을 먼저 구한 뒤 다시 평균을 구하는 방식은 일반적으로 원래 평균과 같지 않다.

Oracle 19c와 MySQL 8.0에서 해시 조인을 확인하는 방법의 차이

항목	Oracle 19c	MySQL 8.0
지원 범위	동등 조건을 포함하는 대량 조인에서 활용한다.	8.0.18부터 지원하며 세부 지원 범위는 마이너 버전에 따라 다르다.
실제 실행 확인	통계를 수집하고 DBMS_XPLAN의 ALLSTATS LAST로 확인한다.	8.0.18부터 EXPLAIN ANALYZE로 실제 행 수와 시간을 확인한다.
빌드 입력 확인	이번 직렬 계획에서는 해시 조인의 첫 번째 자식이다.	트리 출력의 Hash 아래 입력을 확인한다.
메모리와 비교 지표	SQL 작업영역과 Buffers, 메모리 통계를 함께 본다.	join_buffer_size가 관련되며, 실행계획에 Oracle Buffers와 동일한 지표는 없다.

두 제품의 힌트와 설정 이름을 서로 옮겨 쓰면 안 된다. 특히 MySQL 8.0은 마이너 버전에 따라 해시 조인 제어 방식도 달라지므로 ‘MySQL 8에서 실행했다’는 기록만으로는 재현 조건이 충분하지 않다. 아래 코드는 Oracle 19c 전용이다.

완성 코드

macOS 또는 Linux에서 SQL*Plus로 Oracle 19c 서버에 접속하여 실행한다. macOS에서 데이터베이스 서버가 직접 실행된다는 전제는 아니다. 다른 업무 객체와 충돌하지 않는 실습 스키마를 사용하며, 테이블 생성 권한과 테이블스페이스 할당량, 세션 설정 권한이 필요하다. DBMS_STATS와 DBMS_XPLAN 실행 권한 및 V$SESSION, V$SQL, V$SQL_PLAN, V$SQL_PLAN_STATISTICS_ALL에 대한 조회 권한도 준비한다.

파일은 한 개다. 테이블 생성부터 데이터 적재, 통계 수집, 두 SQL 실행과 실제 계획 저장까지 포함한다. 기존 객체를 지우는 코드는 넣지 않았다. 같은 이름의 테이블이 이미 있으면 오류로 중단되므로 새 실습 스키마에서 실행한다. 저장 프로시저나 외부 라이브러리를 만들지 않는 SQL*Plus 스크립트여서 별도 컴파일 단계는 없다.

hash_join_demo.sql

whenever oserror exit failure
whenever sqlerror exit sql.sqlcode rollback

set echo off
set verify off
set feedback off
set heading off
set pagesize 0
set linesize 220
set trimspool on
set tab off
set recsep off
set serveroutput off
set termout off

alter session set workarea_size_policy = manual;
alter session set hash_area_size = 1048576;

create table hj_orders (
    order_id     number(10) not null,
    order_date   date not null,
    channel_code number(1) not null,
    constraint hj_orders_pk primary key (order_id)
);

create table hj_items (
    order_id  number(10) not null,
    line_no   number(2) not null,
    quantity  number(3) not null,
    unit_price number(8) not null
);

insert into hj_orders (order_id, order_date, channel_code)
select level,
       date '2025-01-01' + mod(level - 1, 100),
       mod(trunc((level - 1) / 100), 3) + 1
from dual
connect by level <= 100000;

insert into hj_items (order_id, line_no, quantity, unit_price)
select o.order_id,
       n.line_no,
       1,
       1000 + 100 * mod(n.line_no, 5)
from hj_orders o
cross join (
    select level as line_no
    from dual
    connect by level <= 10
) n;

commit;

begin
    dbms_stats.gather_table_stats(
        ownname => user,
        tabname => 'HJ_ORDERS',
        estimate_percent => 100,
        method_opt => 'FOR ALL COLUMNS SIZE 1',
        cascade => true
    );
    dbms_stats.gather_table_stats(
        ownname => user,
        tabname => 'HJ_ITEMS',
        estimate_percent => 100,
        method_opt => 'FOR ALL COLUMNS SIZE 1',
        cascade => true
    );
end;
/

column result_line format a40

spool hash_join_report.txt replace

prompt A_RESULT
select /*+ gather_plan_statistics
           leading(o l) use_hash(l) swap_join_inputs(l)
           full(o) full(l) no_parallel */
       to_char(o.channel_code, 'FM0') || '|' ||
       to_char(count(*), 'FM9999999990') || '|' ||
       to_char(sum(l.quantity * l.unit_price),
               'FM9999999990') as result_line
from hj_orders o
join hj_items l on l.order_id = o.order_id
where o.order_date >= date '2025-01-01'
  and o.order_date < date '2025-01-02'
group by o.channel_code
order by o.channel_code;

select plan_table_output
from table(
    dbms_xplan.display_cursor(
        null, null,
        'ALLSTATS LAST +PREDICATE +ALIAS +HINT_REPORT'
    )
);

prompt B_RESULT
select /*+ gather_plan_statistics
           leading(o l) use_hash(l) no_swap_join_inputs(l)
           full(o) full(l) no_parallel */
       to_char(o.channel_code, 'FM0') || '|' ||
       to_char(count(*), 'FM9999999990') || '|' ||
       to_char(sum(l.quantity * l.unit_price),
               'FM9999999990') as result_line
from hj_orders o
join hj_items l on l.order_id = o.order_id
where o.order_date >= date '2025-01-01'
  and o.order_date < date '2025-01-02'
group by o.channel_code
order by o.channel_code;

select plan_table_output
from table(
    dbms_xplan.display_cursor(
        null, null,
        'ALLSTATS LAST +PREDICATE +ALIAS +HINT_REPORT'
    )
);

spool off
set termout on
prompt 완료: hash_join_report.txt
exit success

줄별 해설

처음 두 줄은 운영체제 오류와 SQL 오류가 발생했을 때 실행을 중단한다. 데이터 적재가 실패했는데도 뒤쪽 조회를 계속 실행하는 상황을 막는다. 다만 Oracle의 DDL은 암묵적으로 커밋되므로 마지막의 rollback이 이미 생성된 테이블까지 없애지는 않는다.

SET 구문은 보고서 형식을 일정하게 만든다. 특히 TERMOUT OFF는 파일에서 실행되는 명령의 화면 출력을 숨긴다. SPOOL에는 조회 결과와 실행계획이 기록된다. SERVEROUTPUT OFF는 DBMS_OUTPUT 처리 때문에 계획을 확인하려는 SQL 사이에 다른 호출이 끼어드는 일을 피하기 위한 설정이다.

두 ALTER SESSION 문장은 이번 연결의 작업영역 정책을 바꾼다. 비교하는 A와 B는 같은 설정에서 실행된다. 스크립트 끝에서 연결을 종료하므로 이후 새 연결은 자신의 기본 설정을 사용한다. 운영 배치의 성능을 판단할 때는 별도로 실제 자동 메모리 정책에서도 측정해야 한다.

HJ_ORDERS의 기본 키는 주문 번호의 유일성을 보장한다. HJ_ITEMS에는 접근용 인덱스를 만들지 않는다. 주문 번호마다 상세가 열 개씩 있으므로 내부 조인은 선택된 주문 하나를 상세 열 개로 확장한다. 이 관계를 알아야 조인 출력 10,000건이 정상인지 판단할 수 있다.

첫 INSERT의 날짜는 100일 주기로 반복된다. 1월 1일 주문 번호는 1, 101, 201처럼 증가한다. 채널 번호는 주문 번호를 100으로 나눈 몫을 이용해 세 종류로 배정하므로 해당 날짜에도 세 채널이 모두 나온다.

두 번째 INSERT는 주문과 1부터 10까지의 번호를 교차 결합한다. 수량은 모두 1이며 단가는 1,000원부터 1,400원까지 반복된다. 주문 하나의 상세 금액 합은 12,000원이다. 임의 데이터 생성 함수를 사용하지 않아 실행할 때마다 업무 결과가 같다.

통계 수집에서는 전체 데이터를 표본으로 사용하고 히스토그램을 만들지 않는다. 재현 실험의 조건을 단순하게 유지하기 위한 선택이다. 실제 운영의 데이터 편향까지 이러한 설정으로 처리하라는 의미는 아니다.

A의 GATHER_PLAN_STATISTICS는 실제 행 수와 버퍼 접근 등의 수집을 요청한다. FULL과 NO_PARALLEL은 두 테이블을 직렬 전체 스캔하는 비교 조건을 만든다. SWAP_JOIN_INPUTS는 주문 상세가 첫 번째 자식이 되는 계획을 유도한다.

B는 같은 조인 순서와 조인 방식을 사용하면서 입력 교환을 막도록 요청한다. 기대하는 차이는 해시 조인 아래에서 주문이 첫 번째 자식으로 나타나는 것이다. 힌트가 적혀 있어도 적용이 보장되는 것은 아니므로 계획과 힌트 보고서를 확인한다.

각 조회 바로 다음의 DISPLAY_CURSOR는 직전에 실행한 커서의 마지막 실행 통계를 출력한다. 두 문장 사이에 다른 SQL을 추가하지 않는다. 도구가 내부 조회를 삽입하는 환경에서는 대상 SQL_ID와 자식 커서 번호를 명시하는 방식으로 바꿔야 한다.

마지막 PROMPT는 파일 기록 단계가 끝났다는 뜻이다. 힌트가 모두 적용됐거나 성능이 개선됐다는 자동 판정은 아니다. 보고서에 실제 통계가 없다는 안내가 나오면 권한과 통계 수집 여부부터 확인한다.

실행 결과

SQL*Plus를 사용할 수 있는 터미널에서 다음과 같이 실행한다. BOOKLAB은 준비한 접속 식별자이며 계정 비밀번호는 접속 과정에서 입력한다.

sqlplus -s booklab@BOOKLAB @hash_join_demo.sql

접속 과정의 비밀번호 입력 안내를 제외하면 스크립트의 정상 종료 출력은 다음과 같다.

완료: hash_join_report.txt

보고서의 업무 결과만 추출하는 명령과 예상 출력은 다음과 같다. 두 조회 모두 세 행을 끝까지 가져오므로 실행 통계도 전체 처리 기준으로 수집한다.

awk '/^[AB]_RESULT$/ || /^[123][|]/' hash_join_report.txt

A_RESULT
1|3340|4008000
2|3330|3996000
3|3330|3996000
B_RESULT
1|3340|4008000
2|3330|3996000
3|3330|3996000

채널 1에는 주문 334건이 있고 나머지 채널에는 각각 333건이 있다. 상세 건수의 합은 10,000건이고 매출 합계는 12,000,000원이다. SQL 간 비교에서는 합계 하나만 확인하지 않고 채널별 건수와 금액을 함께 대조한다.

다음은 기대하는 실행계획의 핵심 형태를 설명용 숫자로 축약한 것이다. 실제 보고서의 복사본이나 고정 예상 출력이 아니다. 최상단 집계는 환경에 따라 다른 연산 조합으로 나타날 수 있으며, 중요한 것은 해시 조인의 두 입력과 실제 행 수다.

A: 큰 상세를 빌드하는 계획의 예
Operation                       A-Rows   Buffers
SELECT STATEMENT                     3      8260
  SORT GROUP BY                      3      8260
    HASH JOIN                    10000      8260
      TABLE ACCESS FULL HJ_ITEMS 1000000     7500
      TABLE ACCESS FULL HJ_ORDERS   1000      760

B: 걸러진 주문을 빌드하는 계획의 예
Operation                       A-Rows   Buffers
SELECT STATEMENT                     3      8260
  SORT GROUP BY                      3      8260
    HASH JOIN                    10000      8260
      TABLE ACCESS FULL HJ_ORDERS   1000      760
      TABLE ACCESS FULL HJ_ITEMS 1000000     7500

위 예에서 부모 연산의 Buffers는 하위 작업을 포함하는 누적 통계이므로 모든 행을 더하면 안 된다. 문장 전체의 논리 읽기를 비교할 때는 최상단 통계를 기준으로 삼고, 어느 접근이 비용을 만드는지 볼 때 하위 행을 읽는다.

같은 전체 스캔에서도 빌드 입력 축소로 임시 작업이 줄어드는 설명용 비교

지표	A	B	해석
빌드 입력 행 수	1,000,000	1,000	해시 테이블에 넣는 행이 줄어든다.
조인 출력 행 수	10,000	10,000	업무 처리 대상은 같다.
문장 논리 읽기	8,260	8,260	양쪽 모두 두 테이블을 전체 스캔한다.
해시 작업의 최대 임시 공간	32MB	0MB	작은 빌드가 메모리에 들어가는 상황을 가정한다.

실제 임시 공간 사용이 없었다면 이 표의 32MB를 실측 결과에 옮겨 적으면 안 된다. 메모리가 충분해 A도 메모리에서 끝났을 수 있다. 반대로 B에서도 다른 정렬이나 집계 연산이 임시 공간을 사용할 수 있으므로 해당 연산을 구분한다.

시간을 비교할 때는 실행 순서도 통제한다. 먼저 실행한 SQL만 저장장치에서 블록을 읽고 나중 SQL은 캐시의 도움을 받을 수 있다. 두 순서를 번갈아 반복하고 논리 읽기, 물리 입출력, 경과 시간과 동시 부하를 기록한다. 이 실습의 1회 실행만으로 운영 배치의 시간 단축률을 계산하지 않는다.

실무에서 자주 틀리는 것

테이블 크기만 보고 빌드 입력을 정한다

다음은 이번 데이터에서 큰 상세를 빌드하도록 유도하는 힌트다. 업무 결과는 맞지만 메모리 사용 관점에서 불리하다.

/*+ leading(o l) use_hash(l) swap_join_inputs(l) */

같은 조인 순서에서 날짜로 걸러진 주문을 빌드하게 비교하려면 다음처럼 요청한다. 실제 계획의 첫 번째 자식이 주문인지 반드시 확인한다.

/*+ leading(o l) use_hash(l) no_swap_join_inputs(l) */

조회 기간을 100일로 늘리면 주문 입력도 커진다. 한 날짜에서 얻은 결론을 모든 기간에 고정하지 말고 입력 규모가 달라지는 대표 조건도 비교한다.

집계 후에 날짜를 판정하면 된다고 생각한다

다음 조건은 선택한 날짜의 주문만 집계하는 의미가 아니다. 여러 날짜의 금액을 합친 뒤 그룹의 가장 이른 날짜를 검사한다.

select o.channel_code, sum(l.quantity * l.unit_price)
from hj_orders o
join hj_items l on l.order_id = o.order_id
group by o.channel_code
having min(o.order_date) = date '2025-01-01';

날짜 조건은 주문 행을 거르는 WHERE 절에 둔다. 필터가 주문 액세스에서 적용됐는지도 계획으로 확인한다.

select o.channel_code, sum(l.quantity * l.unit_price)
from hj_orders o
join hj_items l on l.order_id = o.order_id
where o.order_date >= date '2025-01-01'
  and o.order_date < date '2025-01-02'
group by o.channel_code;

작은 입력을 만든다며 조인 키를 없앤다

상세를 먼저 전부 합치면 한 행이 되지만 어느 주문의 금액인지 사라진다. 다음 SQL은 전체 상세 금액을 선택된 주문 수만큼 더한다.

select o.channel_code, sum(x.amount)
from hj_orders o
cross join (
    select sum(quantity * unit_price) as amount
    from hj_items
) x
where o.order_date >= date '2025-01-01'
  and o.order_date < date '2025-01-02'
group by o.channel_code;

선집계가 필요하다면 주문 번호를 유지해야 한다. 다음 SQL은 결과 의미를 보존하지만 상세 전체를 먼저 집계하는 비용이 있으므로 이번 사례의 개선안보다 빠르다고 단정하지 않는다.

select o.channel_code, sum(x.amount)
from hj_orders o
join (
    select order_id, sum(quantity * unit_price) as amount
    from hj_items
    group by order_id
) x on x.order_id = o.order_id
where o.order_date >= date '2025-01-01'
  and o.order_date < date '2025-01-02'
group by o.channel_code;

예상 계획을 실제 메모리 사용의 증거로 삼는다

다음 명령만으로는 실행 중 실제 처리 행 수나 임시 공간 사용을 알 수 없다.

explain plan for
select count(*)
from hj_orders o
join hj_items l on l.order_id = o.order_id;

select * from table(dbms_xplan.display);

대상 SQL을 통계 수집 옵션으로 실제 실행한 뒤 커서의 실행 통계를 확인한다. 아래 조회는 전체를 끝까지 처리하는 집계이므로 일부 행만 가져온 상태와도 구분된다.

select /*+ gather_plan_statistics */ count(*)
from hj_orders o
join hj_items l on l.order_id = o.order_id;

select *
from table(
    dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')
);

한눈에 보기

해시 조인 튜닝에서 관찰할 항목과 판단 기준

관찰 항목	확인할 내용	이번 사례의 판단
빌드 입력	필터 후 행 수와 전달할 열의 폭	주문 1,000건을 후보로 삼는다.
조건 적용 위치	액세스 조건과 실제 출력 행 수	주문 스캔에서 날짜로 거른다.
프로브 읽기	스캔 행 수와 조인 출력의 차이	상세 100만 건을 읽고 1만 건을 출력한다.
논리 읽기	문장 전체 Buffers	빌드 교환만으로 감소하지 않을 수 있다.
임시 공간	사용 연산과 최대 점유량, 입출력	큰 빌드의 디스크 처리 여부를 본다.
집계 위치	선집계 비용과 조인 후 남는 행 수	선택된 주문과 조인한 뒤 집계한다.

이번에는 명시적인 내부 조인의 두 입력을 조정했다. 다음 장에서는 서브쿼리로 작성된 조건이 어떤 실행 구조로 바뀌는지 살펴본다.

연습 문제

1. A와 B의 문장 전체 Buffers가 같고, B의 임시 공간 사용과 경과 시간만 감소했다. 개선이라고 판단할 수 있는 이유와 추가로 확인할 지표를 설명하라.
2. 조회 기간을 하루에서 전체 100일로 늘렸다. 주문 쪽이 여전히 빌드 입력으로 적절한지 판단하려면 무엇을 확인해야 하는가.
3. 개선 후 주문 액세스의 A-Rows는 1,000, 상세 액세스는 1,000,000, 해시 조인은 10,000이다. 상세를 10,000건만 읽었다는 해석이 잘못된 이유를 설명하라.
4. 상세를 주문별로 선집계할 때 SUM 대신 AVG를 계산했다. 채널별 평균 단가를 구하려고 주문별 평균을 다시 AVG로 합치면 어떤 문제가 생기며 어떻게 수정해야 하는가.

정답과 해설

1. 두 계획이 같은 테이블을 같은 방식으로 스캔하면 논리 읽기가 비슷할 수 있다. 작은 빌드 입력이 임시 공간 기록과 재처리를 줄였다면 별도의 비용이 감소한 것이다. 실제 작업영역 통계, 임시 공간 읽기·쓰기, 물리 읽기, 반복 실행 시간과 동시 부하를 함께 확인한다.
2. 필터 후 주문 수가 1,000건에서 100,000건으로 늘어난다. 상세는 여전히 1,000,000건이지만 행 수만으로 결론을 내리지 않는다. 양쪽에서 필요한 열의 폭, 작업영역에 들어가는지, 실제 임시 공간 사용과 수행 시간을 비교한다. 넓은 기간에서는 선집계의 이익도 별도로 검토할 수 있다.
3. 상세 액세스 연산이 출력한 행은 1,000,000건이다. 그중 선택한 주문에 대응하는 10,000건만 해시 조인을 통과했다. 조인 이후 행 수 감소와 테이블에서 읽은 행 수 감소는 다른 현상이다.
4. 주문마다 상세 건수가 다르면 주문별 평균을 동일한 비중으로 평균 내는 결과가 된다. 상세 행 기준 평균이 필요하면 주문별 단가 합과 단가가 NULL이 아닌 행의 수를 함께 보존하고, 채널별 합계끼리 나눈다. 수량 가중 평균이 목적이라면 금액 합을 수량 합으로 나누는 등 업무 정의에 맞는 분자와 분모를 유지한다.