오라클(Oracle)에서 대용량 테이블의 성능을 최적화하려면 파티션 테이블과 인덱스 활용이 필수입니다.

특히 💡 파티션 인덱스(Local/Global)를 제대로 이해하면,
대용량 데이터에서도 쿼리 속도 저하 없이 안정적으로 운영할 수 있죠.

이 글에서는 👉 Oracle SQL에서 파티션 테이블을 만들고,
그 위에 인덱스를 생성하는 방법(로컬 vs 글로벌)을 예제와 함께
상세하게 정리해드립니다.

🧩 성능 최적화, 실행계획 이해, 유지보수 전략까지 함께 확인해보세요!


1️⃣ 오라클에서 파티션 테이블이란?

  • 파티션 테이블은 큰 테이블을 논리적으로 나누어 저장하는 방식입니다.
  • 오라클에서는 RANGE, LIST, HASH, INTERVAL 등 다양한 파티셔닝 유형을 제공합니다.
  • 주로 날짜, 구간별 ID, 지역코드 등으로 분리하여 관리 성능을 높입니다.

2️⃣ 파티션 테이블 생성 예시 (RANGE 기준)

CREATE TABLE sales (
  sale_id     NUMBER,
  sale_date   DATE,
  amount      NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
  PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);


  • sale_date를 기준으로 연도별 파티션을 나눕니다.
  • 나중에 데이터량이 많아지면 파티션별로 관리/삭제도 가능해 효율적입니다.


3️⃣ 인덱스 생성 방법

Oracle에서는 파티션 테이블 위에 인덱스를 생성할 때
2가지 방식이 있습니다👇

🔹 (1) 로컬 인덱스 (LOCAL INDEX)

각 파티션마다 별도의 인덱스를 가지며, 파티션 드롭/병합에도 영향 적음


CREATE INDEX idx_sales_local 
ON sales(sale_date) LOCAL;


📌 특징

  • 각 파티션별 독립적인 인덱스 구성
  • 관리 용이, 유지보수 편리
  • 실행계획에서 병렬 스캔도 유리


🔸 (2) 글로벌 인덱스 (GLOBAL INDEX)

파티션과 상관없이 전체 테이블을 하나의 인덱스로 관리


CREATE INDEX idx_sales_global 
ON sales(sale_date) GLOBAL;


📌 특징

  • 전체 테이블 기준 하나의 인덱스
  • 범위 쿼리 성능이 좋음
  • 단점: 파티션 삭제 시 인덱스 INVALID될 수 있음 → 재빌드 필요


4️⃣ 로컬 vs 글로벌 인덱스 비교 요약


구분로컬 인덱스 (LOCAL)글로벌 인덱스 (GLOBAL)
인덱스 단위파티션별 별도 인덱스전체 테이블 단일 인덱스
유지보수 편의성좋음파티션 연산 시 불리함
성능병렬 처리 유리범위 조회에 유리
파티션 드롭 시영향 없음INVALID 가능성 있음
사용 예시월별 데이터, 로그 테이블통합 검색 인덱스

5️⃣ 실행계획 확인 방법

인덱스 적용 후 실행계획은 다음처럼 확인할 수 있습니다

EXPLAIN PLAN FOR 
 SELECT * FROM sales 
  WHERE sale_date = TO_DATE('2024-06-01','YYYY-MM-DD'); 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  • INDEX RANGE SCAN, PARTITION RANGE 같은 키워드 확인
  • 로컬 인덱스인 경우, 파티션 단위 스캔이 나올 수 있음


✅ 실무 팁 & 권장 전략

  • 주기적으로 파티션 삭제하거나 병합한다면 → LOCAL INDEX 사용 추천
  • 데이터가 정형적이고 파티션 연산이 적다면 → GLOBAL INDEX도 고려 가능
  • 중요: 파티션 키가 인덱스 키에 포함되지 않으면, 로컬 인덱스 생성 불가

✅ 마무리

Oracle에서 파티션 테이블을 제대로 구성하고 인덱스를 적절히 적용하면
대용량 테이블도 무리 없이 빠르고 안정적인 성능을 낼 수 있습니다.

이번 포스팅이 오라클 SQL 인덱스 설계에 실질적인 도움이 되었길 바랍니다!