DBE 도토리 창고

SQL 튜닝 : 파티션 테이블(RANGE, HASH, LIST) 본문

SQL

SQL 튜닝 : 파티션 테이블(RANGE, HASH, LIST)

도토리팽귄 2025. 8. 6. 13:11

SQL 튜닝은 단순히 쿼리 문법을 최적화하는 것을 넘어, 데이터베이스의 구조와 데이터의 특성을 깊이 이해하는 과정입니다. 특히, 대용량 데이터를 다루는 환경에서는 테이블을 효율적으로 분할하는 파티셔닝(Partitioning) 기법이 필수적입니다. 파티션 테이블을 적재적소에 활용하면 I/O 비용을 획기적으로 줄이고, 쿼리 성능을 비약적으로 향상시킬 수 있습니다.

 

이번 글에서는 파티션 테이블의 종류와 개념을 명확히 정리하고, 실제 오라클 SQL 코드를 통해 각 파티션의 생성 및 활용법을 상세히 알아보겠습니다. 또한, 여러분이 직접 학습하면서 궁금해하셨던 부분들을 복습 문제와 주의사항으로 담아 실무에 바로 적용할 수 있도록 구성했습니다. 

대용량 테이블은 파티션 테이블로 재구성하세요

대용량 테이블을 파티셔닝(Partitioning)하면, 쿼리 조건에 해당하는 파티션만 스캔하는 파티션 프루닝(Partition Pruning) 기능을 통해 성능을 크게 향상시킬 수 있습니다.

 

파티션 테이블로 관리하면 좋은점?

  1. 관리적 측면 : 보관주기가 지난 data 들을 별도로 백업하고 지우는 일이 아주 쉬워진다.
  2. 성능적 측면 : 해당 서랍만 읽으면 되기 때문에 검색 속도도 빨라집니다.

 

실습1. 파티션 테이블 생성

CREATE TABLE emp_partition2
  PARTITION BY RANGE(deptno)
  (
      PARTITION p1 VALUES LESS THAN(20),
      PARTITION p2 VALUES LESS THAN(30),
      PARTITION p3 VALUES LESS THAN(40)
  )
  AS SELECT * FROM emp;

실습2. 파티션 테이블 성능 비교

-- 튜닝 전 (일반 테이블)
SELECT * FROM emp WHERE deptno = 10;

실행 계획 분석: TABLE ACCESS FULL이 발생하며 BUFFERS는 7입니다.

 
SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'ALLSTATS LAST'));
/*
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      3 |00:00:00.01 |       7 |
|* 1 |  TABLE ACCESS FULL  | EMP  |      1 |      3 |      3 |00:00:00.01 |       7 |
*/
 
-- 튜닝 후 (파티션 테이블)
SELECT * FROM emp_partition2 WHERE deptno = 10;

실행 계획 분석: PARTITION RANGE SINGLE 연산을 통해 p1 파티션만 스캔하여 BUFFERS가 2로 줄어듭니다.

SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'ALLSTATS LAST'));
/*
| Id  | Operation             | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |        |      3 |00:00:00.01 |       2 |      1 |
|   1 |  PARTITION RANGE SINGLE|                  |      1 |      3 |      3 |00:00:00.01 |       2 |      1 |
|* 2 |   TABLE ACCESS FULL   | EMP_PARTITION2 |      1 |      3 |      3 |00:00:00.01 |       2 |      1 |
*/

 

 

1. 파티션 테이블의 종류 및 개념

파티션 테이블은 하나의 물리적 테이블을 여러 개의 논리적 테이블(파티션)로 나누어 관리하는 기법입니다. 데이터의 특성과 쿼리 패턴에 따라 적절한 파티션 방식을 선택하는 것이 중요합니다. 오라클에서 제공하는 주요 파티션 테이블의 종류는 다음과 같습니다.

  • 레인지(Range) 파티션: 특정 컬럼의 값 범위를 기준으로 데이터를 나눕니다. 날짜(Date)나 숫자(Number) 컬럼을 파티션 키로 주로 사용합니다. (예: 월별, 연도별 데이터)

  • 해쉬(Hash) 파티션: 파티션 키 컬럼의 해쉬 함수 값을 기준으로 데이터를 균등하게 분산시킵니다. 데이터가 어느 파티션에 들어갈지 예측하기 어려울 때 사용하며, 파티션 간의 데이터 분포를 균등하게 유지하는 데 용이합니다.

  • 리스트(List) 파티션: 파티션 키 컬럼의 특정 값 목록을 기준으로 데이터를 나눕니다. (예: 지역, 부서 코드, 통신사 등)

  • 복합(Composite) 파티션: 파티션을 주 파티션과 보조 파티션과 같이 두 가지 이상의 파티션 기법을 결합하여 사용합니다.
    1. range + hash 파티셔닝
    2. range + list 파티셔닝
    3. range + range 파티셔닝
    4. list + hash 파티셔닝
    5. list + list 파티셔닝
    6. list + range 파티셔닝

