Oracle - 인덱스 생성과 파티션 테이블 ONLINE REBUILD 정리

2026-04-25

Oracle 인덱스 생성과 파티션 테이블 ONLINE REBUILD 정리

오라클에서 일반 테이블과 파티션 테이블의 인덱스를 생성하는 방법, 운영 중인 테이블에서 ONLINE 으로 인덱스를 생성하거나 REBUILD 하는 방법을 한 번에 정리한다.

실무에서 인덱스 작업은 단순히 CREATE INDEX 만 아는 것으로 끝나지 않는다.
특히 대용량 테이블이나 파티션 테이블에서는 LOCAL 인덱스를 만들지, GLOBAL 인덱스를 만들지, 운영 중 서비스에 영향 없이 ONLINE 으로 처리할 수 있는지까지 같이 판단해야 한다.

이 글에서는 다음 내용을 기준으로 정리한다.

일반 테이블에서 인덱스 생성
파티션 테이블에서 LOCAL / GLOBAL 인덱스 생성
운영 중 테이블에서 ONLINE 인덱스 생성
인덱스 REBUILD 와 파티션별 REBUILD
운영 시 확인할 점검 쿼리

기본 인덱스 생성

오라클에서 기본 인덱스는 보통 B-Tree 인덱스이며, CREATE INDEX 문으로 생성한다.

-- 단일 컬럼 인덱스
CREATE INDEX idx_orders_order_date
    ON orders(order_date);

-- 복합 인덱스
CREATE INDEX idx_orders_cust_status
    ON orders(customer_id, order_status);

-- 유니크 인덱스
CREATE UNIQUE INDEX idx_orders_order_no
    ON orders(order_no);

-- 함수 기반 인덱스
CREATE INDEX idx_orders_upper_code
    ON orders(UPPER(order_code));

복합 인덱스는 보통 다음 원칙으로 설계한다.

자주 함께 조회되는 컬럼을 묶는다.
등호 조건(=) 컬럼을 앞쪽에 둔다.
범위 조건(BETWEEN, >, <) 컬럼은 뒤쪽에 둔다.
선택도가 높은 컬럼을 우선 검토한다.

파티션 테이블에서 인덱스 생성

파티션 테이블에서는 인덱스도 같이 전략적으로 설계해야 한다.
오라클에서는 크게 다음 두 가지를 많이 사용한다.

LOCAL 인덱스
GLOBAL 인덱스

참고로 파티션 테이블에 일반 인덱스를 만들면, 그 인덱스는 기본적으로 GLOBAL 인덱스로 취급된다.

1. LOCAL 인덱스

LOCAL 인덱스는 테이블의 파티션 구조를 그대로 따라간다.
즉, 테이블 파티션과 같은 개수와 같은 경계값을 가진 인덱스 파티션이 자동으로 생성된다.

