파티셔닝 - 파티션 프루닝과 로컬 인덱스

이 장에서 배우는 것

앞 장에서 다룬 페이징은 화면에 필요한 행만 가져오는 문제였다. 이번에는 월별 주문 집계가 읽는 저장 영역을 줄인다. 온라인 서점의 주문 테이블에는 여러 해의 주문이 쌓이지만, 운영자가 조회하는 범위는 대개 특정 월이다. 이때 테이블을 월별로 나누는 것만으로 성능 개선이 끝나지는 않는다. SQL의 날짜 조건이 읽을 파티션을 정확히 가리켜야 한다.

이 장에서는 같은 주문 데이터를 일반 테이블과 월별 파티션 테이블에 저장한다. 날짜 컬럼을 가공한 조건과 원래 값을 비교하는 조건을 각각 실행하고, 실행계획의 접근 범위와 논리 읽기를 함께 확인한다. 인덱스 접근의 이점을 섞지 않도록 집계 실험에서는 전체 스캔을 지정한다.

• Range·List·Hash 파티션이 데이터를 나누는 기준과 적합한 사용 목적을 구분한다.
• 파티션 프루닝이 가능한 조건을 작성하고 실행계획에서 실제 접근 범위를 확인한다.
• 로컬 인덱스와 글로벌 인덱스의 검색 범위 및 유일성 보장 차이를 설명한다.
• 월별 주문 집계의 결과를 보존하면서 논리 읽기가 줄었는지 측정한다.

문제 상황

온라인 서점의 운영 화면에는 월별 주문 건수와 주문 금액 합계가 표시된다. 서비스 초기에는 한 달을 조회해도 응답이 빨랐지만, 주문 보관 기간이 늘면서 같은 화면이 점점 느려졌다. 결과는 여전히 한 행인데, 실행계획에는 주문 테이블 전체 스캔이 나타났다. 결과 행 수가 작다는 사실은 입력 데이터도 적게 읽었다는 뜻이 아니다.

기존 SQL은 화면에서 받은 ‘2025-01’을 날짜 컬럼의 문자열 변환 결과와 비교했다. 운영팀은 월별 파티션 테이블을 만들었지만, 이 조건을 그대로 사용한 집계에서는 기대한 만큼 읽기가 줄지 않았다. 월별로 나눈 저장 영역과 SQL이 표현하는 검색 범위가 제대로 연결되지 않은 것이다.

SELECT COUNT(*), SUM(order_total)
FROM p09_ord_month
WHERE TO_CHAR(order_date, 'YYYY-MM') = '2025-01';

재현 데이터는 2024년 1월부터 2025년 12월까지 24개월이며, 월마다 5,000건씩 총 120,000건이다. 실제 운영 규모를 그대로 복제하기보다 읽기 범위의 차이가 드러나는 크기로 줄였다. 각 행에는 200바이트의 부가 문자열을 넣어 테이블 블록 접근 비용을 관찰한다. 집계는 취소 여부와 무관한 전체 주문 발생 금액을 대상으로 한다.

비교 대상은 일반 테이블의 날짜 범위 조회, 파티션 테이블의 문자열 변환 조회, 파티션 테이블의 날짜 범위 조회다. 세 SQL은 모두 5,000건과 317,502,500이라는 같은 결과를 반환해야 한다. 성능 수치를 보기 전에 이 결과가 유지되는지부터 검증한다.

데이터를 나누는 기준과 읽기를 줄이는 원리

파티셔닝은 하나의 논리적 테이블을 여러 저장 단위로 나누는 설계다. 파티션 프루닝(partition pruning)은 조건에 맞을 수 없는 파티션을 실행 대상에서 제외하는 동작이다. 전자는 저장 구조이고 후자는 실행 시 읽기 범위를 줄이는 효과다. 파티션을 만들었다고 모든 SQL에서 프루닝이 일어나지는 않는다.

파티션 방식은 데이터의 분포보다 먼저 조회와 관리 목적에 맞춰 선택한다

방식	분할 기준	온라인 서점의 예	주의점
Range	키 값의 연속 구간	주문일 기준 월별 주문	기간 조회에 적합하며 상한은 포함하지 않는다
List	지정한 값의 집합	국내·해외 판매 채널	신규 값의 수용 정책과 값별 편중을 고려한다
Hash	키의 해시 결과	고객 번호에 따른 데이터 분산	키 동등 조건에는 유용하지만 날짜 범위가 자연스럽게 모이지 않는다