2. 레인지(Range) 파티션 테이블

레인지 파티션은 데이터의 범위를 기준으로 파티션을 나눕니다. 시계열 데이터(매일, 매월 발생하는 데이터)를 관리할 때 가장 많이 사용됩니다.

예제 1: hiredate 컬럼으로 emp_partition5 테이블 생성하기

다음은 emp 테이블의 hiredate 컬럼을 기준으로 연도별 레인지 파티션을 생성하는 예제입니다.

 

CREATE TABLE emp_partition5
(	empno   NUMBER(4,0) ,
    ename   VARCHAR2(10),
    job     VARCHAR2(9),
    mgr     NUMBER(4,0),
    hiredate DATE,
    sal     NUMBER(7,2),
    comm    NUMBER(7,2),
    deptno  NUMBER(2,0)
)
PARTITION BY RANGE(hiredate)
(
    PARTITION p1 VALUES LESS THAN( to_date('19810101','yyyyMMDD') ),
    PARTITION p2 VALUES LESS THAN( to_date('19820101','yyyyMMDD') ),
    PARTITION p3 VALUES LESS THAN( to_date('19830101','yyyyMMDD') ),
    PARTITION p4 VALUES LESS THAN( to_date('19840101','yyyyMMDD')),
    PARTITION p5 VALUES LESS THAN( MAXVALUE )
);

설명:

  • PARTITION BY RANGE(hiredate): hiredate 컬럼을 파티션 키로 지정합니다.
  • p1 VALUES LESS THAN(...): 1981-01-01 미만의 모든 데이터는 p1 파티션에 저장됩니다.
  • MAXVALUE: 파티션 키 값의 가장 큰 값으로, 범위에 속하지 않는 모든 데이터를 이 파티션에 저장합니다.

 

예제 2: 파티션별 데이터 분배 확인

테이블에 데이터를 입력한 후, 각 파티션에 데이터가 어떻게 분배되었는지 확인하는 것은 파티션 관리에 있어 필수적인 과정입니다.

-- 1. emp 테이블의 데이터를 emp_partition5에 입력
INSERT INTO emp_partition5
  SELECT * FROM emp;
COMMIT;

-- 2. 통계정보를 수집하여 파티션의 메타데이터를 갱신
EXEC dbms_stats.gather_table_stats('c##scott', 'emp_partition5');

-- 3. 데이터 딕셔너리를 통해 파티션별 행 수(num_rows) 확인
SELECT table_name, partition_name, num_rows
  FROM user_tab_partitions
  WHERE table_name = 'EMP_PARTITION5';

 

예상 출력:

TABLE_NAME           PARTITION_NAME         NUM_ROWS
-------------------- -------------------- ----------
EMP_PARTITION5       P1                            3
EMP_PARTITION5       P2                            7
EMP_PARTITION5       P3                            4
EMP_PARTITION5       P4                            0
EMP_PARTITION5       P5                            0

 

복습 문제: 파티션 튜닝 시 버퍼 값이 오히려 증가한 이유 분석

이전 대화에서 나눴던 orders_partition 테이블의 예시를 통해 레인지 파티션의 중요한 함정을 짚어보겠습니다. 파티션 테이블을 사용했는데도 버퍼 값이 오히려 높게 나온 이유를 분석해 봅시다.

-- 튜닝 전: 일반 테이블 orders
SELECT count(*)
  FROM orders
  WHERE order_date BETWEEN to_date('20250101','YYYYMMDD') AND to_date('20250201','YYYYMMDD');

 

실행 계획 (예상):

----------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows | A-Time   | Buffers |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |      53 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |      53 |
|* 2 |   TABLE ACCESS FULL| ORDERS |      1 |    161 |      1 |00:00:00.01 |      53 |
----------------------------------------------------------------------------------