CREATE TABLE sales (
    sales_id     NUMBER,
    sales_date   DATE,
    customer_id  NUMBER,
    amount       NUMBER
)
PARTITION BY RANGE (sales_date) (
    PARTITION p202401 VALUES LESS THAN (DATE '2024-02-01'),
    PARTITION p202402 VALUES LESS THAN (DATE '2024-03-01'),
    PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

-- LOCAL 인덱스
CREATE INDEX idx_sales_local_date
    ON sales(sales_date)
    LOCAL;

-- LOCAL 복합 인덱스
CREATE INDEX idx_sales_local_cust_date
    ON sales(customer_id, sales_date)
    LOCAL;

LOCAL 인덱스의 장점은 다음과 같다.

테이블 파티션 유지보수와 함께 관리하기 쉽다.
특정 파티션 작업 시 영향 범위를 작게 가져갈 수 있다.
대용량 이력성 테이블에서 운영이 편하다.

주의할 점도 있다.

LOCAL UNIQUE INDEX 는 파티션 키가 인덱스 컬럼에 포함되어 있어야 한다.

예를 들어, sales_date 로 파티셔닝된 테이블에서 LOCAL UNIQUE INDEX 를 만들려면 다음처럼 파티션 키를 포함하는 형태가 안전하다.

CREATE UNIQUE INDEX idx_sales_local_uk
    ON sales(sales_id, sales_date)
    LOCAL;

2. GLOBAL 인덱스

GLOBAL 인덱스는 테이블 파티션과 독립적으로 구성된다.
파티션 테이블 전체를 하나의 인덱스로 보거나, 인덱스 자체를 별도의 기준으로 다시 파티셔닝할 수 있다.

-- 비파티션 GLOBAL 인덱스
CREATE INDEX idx_sales_global_id
    ON sales(sales_id);

GLOBAL 인덱스는 다음 두 형태를 생각하면 된다.

비파티션 글로벌 인덱스
글로벌 파티션 인덱스 (GLOBAL PARTITION BY RANGE/HASH)

-- Range 파티션 GLOBAL 인덱스
CREATE INDEX idx_sales_global_amt
    ON sales(amount)
    GLOBAL PARTITION BY RANGE (amount) (
        PARTITION p1 VALUES LESS THAN (100000),
        PARTITION p2 VALUES LESS THAN (500000),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
    );

-- Hash 파티션 GLOBAL 인덱스
CREATE INDEX idx_sales_global_cust
    ON sales(customer_id)
    GLOBAL PARTITION BY HASH (customer_id)
    PARTITIONS 4;

GLOBAL 인덱스는 다음 상황에서 검토한다.

조회 패턴이 파티션 키와 무관할 때
테이블 전체 범위에서 유니크 보장이 필요할 때
특정 비즈니스 키 기준으로 조회가 집중될 때

반대로 파티션 유지보수 작업이 자주 일어나는 테이블이라면 LOCAL 인덱스가 운영상 더 단순한 경우가 많다.

운영 중인 테이블에서 ONLINE 인덱스 생성

서비스 중인 테이블에 인덱스를 새로 만들 때는 ONLINE 옵션을 우선 검토하게 된다.

왜 ONLINE 옵션을 써야 할까

운영 중인 테이블에 인덱스를 생성하거나 재구성할 때 ONLINE 옵션이 중요한 이유는, 서비스 요청이 계속 들어오는 상황에서도 작업을 진행해야 하기 때문이다.

예를 들어 주문, 결제, 회원, 로그성 테이블처럼 계속 INSERT, UPDATE, DELETE 가 발생하는 테이블에서 오프라인 방식으로 인덱스를 만들거나 재구성하면 다음 문제가 생길 수 있다.

애플리케이션 DML 이 대기하거나 실패할 수 있다.
배치와 온라인 트랜잭션이 서로 영향을 줄 수 있다.
인덱스 작업 시간 동안 서비스 지연이 커질 수 있다.
배포 가능 시간이 심야 점검 시간으로 강하게 제한된다.

반대로 ONLINE 옵션을 사용하면 인덱스 작업 중에도 베이스 테이블 DML 을 계속 수행할 수 있으므로 운영 영향도를 크게 낮출 수 있다.

실무적으로는 다음과 같은 이유로 ONLINE 을 선택한다.

서비스 중단 없이 인덱스를 추가해야 할 때
대용량 테이블이라 작업 시간이 길 것으로 예상될 때
배치, API, 관리자 화면이 동시에 같은 테이블을 사용할 때
장애 예방 차원에서 락 영향 범위를 최소화하고 싶을 때

즉, ONLINE 은 단순한 편의 옵션이 아니라 운영계에서 인덱스 작업을 안전하게 수행하기 위한 옵션에 가깝다.
다만 ONLINE 이 만능은 아니고, DDL 은 허용되지 않으며 동시 DML 이 많으면 작업 시간이 길어질 수 있으므로 부하가 비교적 낮은 시간대에 수행하는 것이 좋다.

CREATE INDEX idx_orders_online_01
    ON orders(order_date, customer_id)
    ONLINE;

ONLINE 생성 시 핵심 포인트는 다음과 같다.

인덱스 생성 중에도 DML은 가능하다.
대신 DDL은 허용되지 않는다.
ONLINE 생성/재빌드 중에는 parallel DML 이 지원되지 않는다.
동시 DML 이 많을수록 작업 시간이 길어진다.

운영 예시는 보통 다음처럼 사용한다.

CREATE INDEX idx_orders_online_02
    ON orders(customer_id, order_status)
    TABLESPACE app_index_ts
    ONLINE;

대용량 인덱스라면 PARALLEL, NOLOGGING, 별도 인덱스 테이블스페이스를 함께 검토할 수 있다.
다만 NOLOGGING 을 사용했다면 작업 후 백업 정책까지 같이 확인하는 것이 좋다.

CREATE INDEX idx_orders_online_03
    ON orders(order_date)
    TABLESPACE app_index_ts
    NOLOGGING
    PARALLEL 4
    ONLINE;

ALTER INDEX idx_orders_online_03 NOPARALLEL;

인덱스 REBUILD

이미 존재하는 인덱스는 ALTER INDEX ... REBUILD 로 재구성할 수 있다.

ALTER INDEX idx_orders_online_03 REBUILD;

보통 다음과 같은 목적으로 사용한다.

인덱스를 다른 테이블스페이스로 이동
인덱스 세그먼트 재구성
UNUSABLE 상태 인덱스를 다시 USABLE 로 복구

예를 들어 테이블스페이스를 옮기면서 재구성할 수 있다.

ALTER INDEX idx_orders_online_03
    REBUILD TABLESPACE app_index_ts2;

ONLINE REBUILD

운영 중에는 ONLINE REBUILD 를 자주 사용한다.

ALTER INDEX idx_orders_online_03
    REBUILD ONLINE;

또는 테이블스페이스 이동과 함께 처리할 수도 있다.

ALTER INDEX idx_orders_online_03
    REBUILD ONLINE TABLESPACE app_index_ts2;

ONLINE REBUILD 시 알아둘 점은 다음과 같다.

베이스 테이블 DML 과 병행 가능하다.
REBUILD 절은 인덱스명 바로 뒤에 와야 한다.
온라인 재구성은 일반 재구성보다 최대 키 길이 제약이 더 엄격할 수 있다.
ORA-01450 이 발생하면 오프라인 REBUILD, COALESCE, 재생성을 검토한다.
공간이 부족하면 COALESCE 가 대안이 될 수 있다.

파티션 인덱스 REBUILD

파티션 인덱스는 일반 인덱스처럼 전체를 한 번에 REBUILD 한다고 생각하면 안 된다.

중요한 제약은 다음과 같다.

전체 파티션 인덱스는 한 번에 REBUILD 할 수 없다.
LOCAL 인덱스 전체도 한 번에 REBUILD 할 수 없다.
필요한 파티션 또는 서브파티션 단위로 처리해야 한다.

1. 인덱스 파티션 REBUILD

ALTER INDEX idx_sales_local_date
    REBUILD PARTITION p202401;

특정 파티션을 다른 테이블스페이스로 옮기면서 재구성할 수도 있다.

ALTER INDEX idx_sales_local_date
    REBUILD PARTITION p202401
    TABLESPACE app_index_ts;

2. 복합 파티션 테이블이면 SUBPARTITION 단위로 REBUILD

복합 파티션 테이블에서는 PARTITION 이 아니라 SUBPARTITION 단위로 작업해야 하는 경우가 있다.

ALTER INDEX idx_sales_local_date
    REBUILD SUBPARTITION sp_202401_seoul
    TABLESPACE app_index_ts;

3. UNUSABLE 한 LOCAL INDEX 만 자동으로 복구

파티션 유지보수 이후 LOCAL INDEX 일부가 UNUSABLE 이 되었다면, 테이블 기준으로 복구하는 방법도 실무에서 자주 쓴다.

ALTER TABLE sales
    MODIFY PARTITION p202401
    REBUILD UNUSABLE LOCAL INDEXES;

복합 파티션이면 서브파티션 단위도 가능하다.

ALTER TABLE sales
    MODIFY SUBPARTITION sp_202401_seoul
    REBUILD UNUSABLE LOCAL INDEXES;

이 방식은 해당 파티션에 연결된 UNUSABLE 한 로컬 인덱스만 찾아서 다시 빌드한다는 점이 장점이다.

언제 LOCAL, 언제 GLOBAL 인덱스를 쓸까

정답은 없지만 보통 다음 기준으로 판단한다.

LOCAL 인덱스가 유리한 경우

월별, 일별 이력 데이터처럼 파티션 유지보수가 잦다.
파티션 단위 아카이빙, 드롭, 스플릿이 많다.
운영 안정성과 관리 편의성이 더 중요하다.

GLOBAL 인덱스가 유리한 경우

조회 조건이 파티션 키를 잘 타지 않는다.
전체 데이터 범위에서 빠른 조회가 필요하다.
특정 키에 대해 전역 유니크 보장 또는 단일 액세스 경로가 필요하다.

실무에서는 LOCAL 로 단순하게 가져가고, 꼭 필요한 조회 패턴에만 GLOBAL 을 추가하는 방식이 비교적 안전하다.

운영 전 확인 쿼리

인덱스 작업 전후에는 현재 상태를 먼저 확인하는 것이 좋다.

-- 인덱스 기본 정보
SELECT index_name,
       table_name,
       uniqueness,
       status,
       partitioned,
       tablespace_name
FROM user_indexes
WHERE table_name = 'SALES';

-- 인덱스 컬럼 확인
SELECT index_name,
       column_name,
       column_position
FROM user_ind_columns
WHERE table_name = 'SALES'
ORDER BY index_name, column_position;

-- 파티션 인덱스 정보
SELECT index_name,
       locality,
       partitioning_type,
       status
FROM user_part_indexes
WHERE table_name = 'SALES';

-- 인덱스 파티션 상태 확인
SELECT index_name,
       partition_name,
       status,
       tablespace_name
FROM user_ind_partitions
WHERE index_name = 'IDX_SALES_LOCAL_DATE'
ORDER BY partition_position;

정리

오라클 인덱스 운영에서 핵심은 단순히 인덱스를 만드는 것이 아니라, 테이블 구조와 운영 방식에 맞춰 인덱스 전략을 잡는 것이다.

일반 테이블은 CREATE INDEX
운영 중 테이블은 CREATE INDEX ... ONLINE
기존 인덱스 재구성은 ALTER INDEX ... REBUILD
서비스 영향 최소화가 목표면 ALTER INDEX ... REBUILD ONLINE
파티션 테이블은 LOCAL / GLOBAL 전략을 먼저 결정
파티션 인덱스는 전체가 아니라 PARTITION / SUBPARTITION 단위로 재구성

특히 파티션 테이블에서는 인덱스 구조까지 같이 설계하지 않으면, 나중에 파티션 유지보수 시점에 운영 복잡도가 급격히 올라간다.
대용량 시스템일수록 LOCAL 과 GLOBAL 의 장단점, ONLINE 작업의 제약사항을 먼저 이해하고 적용하는 것이 중요하다.

딴짓하는 개발자

DevelopOracle

Oracle - 인덱스 생성과 파티션 테이블 ONLINE REBUILD 정리