Range 파티션의 VALUES LESS THAN은 상한 미만을 뜻한다. 2025년 1월 파티션의 상한은 DATE '2025-02-01'이다. 앞 파티션의 상한이 DATE '2025-01-01'이라면 해당 파티션에는 1월 1일 이상, 2월 1일 미만의 값이 들어간다. Oracle의 DATE에는 시각도 저장되므로 월말 날짜의 자정만 상한으로 잡으면 그날 낮과 저녁의 주문을 놓친다.

첫 번째 Range 파티션에는 이전 파티션이 없으므로 하한도 따로 없다. 예제의 P202401은 이름과 달리 2024년 1월보다 오래된 값도 수용한다. 이번 데이터는 2024년 1월부터 생성하므로 문제가 없지만, 이름이 데이터 유효성 규칙을 대신하지는 않는다. 마지막 P_FUTURE는 앞선 상한 이후의 값을 수용한다. 이 파티션을 두어도 새 월의 전용 파티션이 자동으로 생기지는 않는다.

List는 값의 의미에 따라 관리 단위를 나눌 때 적합하다. 반면 주문 상태처럼 자주 바뀌는 값을 파티션 키로 삼으면 상태 변경에 따른 행 이동까지 고려해야 한다. Hash는 값별 분산을 목적으로 선택한다. 고객 번호를 Hash 키로 정해도 많이 주문한 고객 하나의 데이터는 같은 파티션에 모이므로, 업무 부하까지 고르게 나뉜다고 단정할 수 없다.

이 사례에서 필요한 것은 주문일 기준 월별 Range 파티션이다. 전체 보관 기간이 늘어도 한 달 집계가 읽는 데이터는 해당 월의 크기에 가깝게 유지된다. 다만 한 달의 주문량 자체가 늘면 그 월의 읽기도 늘어난다. 프루닝은 필요한 데이터 내부의 처리 비용까지 없애는 기능은 아니다.

프루닝을 결정하는 조건과 실행계획

가장 명확한 조건은 파티션 키를 그대로 두고 자료형이 맞는 경계를 비교하는 것이다. 시작 시각은 포함하고 다음 기간의 시작 시각은 제외한다. 이 형태는 월 길이와 시각의 유무에 영향을 받지 않으며 연속한 두 달의 경계도 겹치지 않는다.

WHERE order_date >= DATE '2025-01-01'
  AND order_date <  DATE '2025-02-01'

리터럴 경계를 사용하면 최적화 과정에서 대상 파티션이 정해지는 정적 프루닝(static pruning)을 기대할 수 있다. 바인드 값처럼 실행할 때 경계가 정해지는 경우에는 동적 프루닝(dynamic pruning)이 가능하다. 바인드를 사용했다는 이유만으로 프루닝이 안 된다고 판단해서는 안 된다.

반대로 TO_CHAR(order_date, 'YYYY-MM')처럼 파티션 키에 함수를 적용하면 원래 날짜 구간과의 대응을 옵티마이저가 직접 이용하기 어려워진다. 이 사례에서는 모든 파티션을 읽고 문자열 조건을 검사하는 계획이 비교 대상이다. 함수 종류와 파티션 정의에 따라 변환 가능성이 다르므로, 모든 함수가 항상 프루닝을 막는다는 규칙으로 일반화하지 않는다.

조건의 모양과 실제 파티션 접근 범위를 함께 확인한다

조건	예상되는 동작	확인할 항목
날짜 리터럴의 반열린 구간	정적 프루닝 가능	Pstart·Pstop의 파티션 위치
DATE 바인드의 범위 비교	실행 시 프루닝 가능	KEY 표시와 실제 Buffers
날짜 키의 TO_CHAR 변환	전체 파티션 접근 가능	PARTITION RANGE ALL과 필터 조건
고객 번호 조건만 존재	주문일로 범위를 제한하기 어려움	여러 로컬 인덱스 파티션의 Starts
월 범위 OR 다른 독립 조건	월 밖의 행도 필요할 수 있음	최종 계획에서 각 분기의 접근 범위

실행계획의 Pstart와 Pstop은 접근할 파티션의 시작과 끝을 보여 준다. 숫자는 파티션 이름의 월이 아니라 위치다. 예제에서 2025년 1월은 열세 번째 파티션이다. 두 값이 13이면 그 파티션만 읽는 계획으로 해석한다. KEY는 실행 시 계산되는 범위를 나타내므로 실패 표시가 아니다.