설명: 일반 테이블 orders는 order_date 컬럼에 인덱스가 없는 경우, 테이블 전체를 스캔(Full Table Scan)하여 조건을 만족하는 행을 찾습니다. 이 과정에서 53개의 버퍼가 사용됩니다.

 
-- 튜닝 후: 파티션 테이블 orders_partition
-- partition p18 values less than (to_date('20250201', 'yyyymmdd'))
-- partition p19 values less than (to_date('20250301', 'yyyymmdd'))
SELECT count(*)
  FROM orders_partition
  WHERE order_date BETWEEN to_date('20250101','YYYYMMDD') AND to_date('20250201','YYYYMMDD');

 

실행 계획 (예상):

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |      1 |        |      1 |00:00:00.01 |      72 |
|   1 |  SORT AGGREGATE             |                    |      1 |      1 |      1 |00:00:00.01 |      72 |
|   2 |   PARTITION RANGE CONCATENATION|                   |      1 |        |      1 |00:00:00.01 |      72 |
|   3 |    PARTITION RANGE SINGLE   |                    |      1 |      1 |      1 |00:00:00.01 |      36 |
|* 4 |     TABLE ACCESS FULL       | ORDERS_PARTITION |      1 |    20 |      1 |00:00:00.01 |      36 |
|   5 |    PARTITION RANGE SINGLE   |                    |      1 |      1 |      0 |00:00:00.01 |      36 |
|* 6 |     TABLE ACCESS FULL       | ORDERS_PARTITION |      1 |      1 |      0 |00:00:00.01 |      36 |
---------------------------------------------------------------------------------------------------------

분석 및 주의사항: 파티션 테이블의 버퍼 값이 더 높게 나온 이유는 BETWEEN 조건이 파티션의 경계선에 걸쳐있기 때문입니다. p18 파티션은 2025-02-01 미만의 데이터를 포함하며, 2025-02-01 데이터는 그 다음 파티션인 p19에 속합니다. 따라서 BETWEEN '20250101' AND '20250201'이라는 쿼리는 p18과 p19 두 파티션을 모두 스캔하게 됩니다. 이처럼 파티션 프루닝(Partition Pruning)이 제대로 동작하지 않으면, 파티션의 장점을 살리지 못하고 오히려 비효율적인 성능을 초래할 수 있습니다.

 

💡파티션 pruning 이란 ?

가지를 치다, 불필요한 부분을 제거한다는 의미로 특정 파티션만 조회하는 기능을 파티션 프루닝이라고 합니다.

 

파티션 pruning 이 안되는 경우 ?  (★ SQLP시험 출제)

  1. WHERE 절의 컬럼이 가공되었을때
  2. 등치 조건이 아닐때 (hash 파티션일 때)

3. 해쉬(Hash) 파티션 테이블

해쉬 파티션은 파티션 키를 기준으로 데이터를 해쉬 함수에 넣어 결과를 계산하고, 그 결과에 따라 데이터를 균등하게 분산시킵니다. 데이터가 특정 범위에 몰려있지 않고 고르게 분포되어야 할 때 유용합니다. 다만, 해쉬 파티션은 만들기는 쉬운데 검색하고자 하는 데이터가 어느 파티션에 있는지 알수 가 없어서 파티션을 삭제하는 관리작업을 하기가 어렵습니다.

예제 1: order_date 컬럼으로 orders_hash 테이블 생성하기

CREATE TABLE orders_hash
( order_id    NUMBER,
  member_id   NUMBER,
  product_id  NUMBER,
  order_date  DATE,
  amount      NUMBER(10,2),
  quantity    NUMBER
)
PARTITION BY HASH(order_date) PARTITIONS 20;

설명:

  • PARTITION BY HASH(order_date) PARTITIONS 20;: order_date를 파티션 키로 사용하여 20개의 파티션으로 데이터를 분산합니다. 파티션의 이름은 자동으로 생성됩니다.

예제 2: 해쉬 파티션의 성능 비교

해쉬 파티션은 특정 키 값으로 검색할 때 파티션 프루닝 효과를 얻을 수 있습니다.

-- 튜닝 전: 일반 테이블 orders
SELECT *
  FROM orders
  WHERE order_date = TO_DATE('2023/08/09', 'RRRR/MM/DD');

실행 계획 (예상):

