Oracle 인덱스 구성 테이블(Index-Organized Tables) 완벽 가이드

인덱스 구성 테이블(IOT)은 데이터를 프라이머리 키 순서로 저장하여 조회 성능을 최적화하는 특별한 유형의 테이블입니다. 이 가이드에서는 IOT의 개념부터 실제 활용까지 상세히 다루겠습니다.

1. 인덱스 구성 테이블 개요

1.1 주요 특징

데이터가 프라이머리 키 순서로 저장
인덱스와 데이터가 하나의 구조로 통합
추가적인 인덱스 없이 프라이머리 키 기반 조회 최적화
중복 저장 공간 제거로 스토리지 효율성 향상
ROWID를 사용하지 않음

1.2 일반 테이블과의 차이점

-- 일반 테이블의 경우
CREATE TABLE regular_customers (
    customer_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    email VARCHAR2(100)
);
-- 데이터는 힙에 저장되고, 별도의 인덱스가 생성됨

-- 인덱스 구성 테이블의 경우
CREATE TABLE iot_customers (
    customer_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    email VARCHAR2(100)
) ORGANIZATION INDEX;
-- 데이터가 인덱스 구조 안에 직접 저장됨

2. IOT 생성 및 구성

2.1 기본 IOT 생성

-- 기본적인 IOT 생성
CREATE TABLE orders_iot (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_date DATE,
    total_amount NUMBER
) ORGANIZATION INDEX;

2.2 오버플로우 세그먼트 활용

-- 오버플로우 세그먼트를 사용하는 IOT
CREATE TABLE products_iot (
    product_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    description CLOB,
    specifications CLOB
) ORGANIZATION INDEX
  OVERFLOW TABLESPACE products_overflow
  INCLUDING name;

2.3 보조 인덱스 생성

-- IOT에 보조 인덱스 생성
CREATE INDEX idx_products_name 
ON products_iot(name);

-- 비트맵 인덱스 생성
CREATE BITMAP INDEX idx_products_status
ON products_iot(status);

3. IOT 활용 사례

3.1 조회 위주의 업무

-- 우편번호 테이블
CREATE TABLE postal_codes_iot (
    postal_code VARCHAR2(10) PRIMARY KEY,
    city VARCHAR2(100),
    district VARCHAR2(100),
    base_address VARCHAR2(200)
) ORGANIZATION INDEX;

-- 빠른 조회 예제
SELECT base_address
FROM postal_codes_iot
WHERE postal_code = '12345';

3.2 참조 테이블

-- 상품 코드 마스터
CREATE TABLE product_master_iot (
    product_code VARCHAR2(20) PRIMARY KEY,
    category VARCHAR2(50),
    standard_cost NUMBER,
    list_price NUMBER
) ORGANIZATION INDEX
COMPRESS;

-- 관련 테이블에서 참조
CREATE TABLE sales_details (
    sale_id NUMBER,
    product_code VARCHAR2(20) REFERENCES product_master_iot,
    quantity NUMBER,
    unit_price NUMBER
);

3.3 이력 관리

-- 고객 주소 이력
CREATE TABLE customer_address_history_iot (
    customer_id NUMBER,
    effective_date DATE,
    address VARCHAR2(200),
    postal_code VARCHAR2(10),
    PRIMARY KEY (customer_id, effective_date)
) ORGANIZATION INDEX;

4. 성능 최적화

4.1 키 압축

-- 프라이머리 키 압축을 사용한 IOT
CREATE TABLE orders_compressed_iot (
    order_id NUMBER,
    order_date DATE,
    customer_id NUMBER,
    amount NUMBER,
    PRIMARY KEY(order_id, order_date)
) ORGANIZATION INDEX
COMPRESS 1;  -- order_id만 압축

4.2 병렬 처리

-- 병렬 처리를 활용한 데이터 로딩
ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ APPEND PARALLEL(t,4) */
INTO orders_iot t
SELECT /*+ PARALLEL(s,4) */
    order_id, customer_id, order_date, amount
FROM source_table s;

4.3 파티셔닝

-- 파티션된 IOT 생성
CREATE TABLE sales_iot (
    sale_date DATE,
    product_id NUMBER,
    quantity NUMBER,
    amount NUMBER,
    PRIMARY KEY (sale_date, product_id)
) ORGANIZATION INDEX
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION sales_2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION sales_future VALUES LESS THAN (MAXVALUE)
);

5. 관리 및 유지보수

5.1 통계 수집

-- IOT 통계 수집
ANALYZE TABLE orders_iot COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;

-- 또는
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'ORDERS_IOT',
    estimate_percent => 100,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);

5.2 공간 관리

-- IOT 재구성
ALTER TABLE orders_iot MOVE ONLINE;

-- 오버플로우 세그먼트 재구성
ALTER TABLE orders_iot MOVE OVERFLOW;

5.3 모니터링

-- IOT 상태 확인
SELECT table_name, iot_type, iot_name, overflow
FROM USER_TABLES
WHERE iot_type IS NOT NULL;

-- IOT 공간 사용량 확인
SELECT segment_name, segment_type, bytes/1024/1024 as size_mb
FROM USER_SEGMENTS
WHERE segment_name = 'ORDERS_IOT';

6. 설계 고려사항

6.1 적합한 사용 케이스

프라이머리 키 기반 조회가 많은 경우
- 우편번호 검색
- 코드 테이블
- 참조 데이터
순차적 접근이 필요한 경우
- 이력 데이터
- 시계열 데이터
- 로그 데이터
저장 공간 최적화가 필요한 경우
- 중복 데이터가 많은 테이블
- 참조 테이블

6.2 부적합한 사용 케이스

대량 DML이 빈번한 경우
프라이머리 키가 자주 변경되는 경우
ROWID를 필요로 하는 경우

7. 성능 분석

7.1 실행 계획 분석

EXPLAIN PLAN FOR
SELECT * FROM orders_iot
WHERE order_id = 12345;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

7.2 성능 비교

-- 일반 테이블과 IOT 성능 비교
SET TIMING ON

-- IOT 조회
SELECT /*+ INDEX(orders_iot) */ *
FROM orders_iot
WHERE order_id BETWEEN 1000 AND 2000;

-- 일반 테이블 조회
SELECT /*+ INDEX(orders) */ *
FROM orders
WHERE order_id BETWEEN 1000 AND 2000;

8. 베스트 프랙티스

프라이머리 키 설계
- 자주 접근되는 컬럼 조합
- 적절한 카디널리티
- 단조 증가하지 않는 값 선호
오버플로우 세그먼트 활용
- 큰 컬럼은 오버플로우로 이동
- INCLUDING 절 적절히 사용
- 자주 접근되는 컬럼은 메인 세그먼트에 유지
유지보수 전략
- 정기적인 통계 수집
- 주기적인 재구성 고려
- 성능 모니터링 실시

인덱스 구성 테이블(IOT)은 특정 사용 케이스에서 매우 효과적인 성능 최적화 도구입니다. 프라이머리 키 기반의 조회가 많은 업무에서는 탁월한 성능을 제공하며, 저장 공간도 효율적으로 사용할 수 있습니다. 다만, 적절한 사용 케이스 선정과 세심한 설계가 성공적인 구현의 핵심입니다.

저작자표시 비영리 변경금지

개발