PARTITION RANGE SINGLE 아래에 TABLE ACCESS FULL이 있어도 테이블 전체를 읽는다는 뜻은 아니다. 선택된 파티션 안에서는 전체 스캔을 하고, 다른 파티션은 제외할 수 있다. 이 사례처럼 한 달의 모든 주문을 합산할 때는 해당 월 파티션의 전체 스캔이 합리적인 접근이 될 수 있다.

판정에는 실제 실행 통계가 필요하다. 예상 행 수만 보는 EXPLAIN PLAN 대신 SQL을 끝까지 실행한 다음 DBMS_XPLAN.DISPLAY_CURSOR로 통계를 조회한다. 루트 연산의 Buffers를 전체 논리 읽기 비교값으로 삼으며, 부모와 자식 연산의 Buffers를 모두 더하지 않는다. 상위 연산에 하위 작업이 반영될 수 있어 중복 계산이 되기 때문이다.

로컬 인덱스와 글로벌 인덱스의 선택

로컬 인덱스(local index)는 테이블 파티션에 대응하는 인덱스 파티션으로 나뉜다. 글로벌 인덱스(global index)는 테이블 파티션 경계와 독립된 범위를 인덱싱한다. 글로벌 인덱스는 비파티션 인덱스일 수도 있고 별도로 파티션된 인덱스일 수도 있다. ‘글로벌’과 ‘비파티션’은 같은 뜻이 아니다.

예제에는 고객 번호와 주문일 순서의 로컬 인덱스를 만든다. 주문일 조건으로 월을 제한하고 고객 번호로 찾는 화면에 사용할 수 있는 구조다. 파티션 키가 인덱스의 첫 컬럼이 아니어도 테이블의 파티션 경계와 대응 관계는 유지된다. 다만 날짜 조건 없이 고객 번호만 조회하면 여러 인덱스 파티션을 탐색할 수 있다.

주문 번호 하나로 주문을 찾는 요청은 다른 판단이 필요하다. 이 요청에는 주문일이 없을 수 있다. 예제는 order_id의 유일성을 글로벌 비파티션 인덱스로 보장한다. 날짜를 몰라도 주문 번호를 인덱스에서 찾을 수 있지만, 파티션 삭제와 같은 유지보수에서는 글로벌 인덱스 관리도 검토해야 한다. 유지 방식은 작업 종류와 옵션에 따라 달라지므로 파티션을 지우면 항상 사용 불능이 된다고 단정하지 않는다.

Oracle에서 로컬 유일 인덱스를 만들려면 테이블의 파티션 키가 인덱스 키에 포함되어야 한다. 따라서 order_id만으로 로컬 유일 인덱스를 만드는 것은 이 설계에 맞지 않는다. order_date를 추가하면 생성 가능한 구조가 되지만, 보장하는 것은 두 컬럼 조합의 유일성이다. 서로 다른 날짜에 같은 주문 번호를 넣지 못하게 하는 업무 규칙과는 다르다.

Oracle 19c와 MySQL 8.0은 파티션 인덱스와 제약 조건에서 설계 선택지가 다르다

항목	Oracle 19c	MySQL 8.0
월별 날짜 구간	DATE 키의 RANGE 사용	DATE·DATETIME 키의 RANGE COLUMNS 사용 가능
인덱스 범위	로컬 및 글로벌 인덱스 선택 가능	InnoDB 파티션 테이블에 Oracle식 글로벌 인덱스가 없음
유일성 제약	글로벌 인덱스로 주문 번호 단독 유일성 보장 가능	모든 유일 키가 파티션 표현식에 쓰이는 컬럼을 포함해야 함
실행계획과 읽기	Pstart·Pstop 및 실제 Buffers 확인	EXPLAIN의 partitions로 범위 확인. Oracle Buffers와 같은 지표가 그대로 제공되지는 않음

따라서 Oracle 예제의 글로벌 유일 인덱스를 MySQL로 그대로 옮길 수는 없다. 월별 파티셔닝과 주문 번호 단독 유일성이 모두 필요한 경우에는 스키마 설계를 다시 검토해야 한다. 또한 MySQL의 실행계획 출력에서 Oracle의 논리 읽기 수치를 그대로 찾으려 해서는 안 된다. 제품 간 숫자 비교보다 각 제품에서 읽는 파티션과 실제 작업량이 줄었는지를 확인한다.