----------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows | A-Time   | Buffers |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |      53 |
|* 1 |  TABLE ACCESS FULL | ORDERS |      1 |      1 |      1 |00:00:00.01 |      53 |
----------------------------------------------------------------------------------
 
-- 튜닝 후: 해쉬 파티션 테이블 orders_hash
SELECT *
  FROM orders_hash
  WHERE order_date = TO_DATE('2023/08/09', 'RRRR/MM/DD');

실행 계획 (예상):

------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |      1 |        |      1 |00:00:00.01 |      48 |
|   1 |  PARTITION HASH SINGLE|             |      1 |      1 |      1 |00:00:00.01 |      48 |
|* 2 |   TABLE ACCESS FULL   | ORDERS_HASH |      1 |      1 |      1 |00:00:00.01 |      48 |
------------------------------------------------------------------------------------------

설명: orders_hash 테이블은 order_date 컬럼으로 파티셔닝되어 있으므로, WHERE 절에 order_date에 대한 등치 조건이 오면 특정 파티션만 스캔(PARTITION HASH SINGLE)하여 성능을 높입니다.

4. 리스트(List) 파티션 테이블

리스트 파티션은 파티션 키의 특정 값들을 명시적으로 정의하여 파티션을 나눕니다. 데이터의 종류가 제한적일 때 사용하기 좋으며, 특정 그룹별로 데이터를 묶어 관리할 때 효과적입니다.

예제 1: quantity 컬럼으로 orders_list 테이블 생성하기

 
CREATE TABLE orders_list
PARTITION BY LIST(quantity)
(
  PARTITION p1 VALUES(1),
  PARTITION p2 VALUES(2),
  PARTITION p3 VALUES(3),
  PARTITION p4 VALUES(4),
  PARTITION p5 VALUES(5),
  PARTITION p6 VALUES(6),
  PARTITION p7 VALUES(7),
  PARTITION p8 VALUES(8),
  PARTITION p9 VALUES(9)
)
AS
SELECT * FROM orders;

설명: quantity 값이 1인 모든 행은 p1 파티션에, 2인 모든 행은 p2 파티션에 저장됩니다.

복습 문제: emp21_list 테이블 생성

이전에 학습했던 emp 테이블과 유사한 테이블이 있다고 가정하고, telecom 컬럼으로 파티션 테이블을 만들어 봅시다.

CREATE TABLE emp21_list
PARTITION BY LIST(telecom)
(
  PARTITION pkt VALUES('KT'),
  PARTITION pskt VALUES('SKT'),
  PARTITION plg VALUES('LG')
)
AS
SELECT * FROM emp21;

실행 계획: 이 테이블에 WHERE telecom = 'SKT'와 같은 쿼리를 실행하면, 옵티마이저는 pskt 파티션만 스캔하여 Partition Pruning의 효과를 얻습니다.

5. 보너스 튜닝 팁: 조인 순서만 바꿔도 빨라지는 SQL 튜닝

파티션 튜닝 외에도, SQL 튜닝의 기본 원칙 중 하나는 조인 순서를 최적화하는 것입니다. 옵티마이저는 일반적으로 FULL TABLE SCAN보다 INDEX를 타는 것을 선호하며, 조인의 순서를 바꾸는 힌트를 사용해 성능을 개선할 수 있습니다.

예제: emp와 dept 테이블 조인

emp.ename='SCOTT'와 같은 선택성이 높은 조건이 emp 테이블에 있다면, 이 테이블을 먼저 검색하는 것이 효율적입니다.

 
-- 튜닝 전: DEPT 테이블을 먼저 스캔 (비효율)
SELECT /*+ leading(d e) use_nl(e) */ e.ename, d.loc
  FROM emp e, dept d
  WHERE e.deptno = d.deptno AND e.ename='SCOTT';

실행 계획:

 
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      35 |
|   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:00.01 |      35 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |     82 |      4 |00:00:00.01 |       7 |
|* 3 |   TABLE ACCESS FULL| EMP  |      4 |      1 |      1 |00:00:00.01 |      28 |
-------------------------------------------------------------------------------------

설명: DEPT 테이블을 먼저 Full Table Scan 한 후, 찾은 4개의 행마다 EMP 테이블을 다시 스캔합니다. 총 4번의 테이블 스캔이 발생합니다.

