Oracle 데이터베이스에서 인덱스 설계는 성능 최적화를 위해 매우 중요합니다. 설계는 데이터를 이해하고, 쿼리 패턴을 분석하며, 적합한 인덱스 유형을 선택하는 것에서 시작됩니다. 아래는 Oracle 인덱스 설계 방법에 대한 가이드입니다.
1. 데이터와 쿼리 패턴 분석
인덱스 설계의 첫 단계는 데이터 및 쿼리 패턴을 이해하는 것입니다. • 데이터 분석: • 데이터의 **카디널리티(고유 값의 수)**를 확인. • 각 열의 분포 및 데이터 크기 확인. • 열이 자주 업데이트되는지 파악. • 쿼리 패턴 분석: • 어떤 쿼리가 자주 실행되는지 파악. • WHERE, JOIN, GROUP BY, ORDER BY 절에서 자주 사용되는 열을 식별. • 대량 삽입/삭제 작업이 많은 경우 인덱스 사용 여부를 신중히 검토.
2. 인덱스 유형 선택
쿼리 요구사항에 따라 적합한 인덱스 유형을 선택하세요.
2.1 B-Tree 인덱스
• 대부분의 OLTP 환경에서 기본적으로 사용. • WHERE 절에서 **높은 선택도(많은 고유 값)**를 가진 열에 적합. • 예: 고객 ID, 이메일, 계좌 번호 등.
CREATE INDEX idx_customer_id ON customers(customer_id);
2.2 비트맵 인덱스
• 데이터 웨어하우스와 같은 읽기 지향적 환경에서 적합. • 선택도가 낮은 열에 유용(예: 성별, 상태코드). • 주의: 자주 업데이트되는 열에는 비트맵 인덱스를 사용하지 않는 것이 좋음.
CREATE BITMAP INDEX idx_gender ON customers(gender);
2.3 복합 인덱스(Composite Index)
• WHERE 절에서 두 개 이상의 열이 자주 사용되는 경우. • 인덱싱 순서는 쿼리의 조건절에 따라 결정. • 예: WHERE 절에서 first_name, last_name을 동시에 사용하는 경우.
CREATE INDEX idx_name ON customers(first_name, last_name);
Tip: 인덱스는 **선행 열(prefix column)**을 기준으로만 활용되므로, 열 순서를 신중히 결정해야 합니다.
2.4 고유 인덱스(Unique Index)
• 열의 값이 고유해야 하는 경우 사용(제약 조건도 자동 생성). • 예: 이메일 주소, 주민등록번호.
CREATE UNIQUE INDEX idx_unique_email ON customers(email);
2.5 함수 기반 인덱스(Function-Based Index)
• 함수 결과를 인덱싱하여 쿼리 성능을 향상. • 예: 대소문자를 무시하는 검색(UPPER)이 필요한 경우.
CREATE INDEX idx_upper_name ON customers(UPPER(last_name));
2.6 역방향 키 인덱스(Reverse Key Index)
• 순차적으로 증가하는 키(예: 시퀀스 번호)로 인한 인덱스 핫스팟 문제 해결. • 주로 고성능 트랜잭션 환경에서 사용.
CREATE INDEX idx_reverse ON orders(order_id) REVERSE;
3. 인덱스 설계 원칙
3.1 쿼리 최적화에 중점
• 쿼리 조건에 자주 사용되는 열에 인덱스를 생성. • GROUP BY, ORDER BY, JOIN 조건에서 사용되는 열 확인.
3.2 복합 인덱스 설계
• WHERE 절의 **선행 조건(column order)**에 따라 열 순서를 신중히 결정. • 가장 선택도가 높은 열을 첫 번째로 배치.
예:
-- WHERE 절: WHERE region = 'US' AND city = 'New York'; CREATE INDEX idx_region_city ON locations(region, city);
3.3 필요한 곳에만 생성
• 너무 많은 인덱스는 DML 성능(INSERT, UPDATE, DELETE)을 저하시킴. • 사용되지 않는 인덱스는 제거.
4. 가상 컬럼 활용
• 계산된 값(파생된 값)을 인덱싱해야 한다면 가상 컬럼과 함께 사용. • 예: 총 금액 계산(price * quantity).
ALTER TABLE orders ADD (total_amount AS (price * quantity)); CREATE INDEX idx_total_amount ON orders(total_amount);
5. 로컬(Local) 또는 글로벌(Global) 인덱스
• 파티셔닝된 테이블에서는 쿼리 요구에 따라 로컬 인덱스 또는 글로벌 인덱스를 선택. • 로컬 인덱스: 각 파티션에 인덱스가 따로 존재. 관리가 쉬움. • 글로벌 인덱스: 전체 데이터에 대해 하나의 인덱스. 파티션 간 쿼리에 유용.
6. 인덱스와 통계 관리
• 최신 통계를 유지하여 옵티마이저가 최적의 실행 계획을 생성하도록 보장. • 정기적으로 인덱스 통계를 수집.
BEGIN
DBMS_STATS.GATHER_INDEX_STATS('SCHEMA_NAME', 'INDEX_NAME');
END;
7. 인덱스 테스트와 검증
• 실제 실행 계획(EXPLAIN PLAN)을 사용하여 인덱스가 효과적으로 사용되는지 확인. • 필요에 따라 힌트(INDEX, NO_INDEX)를 사용하여 특정 인덱스를 강제하거나 무시.
EXPLAIN PLAN FOR SELECT * FROM customers WHERE customer_id = 1001;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
8. 정기적인 모니터링 및 유지보수
• 인덱스 사용 빈도를 모니터링하여 불필요한 인덱스를 제거. • V$OBJECT_USAGE 뷰를 통해 인덱스 사용 상태 확인:
ALTER INDEX index_name MONITORING USAGE; SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME = 'index_name'; ALTER INDEX index_name NOMONITORING USAGE;
9. 온라인 인덱스 작업
• 운영 환경에서는 온라인 작업으로 인덱스를 생성하거나 재구성하여 다운타임을 최소화.
CREATE INDEX index_name ON table_name(column_name) ONLINE; ALTER INDEX index_name REBUILD ONLINE;
10. DML 작업 영향 최소화
• 인덱스는 INSERT, UPDATE, DELETE 작업 시 오버헤드를 발생시키므로, • 빈번히 변경되는 데이터에는 신중히 설계. • 대량 데이터 삽입 전에는 인덱스를 비활성화하거나 삭제 후 다시 생성.
이러한 설계 원칙을 따르면 데이터베이스 성능과 유지 관리 효율성을 모두 개선할 수 있습니다. 인덱스 설계는 데이터 특성과 쿼리 패턴에 맞게 맞춤형으로 설계하는 것이 핵심입니다.