사실 확인에는 Oracle 19c 파티션 프루닝 문서와 MySQL 8.0 파티션 키와 유일 키 제한 문서를 참고할 수 있다. 아래 실험 데이터와 SQL은 이 사례를 위해 작성한 것이다.

완성 코드

macOS 또는 Linux의 SQL*Plus 클라이언트에서 Oracle 19c 데이터베이스에 접속해 실행하는 완전한 SQL 스크립트다. macOS에서는 접속 대상 데이터베이스를 별도 서버나 가상 환경에 둔다. 파티셔닝을 사용할 수 있는 데이터베이스와 해당 사용 권한이 전제다.

실습 계정에는 테이블·인덱스 생성 권한과 테이블스페이스 할당량이 필요하다. 실행 통계 조회를 위해 V_$SQL, V_$SQL_PLAN, V_$SQL_PLAN_STATISTICS_ALL, V_$SESSION에 대한 조회 권한도 필요하다. 해당 권한은 관리자가 준비한다. 코드는 P09_ORD_BASE와 P09_ORD_MONTH를 삭제하고 다시 만들므로 전용 실습 스키마에서 실행한다.

아래 내용을 partition_case.sql로 저장한다. 저장 프로시저를 생성하지 않으며, 익명 PL/SQL 블록은 실행 시 컴파일된다. 이 원고에서는 Oracle 인스턴스를 실행하지 않았으므로 실제 컴파일·측정 완료를 주장하지 않는다. 스크립트는 SQL 오류와 결과 불일치가 발생하면 실패 상태로 종료하도록 작성했다.

WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
SET ECHO OFF VERIFY OFF FEEDBACK OFF HEADING OFF
SET PAGESIZE 0 LINESIZE 220 TRIMSPOOL ON TAB OFF
SET TERMOUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED

ALTER SESSION SET NLS_CALENDAR = 'GREGORIAN';

-- [01] 기존 실습 객체 정리
BEGIN
  FOR r IN (
    SELECT table_name
    FROM user_tables
    WHERE table_name IN ('P09_ORD_BASE', 'P09_ORD_MONTH')
  ) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE ' || r.table_name || ' PURGE';
  END LOOP;
END;
/

-- [02] 일반 테이블과 원본 데이터
CREATE TABLE p09_ord_base (
  order_id     NUMBER(12) NOT NULL,
  order_date   DATE NOT NULL,
  customer_id  NUMBER(10) NOT NULL,
  order_total  NUMBER(12) NOT NULL,
  note         VARCHAR2(200) NOT NULL
);

INSERT INTO p09_ord_base
SELECT LEVEL,
       ADD_MONTHS(DATE '2024-01-01', TRUNC((LEVEL - 1) / 5000))
         + MOD(LEVEL - 1, 28),
       MOD(LEVEL - 1, 20000) + 1,
       MOD(LEVEL, 90000) + 1000,
       RPAD('x', 200, 'x')
FROM dual
CONNECT BY LEVEL <= 120000;

-- [03] 월별 Range 파티션 생성
DECLARE
  v_ddl VARCHAR2(32767);