-- 튜닝 후: EMP 테이블을 먼저 스캔 (효율적)
SELECT /*+ leading(e d) use_nl(d) */ e.ename, d.loc
  FROM emp e, dept d
  WHERE e.deptno = d.deptno AND e.ename='SCOTT';

실행 계획:

-----------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |      12 |
|   1 |  NESTED LOOPS     |      |      1 |      1 |      1 |00:00:00.01 |      12 |
|* 2 |   TABLE ACCESS FULL| EMP |      1 |      1 |      1 |00:00:00.01 |       7 |
|* 3 |   TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------

설명: EMP 테이블을 먼저 스캔하여 ename='SCOTT'인 단 1개의 행을 찾은 후, 그 행의 deptno로 DEPT 테이블을 한 번만 스캔합니다. 이로 인해 버퍼 값이 35에서 12로 크게 줄어들고, 성능이 향상됩니다.

 

6. 복합(Composite) 파티션 테이블

복합 파티션은 두 가지 파티션 기법을 결합하여 사용하는 방식입니다. 1차 파티션 키로 데이터를 대략적으로 나누고, 2차 서브파티션 키로 그 안의 데이터를 다시 세분화하여, 두 파티션 키를 모두 사용하는 쿼리의 성능을 극대화할 수 있습니다.

예제 1: RANGE-LIST 결합 파티션 테이블 생성하기

다음은 order_date를 기준으로 RANGE 파티션을 나누고, quantity를 기준으로 LIST 서브파티션을 나누는 예제입니다.

 
CREATE TABLE orders_compose_parti
PARTITION BY RANGE(order_date)
SUBPARTITION BY LIST(quantity)
SUBPARTITION TEMPLATE
(
    SUBPARTITION l1 VALUES (1),
    SUBPARTITION l2 VALUES (2),
    SUBPARTITION l3 VALUES (3),
    SUBPARTITION l4 VALUES (4),
    SUBPARTITION l5 VALUES (5),
    SUBPARTITION l6 VALUES (6),
    SUBPARTITION l7 VALUES (7),
    SUBPARTITION l8 VALUES (8),
    SUBPARTITION l9 VALUES (9),
    SUBPARTITION l10 VALUES(DEFAULT)
)
(
    PARTITION p1 VALUES LESS THAN (to_date('20230901','YYYYMMDD') ),
    -- ... (중략) ...
    PARTITION p26 VALUES LESS THAN (maxvalue)
)
AS
SELECT * FROM orders;

설명:

  • PARTITION BY RANGE(order_date): 1차 파티션으로 order_date를 사용합니다.
  • SUBPARTITION BY LIST(quantity): 1차 파티션 내에서 quantity를 기준으로 서브파티션을 나눕니다.
  • SUBPARTITION TEMPLATE: 모든 1차 파티션에 동일한 서브파티션 구조를 적용합니다. 이 템플릿에 따라 quantity 값에 맞는 서브파티션에 데이터가 저장됩니다.

복습 문제: 복합 파티션의 극적인 성능 향상

앞서 학습했던 orders_list 테이블과 orders_compose_parti 테이블을 비교해 봅시다. order_date와 quantity를 동시에 검색하는 쿼리에서 어떤 차이가 있을까요?

 
-- 튜닝 전: ORDERS 테이블에서 조회
SELECT *
FROM orders
WHERE order_date = to_date('2024/07/24','rrrr/MM/DD')
  AND product_id = 109
  AND quantity = 1;

예상 실행 계획 및 출력:

 
--------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |      1 |00:00:00.01 |      53 |
|* 1 |  TABLE ACCESS FULL  | ORDERS  |      1 |      1 |      1 |00:00:00.01 |      53 |
--------------------------------------------------------------------------------------

설명: 인덱스가 없다면 테이블 전체를 스캔하여 53개의 버퍼를 사용합니다.

 
-- 튜닝 후: 복합 파티션 ORDERS_COMPOSE_PARTI에서 조회
SELECT *
FROM orders_compose_parti
WHERE order_date = to_date('2024/07/24','rrrr/MM/DD')
  AND product_id = 109
  AND quantity = 1;

예상 실행 계획 및 출력:

 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  PARTITION RANGE SINGLE              |                      |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   PARTITION LIST SINGLE              |                      |      1 |      1 |      1 |00:00:00.01 |       2 |
|* 3 |    TABLE ACCESS FULL                 | ORDERS_COMPOSE_PARTI |      1 |      1 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------

분석 및 주의사항: orders_compose_parti 테이블은 order_date와 quantity 두 파티션 키를 모두 사용하여 Partition Pruning이 두 단계로 일어납니다. 먼저 order_date 조건으로 2024년 7월 파티션 하나를 찾고, 그 파티션 안에서 quantity = 1 조건으로 해당 서브파티션만 스캔합니다. 결과적으로 버퍼 값이 53에서 2로 획기적으로 줄어들어 성능이 크게 향상됩니다.

7. DBA는 Interval 파티션을 알고 있어야 합니다

INTERVAL 파티션은 RANGE 파티션의 한계를 보완하기 위해 등장했습니다. RANGE 파티션은 새로운 파티션 범위를 미리 수동으로 정의해야 하지만, INTERVAL 파티션은 특정 시점 이후에 데이터가 입력될 때 설정된 간격(Interval)에 따라 파티션이 자동으로 추가되는 기능입니다.

예제: INTERVAL 파티션 테이블 생성 및 자동 생성 확인

다음은 order_date를 기준으로 한 달(1 month) 간격으로 파티션이 자동으로 생성되는 order33 테이블 예제입니다.

CREATE TABLE order33
(
  order_no   NUMBER,
  order_date DATE
)
PARTITION BY RANGE(order_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
    PARTITION p2308 VALUES LESS THAN(to_date('2023/09/01', 'yyyy/mm/dd'))
    -- ... (2024년 1월 이전 파티션들)
    , PARTITION p2312 VALUES LESS THAN(to_date('2024/01/01', 'yyyy/mm/dd'))
);

설명:

  • INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')): 1개월 단위로 파티션을 자동으로 생성하도록 설정합니다.
  • VALUES LESS THAN(...): 미리 정의된 RANGE 파티션입니다. 2024-01-01 이후 데이터가 처음 입력될 때부터 INTERVAL 파티션 기능이 활성화됩니다.

2024년 1월 데이터를 입력하면 2024년 1월 파티션이 자동으로 생성되고, 2024년 2월 데이터를 입력하면 2024년 2월 파티션이 자동으로 생성됩니다.

-- 2024년 2월 데이터 입력
INSERT INTO order33 VALUES(3, to_date('2024/02/02', 'yyyy/mm/dd'));

-- 새로 생성된 파티션 확인
SELECT partition_name, high_value
FROM user_tab_partitions
WHERE table_name = 'ORDER33';

예상 출력:

PARTITION_NAME         HIGH_VALUE
--------------------   ---------------------------
P2308                  TO_DATE(' 2023-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
...
P2312                  TO_DATE(' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_PXXX               TO_DATE(' 2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_PXYX               TO_DATE(' 2024-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

주의사항: INTERVAL 파티션이 자동으로 생성하는 파티션 이름은 SYS_P... 형식입니다. 파티션 이름을 바꾸려면 ALTER TABLE ... RENAME PARTITION 명령어를 사용해야 합니다.

8. 파티션 테이블에 파티션 인덱스를 생성하세요

파티션 테이블은 인덱스도 파티션으로 관리할 수 있습니다. 이를 파티션 인덱스라고 하며, 크게 **로컬(Local)**과 글로벌(Global) 두 가지 종류가 있습니다.

로컬 파티션 인덱스

로컬 파티션 인덱스는 테이블 파티션의 수와 인덱스 파티션의 수가 일치하며, 각 테이블 파티션에 대응하는 인덱스 파티션이 생성됩니다.

  • 장점: 테이블의 파티션을 DROP, TRUNCATE, MERGE해도 해당 파티션에 속한 인덱스만 영향을 받고, 다른 파티션의 인덱스는 USABLE 상태를 유지합니다. 관리가 가장 용이하여 실무에서 가장 많이 사용됩니다.
  • 생성 문법: 인덱스 생성 시 LOCAL 키워드만 추가하면 됩니다.
-- emp21_parti3 테이블에 telecom 컬럼으로 로컬 인덱스 생성
CREATE INDEX emp21_telecom_local_indx ON emp21_parti3(telecom) LOCAL;

-- 'KT' 파티션을 삭제해도 다른 파티션 인덱스는 정상 상태 유지
ALTER TABLE emp21_parti3 DROP PARTITION pkt;

-- 로컬 인덱스 상태 확인
SELECT index_name, partition_name, status
FROM user_ind_partitions
WHERE index_name = 'EMP21_TELECOM_LOCAL_INDX';

예상 출력: pkt 파티션에 대한 인덱스 파티션은 사라지지만, pskt와 plg 파티션 인덱스는 USABLE 상태를 유지합니다.

9. 저장공간을 절약하고 싶다면 글로벌 파티션 인덱스를 생성하세요

글로벌 파티션 인덱스는 테이블 파티션의 수와 인덱스 파티션의 수가 불일치할 수 있습니다. 예를 들어, 테이블은 3개의 리스트 파티션으로 구성되어 있지만, 인덱스는 2개의 해쉬 파티션으로 구성할 수 있습니다.

  • 장점: 인덱스 파티션을 효율적으로 구성하여 저장 공간을 절약할 수 있습니다.
  • 단점: 테이블의 파티션이 DROP되거나 TRUNCATE되면 전체 글로벌 인덱스가 UNUSABLE 상태가 됩니다. 이 상태의 인덱스는 사용할 수 없으므로, 반드시 리빌드해야 하는 관리 부담이 있습니다.

예제: 글로벌 파티션 인덱스 생성 및 리빌드

emp_deptno_list 테이블에 deptno 컬럼으로 글로벌 파티션 인덱스를 생성하는 예제입니다.

 
-- emp_deptno_list 테이블에 deptno 컬럼으로 글로벌 인덱스 생성
CREATE INDEX emp_deptno_list_global
ON emp_deptno_list(deptno) GLOBAL
PARTITION BY HASH(deptno) PARTITIONS 2;

글로벌 인덱스 리빌드(Rebuild) 방법

emp_deptno_list에서 p_10 파티션을 삭제하면 글로벌 인덱스는 UNUSABLE 상태가 됩니다.

 
-- 'p_10' 파티션 삭제
ALTER TABLE emp_deptno_list DROP PARTITION p_10;

-- 글로벌 인덱스 상태 확인 -> 'UNUSABLE' 상태가 됩니다
SELECT index_name, partition_name, status
FROM user_ind_partitions
WHERE index_name= 'EMP_DEPTNO_LIST_GLOBAL';

이전에 나눴던 대화에서 언급되었듯이, 이 상태에서 ALTER INDEX ... REBUILD 명령어를 사용하면 ORA-14086 오류가 발생합니다. 글로벌 인덱스는 전체를 한 번에 리빌드할 수 없기 때문입니다.

정확한 리빌드 명령어는 다음과 같습니다.

-- UNUSABLE 상태인 모든 인덱스 파티션을 리빌드
ALTER INDEX emp_deptno_list_global REBUILD UNUSABLE PARTITIONS;

설명: 이 명령어는 emp_deptno_list_global 인덱스 내의 파티션 중 UNUSABLE 상태인 것들만 선택적으로 리빌드하여, 전체 인덱스를 재구성하지 않고도 문제를 해결할 수 있습니다.

마치며

이번 글에서는 복합 파티션, INTERVAL 파티션, 그리고 파티션 인덱스의 종류(로컬/글로벌)와 관리 방법에 대해 심도 있게 다뤄보았습니다.

  • 복합 파티션은 두 가지 파티션 키를 모두 사용하는 쿼리에서 최상의 성능을 냅니다.
  • INTERVAL 파티션은 DBA의 수동 관리 부담을 덜어주는 자동화된 파티셔닝 기능입니다.
  • 로컬 인덱스는 관리 편의성이 높아 가장 일반적이며, 글로벌 인덱스는 저장 공간 절약의 장점이 있지만 관리 부담이 있습니다.

'SQL' 카테고리의 다른 글

SQL 튜닝 : GROUPING SETS, PARTITION BY, LEAD/LAG, ROWID, BIND VARAIABLE, MERGE, DYNAMIC, PARTITION TABLE  (3) 2025.08.05
SQL 튜닝 : 힌트(NO_MERGE, hash_sj, push_subq, swap_join_inputs, ), Materialized View  (4) 2025.08.04
SQL 튜닝 : 조인 최적화  (7) 2025.07.31
SQL 튜닝 : 인덱스 최적화  (3) 2025.07.31
SQL 튜닝 : 성능 최적화(HAVING절, WHERE절, 암시적 형변환, ORDER BY, INDEX, 검색조건, PARALLEL, 힌트)  (1) 2025.07.30
'SQL' Related Articles more