BEGIN
  v_ddl :=
    'CREATE TABLE p09_ord_month (' ||
    'order_id NUMBER(12) NOT NULL,' ||
    'order_date DATE NOT NULL,' ||
    'customer_id NUMBER(10) NOT NULL,' ||
    'order_total NUMBER(12) NOT NULL,' ||
    'note VARCHAR2(200) NOT NULL)' ||
    ' PARTITION BY RANGE (order_date) (';

  FOR i IN 0..23 LOOP
    IF i > 0 THEN
      v_ddl := v_ddl || ',';
    END IF;
    v_ddl := v_ddl || 'PARTITION p' ||
      TO_CHAR(ADD_MONTHS(DATE '2024-01-01', i), 'YYYYMM') ||
      ' VALUES LESS THAN (DATE ''' ||
      TO_CHAR(ADD_MONTHS(DATE '2024-01-01', i + 1), 'YYYY-MM-DD') ||
      ''')';
  END LOOP;

  v_ddl := v_ddl ||
    ', PARTITION p_future VALUES LESS THAN (MAXVALUE))';
  EXECUTE IMMEDIATE v_ddl;
END;
/

INSERT INTO p09_ord_month
SELECT * FROM p09_ord_base;
COMMIT;

-- [04] 주문 번호의 유일성과 고객별 검색 경로
CREATE UNIQUE INDEX p09_base_id
  ON p09_ord_base(order_id);

CREATE UNIQUE INDEX p09_month_id
  ON p09_ord_month(order_id);

CREATE INDEX p09_month_cust_lx
  ON p09_ord_month(customer_id, order_date) LOCAL;

-- [05] 테이블과 파티션, 인덱스 통계 수집
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER, tabname => 'P09_ORD_BASE',
    estimate_percent => 100, degree => 1,
    method_opt => 'FOR ALL COLUMNS SIZE 1', cascade => TRUE);

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

-- [06] 실제 통계와 실행계획은 별도 파일에 기록
SPOOL partition-plan.txt REPLACE
DECLARE
  v_base   NUMBER;
  v_func   NUMBER;
  v_range  NUMBER;

  PROCEDURE run_case(
    p_name IN VARCHAR2, p_sql IN VARCHAR2, p_buffers OUT NUMBER
  ) IS
    v_count NUMBER;
    v_total NUMBER;
    v_sql_id VARCHAR2(13);
    v_child NUMBER;
  BEGIN
    EXECUTE IMMEDIATE p_sql INTO v_count, v_total;

    IF v_count != 5000 OR v_total IS NULL OR v_total != 317502500 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Unexpected aggregate result');
    END IF;

    SELECT sql_id, child_number
    INTO v_sql_id, v_child
    FROM v$sql
    WHERE sql_text = p_sql
      AND parsing_schema_name = USER
    ORDER BY last_active_time DESC, child_number DESC
    FETCH FIRST 1 ROW ONLY;

    SELECT last_cr_buffer_gets + last_cu_buffer_gets
    INTO p_buffers
    FROM v$sql_plan_statistics_all
    WHERE sql_id = v_sql_id
      AND child_number = v_child
      AND id = 0;

    IF p_buffers IS NULL OR p_buffers <= 0 THEN
      RAISE_APPLICATION_ERROR(-20002, 'Missing buffer statistics');
    END IF;

    DBMS_OUTPUT.PUT_LINE(p_name || ': rows=' ||
      TO_CHAR(v_count, 'FM999999999990') || ', total=' ||
      TO_CHAR(v_total, 'FM999999999990') || ', buffers=' ||
      TO_CHAR(p_buffers, 'FM999999999990'));

    FOR r IN (
      SELECT plan_table_output
      FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
        v_sql_id, v_child, 'ALLSTATS LAST +PARTITION'))
    ) LOOP
      DBMS_OUTPUT.PUT_LINE(r.plan_table_output);
    END LOOP;
  END;
BEGIN
  -- [07] 일반 테이블: 날짜 범위가 있어도 테이블 전체 스캔
  run_case('BASE', q'[SELECT /*+ gather_plan_statistics full(o) no_parallel(o) */ /* P09_BASE */
COUNT(*), SUM(o.order_total)
FROM p09_ord_base o
WHERE o.order_date >= DATE '2025-01-01'
  AND o.order_date < DATE '2025-02-01']', v_base);

  -- [08] 파티션 테이블: 키를 문자열로 가공
  run_case('FUNCTION', q'[SELECT /*+ gather_plan_statistics full(o) no_parallel(o) */ /* P09_FUNCTION */
COUNT(*), SUM(o.order_total)
FROM p09_ord_month o
WHERE TO_CHAR(o.order_date, 'YYYY-MM') = '2025-01']', v_func);

  -- [09] 파티션 테이블: 키를 그대로 비교
  run_case('RANGE', q'[SELECT /*+ gather_plan_statistics full(o) no_parallel(o) */ /* P09_RANGE */
COUNT(*), SUM(o.order_total)
FROM p09_ord_month o
WHERE o.order_date >= DATE '2025-01-01'
  AND o.order_date < DATE '2025-02-01']', v_range);

  IF v_range >= v_base OR v_range >= v_func THEN
    RAISE_APPLICATION_ERROR(-20003, 'Inspect pruning and buffer statistics');
  END IF;
END;
/
SPOOL OFF

-- [10] 검증에 성공한 경우에만 고정 요약 출력
SET TERMOUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE('PASS: all cases returned 5000 / 317502500');
  DBMS_OUTPUT.PUT_LINE('PASS: RANGE buffers < BASE and FUNCTION buffers');
  DBMS_OUTPUT.PUT_LINE('DETAIL: partition-plan.txt');
END;
/
EXIT SUCCESS

줄별 해설

[01]은 실습용 테이블 두 개만 정리한다. 테이블을 삭제하면 그 테이블에 속한 인덱스도 삭제되므로 재실행 시 인덱스 이름이 충돌하지 않는다. DDL은 암묵적으로 커밋되므로 실패 시의 ROLLBACK이 객체 삭제까지 되돌리지는 않는다.

[02]는 연속한 5,000건을 한 달에 배정한다. 날짜의 일수는 1일부터 28일까지 반복하므로 모든 월에서 유효하다. 월별 데이터 크기를 일정하게 만들어 보관 기간과 조회 기간의 차이를 관찰하기 쉽게 한다. 실제 운영에서는 성수기와 비수기의 파티션 크기가 다를 수 있다.

[03]은 24개의 월별 파티션과 하나의 미래 파티션을 생성한다. 세션의 달력은 Gregorian으로 지정하여 ADD_MONTHS와 날짜 문자열 생성의 기준을 맞춘다. DDL에 들어가는 이름과 경계는 코드가 만든 값이며 외부 입력을 연결하지 않는다.

[04]의 p09_month_id에는 LOCAL이 없다. 이 인덱스는 여러 테이블 파티션에 걸쳐 주문 번호를 인덱싱하는 글로벌 비파티션 인덱스다. p09_month_cust_lx는 LOCAL을 지정하므로 테이블 파티션과 대응한다. 이후 집계에는 FULL 힌트를 사용하므로 두 인덱스의 검색 이점은 측정에 포함하지 않는다.

[05]는 일반 테이블뿐 아니라 파티션 단위와 전체 테이블의 통계를 수집한다. 파티션을 잘 나누어도 추정에 필요한 통계가 없거나 오래되면 다른 조회의 접근 경로 선택이 달라질 수 있다. 여기서는 병렬 통계 수집과 히스토그램의 영향을 줄여 비교 조건을 단순하게 했다.

[06]의 run_case는 집계를 끝까지 실행하고 결과를 검사한 뒤, 방금 사용한 SQL의 커서를 찾는다. SQL 본문을 정확히 비교하고 SQL 식별자와 자식 커서 번호를 함께 사용하므로 다른 SQL의 계획을 읽을 가능성을 줄인다. 동일 스키마에서 같은 실험을 동시에 실행하지 않는 조건으로 사용한다.

논리 읽기는 루트 연산의 일관성 읽기와 현재 모드 읽기를 더해 얻는다. 이 합계가 비교용 Buffers다. 집계가 반환하는 행은 하나이지만 내부적으로 검사한 행 수는 별개다. 출력 파일의 A-Rows와 조건 정보를 같이 읽어야 스캔 입력과 최종 집계를 구분할 수 있다.

[07]부터 [09]까지는 집계 대상과 결과를 고정하고 저장 구조와 조건 표현만 바꾼다. GATHER_PLAN_STATISTICS는 실제 행 소스 통계를 수집하며 NO_PARALLEL은 병렬 처리 차이를 배제한다. [10]은 세 결과가 일치하고 날짜 범위 조회의 논리 읽기가 나머지 둘보다 작을 때만 출력된다. 기대와 다르면 성공 문구 대신 오류가 발생하므로 계획을 다시 확인한다.

실행 결과

터미널에서 다음과 같이 실행한다. 접속 문자열의 BOOKSHOP은 환경에 등록된 서비스 이름으로 바꾼다. 비밀번호는 명령행에 넣지 않고 접속 프롬프트에서 입력한다.

sqlplus -s bookstore@BOOKSHOP @partition_case.sql

접속 프롬프트를 제외하면 검증 성공 시 표준 출력은 다음 세 줄이다. SQL*Plus 시작 스크립트가 별도 문구를 출력하지 않는 환경을 기준으로 한다.

PASS: all cases returned 5000 / 317502500
PASS: RANGE buffers < BASE and FUNCTION buffers
DETAIL: partition-plan.txt

실제 실행계획과 논리 읽기는 현재 디렉터리의 partition-plan.txt에 기록된다. 수치는 블록 크기, 저장 공간 배치, 데이터베이스 설정 등에 따라 달라지므로 고정 예상 출력으로 제시하지 않는다. 다음 표의 숫자는 비교 방법을 설명하기 위한 가정값이며, 위 코드를 실행해서 얻은 측정값이 아니다. 실습에서는 파일에 기록된 값으로 교체한다.

설명용 가정값에서는 한 달로 접근 범위를 줄일 때 논리 읽기도 줄어든다

실험	주요 계획 형태	Pstart·Pstop	Buffers 가정값
BASE	TABLE ACCESS FULL	해당 없음	4,200
FUNCTION	PARTITION RANGE ALL → TABLE ACCESS FULL	1·25	4,320
RANGE	PARTITION RANGE SINGLE → TABLE ACCESS FULL	13·13	180

이 가정값에서 FUNCTION 대비 RANGE의 감소율은 (4,320−180)÷4,320으로 약 95.8%다. 실제 감소율을 구할 때도 동일한 두 실험의 루트 Buffers를 사용한다. 24개월 중 한 달만 읽더라도 세그먼트 관리 블록 등의 영향 때문에 정확히 24분의 1이 된다고 요구하지 않는다.

파일에서 RANGE의 접근 범위가 13·13인지, 세 집계 결과가 일치하는지, Buffers가 줄었는지를 함께 확인한다. FUNCTION이 예상과 다르게 프루닝된다면 실제 계획을 기준으로 해석한다. 계획 형태를 원고에 맞추려고 측정값을 바꾸어서는 안 된다.

버퍼 캐시에 데이터가 남아 있으면 물리 읽기와 경과 시간은 실행 순서에 영향을 받는다. 논리 읽기 감소는 이 영향과 구분하여 해석할 수 있다. 운영 적용 판단에서는 같은 바인드와 비슷한 부하 조건에서 응답 시간도 반복 측정한다. 논리 읽기가 감소했다는 사실만으로 동시 사용자 환경의 응답 시간 감소율까지 같다고 보지는 않는다.

실무에서 자주 틀리는 것

월말 자정을 한 달의 끝으로 사용한다

다음 조건은 1월 31일 자정 이후의 주문을 제외한다. 월별 집계 경계는 다음 달 시작 미만으로 표현한다.

-- 잘못된 조건
WHERE order_date BETWEEN DATE '2025-01-01' AND DATE '2025-01-31'

-- 고친 조건
WHERE order_date >= DATE '2025-01-01'
  AND order_date <  DATE '2025-02-01'

화면의 문자열 형식에 맞추어 파티션 키를 변환한다

변환이 필요하면 비교값 쪽에서 수행한다. 아래의 :month_text에는 검증된 YYYY-MM 문자열을 전달한다. 실제 애플리케이션에서는 시작일과 다음 달 시작일을 DATE 바인드로 전달하는 방식이 더 명확하다. 변환한 경계를 사용하더라도 최종 프루닝 여부는 실행계획으로 확인한다.

-- 읽을 파티션을 좁히기 어려운 조건
WHERE TO_CHAR(order_date, 'YYYY-MM') = :month_text

-- 키를 그대로 두는 조건
WHERE order_date >= TO_DATE(:month_text || '-01', 'FXYYYY-MM-DD')
  AND order_date < ADD_MONTHS(
        TO_DATE(:month_text || '-01', 'FXYYYY-MM-DD'), 1)

주문 번호 단독 유일성을 로컬 인덱스로 만들려 한다

주문일로 파티션한 테이블에서 아래 첫 구문은 로컬 유일 인덱스의 요건을 충족하지 못한다. 주문 번호만으로 전체 주문의 유일성을 보장하려면 예제처럼 글로벌 인덱스를 사용한다. 아래 구문은 완성 코드에 추가 실행하는 코드가 아니라 두 설계의 비교다.

-- 파티션 키가 빠진 로컬 유일 인덱스
CREATE UNIQUE INDEX p09_order_uq
ON p09_ord_month(order_id) LOCAL;

-- 주문 번호 단독 유일성을 유지하는 글로벌 인덱스
CREATE UNIQUE INDEX p09_order_uq
ON p09_ord_month(order_id);

로컬 인덱스를 만들면 고객 검색도 한 달만 읽는다고 생각한다

고객 번호만으로는 주문일 파티션을 선택할 수 없다. 화면의 요구가 특정 월의 고객 주문이라면 월 조건도 전달해야 한다. 전체 기간의 주문이 필요하다면 날짜 조건을 임의로 추가해서는 안 되며, 여러 파티션 탐색 비용과 글로벌 인덱스의 필요성을 검토한다.

-- 월별 화면인데 기간 조건이 빠진 조회
SELECT order_id, order_date
FROM p09_ord_month
WHERE customer_id = :customer_id;

-- 화면에서 지정한 월의 범위를 반영한 조회
SELECT order_id, order_date
FROM p09_ord_month
WHERE customer_id = :customer_id
  AND order_date >= :month_start
  AND order_date <  :next_month_start;

한눈에 보기

파티션 튜닝은 저장 경계와 SQL 조건, 실제 읽기 통계를 연결하는 작업이다

판단 대상	선택 기준	검증 근거
월별 주문 저장	주문일 Range 파티션	상한 날짜와 실제 데이터 범위
월별 집계 조건	시작 이상·다음 달 시작 미만	Pstart·Pstop과 조건 정보
고객의 월별 주문 검색	날짜 조건과 고객 번호 로컬 인덱스	접근한 인덱스 파티션과 Starts
주문 번호 단독 검색·유일성	글로벌 인덱스 검토	날짜 없는 검색 비용과 업무 규칙
튜닝 효과	동일 결과에서 불필요한 영역 제외	루트 Buffers 및 반복 응답 시간

이번 사례의 변경점은 읽을 필요가 없는 월을 제외하도록 조건을 작성한 것이다. 운영 테이블의 전환에는 데이터 이관, 기존 제약 조건, 애플리케이션 의존성, 파티셔닝 사용 조건을 별도로 검토해야 한다. 다음 장에서는 읽기 범위를 줄이는 문제에서 더 나아가, 대량 변경 작업 자체와 인덱스 유지 비용을 다룬다.

연습 문제

1. RANGE 실험의 기간을 2025년 1월부터 3월까지로 바꾸었다. 날짜 조건과 예상 Pstart·Pstop을 작성하라. 실행계획에 TABLE ACCESS FULL이 남아 있어도 개선일 수 있는 이유를 설명하라.
2. order_date에 함수가 없고 DATE 바인드로 기간을 전달했지만 Pstart·Pstop에 KEY가 나타났다. 이를 프루닝 실패로 볼 수 있는지 설명하고 추가로 확인할 통계를 제시하라.
3. 로컬 유일 인덱스의 키를 (order_id, order_date)로 만들자는 제안이 나왔다. 이것이 order_id 단독 유일성을 대체하는지 예를 들어 설명하라.
4. 특정 월의 FUNCTION 실행은 8,640 Buffers, RANGE 실행은 720 Buffers였다. 감소율을 계산하고, 이 결과만으로 응답 시간도 같은 비율로 줄었다고 말할 수 있는지 설명하라.

정답과 해설

1. 조건은 order_date >= DATE '2025-01-01' AND order_date < DATE '2025-04-01'이다. 예제의 파티션 순서에서는 Pstart가 13, Pstop이 15인 범위를 기대한다. 세 파티션 안의 전체 스캔은 필요한 기간의 모든 주문을 집계하기 위한 접근이며, 나머지 월을 제외했다면 전체 테이블 스캔보다 읽기가 줄 수 있다.
2. KEY만으로 실패라고 판단할 수 없다. 바인드 값으로 실행 시 접근 범위를 결정할 수 있다. 실제 실행 후 Buffers, Starts, A-Rows와 조건 정보를 확인하고, 기간을 바꾸었을 때 읽기 범위와 작업량이 합리적으로 달라지는지 비교한다.
3. 대체하지 않는다. 주문 번호 100에 대해 주문일이 1월 10일인 행과 2월 10일인 행은 서로 다른 조합이다. 복합 유일 인덱스는 두 행을 허용한다. 전체 기간에서 주문 번호 하나만 허용해야 한다면 그 규칙을 유지하는 글로벌 유일 인덱스 등의 설계가 필요하다.
4. 감소율은 (8,640−720)÷8,640으로 약 91.7%다. 이는 논리 읽기 감소율이다. 응답 시간에는 CPU 처리, 물리 읽기, 동시 실행 부하 등도 영향을 주므로 같은 비율의 시간 감소를 단정할 수 없다. 동일 결과를 확인한 뒤 비슷한 조건에서 경과 시간을 별도로 측정한다.