오라클 인덱스 상태 점검 쿼리

인덱스 상태 확인

ELECT 
    INDEX_NAME, 
    TABLE_NAME, 
    STATUS,        -- VALID or UNUSABLE
    UNIQUENESS,    -- UNIQUE or NONUNIQUE
    LAST_ANALYZED  -- 최근 분석 시점
FROM 
    USER_INDEXES
WHERE 
    TABLE_NAME = '테이블명';

• STATUS
  • VALID: 인덱스가 정상적으로 동작 중
  • UNUSABLE: 인덱스가 비활성 상태 (재구성 필요)
• UNIQUENESS
  • UNIQUE: 고유 인덱스
  • NONUNIQUE: 비고유 인덱스

 

인덱스 손상 여부 확인

특정 인덱스가 손상되었는지 확인하려면 아래 쿼리를 사용할 수 있습니다.

SELECT 
    INDEX_NAME, 
    TABLE_NAME, 
    STATUS 
FROM 
    USER_INDEXES
WHERE 
    STATUS = 'UNUSABLE';

인덱스 구성 확인(컬럼 및 타입)

인덱스에 포함된 컬럼 정보를 확인하려면 USER_IND_COLUMNS 뷰를 사용합니다.

SELECT 
    INDEX_NAME, 
    TABLE_NAME, 
    COLUMN_NAME, 
    COLUMN_POSITION 
FROM 
    USER_IND_COLUMNS
WHERE 
    TABLE_NAME = '테이블명'
ORDER BY 
    INDEX_NAME, COLUMN_POSITION;

 

사용 빈도 확인

인덱스 사용 통계는 V$OBJECT_USAGE 뷰를 통해 확인할 수 있습니다. 이를 활성화하려면 아래 명령을 먼저 실행해야 합니다.

ALTER INDEX 인덱스_이름 MONITORING USAGE;

 

모니터링 후 사용 빈도 확인

SELECT 
    INDEX_NAME, 
    TABLE_NAME, 
    MONITORING, 
    USED 
FROM 
    V$OBJECT_USAGE;

 

USED: YES는 인덱스가 사용됨을 의미, NO는 사용되지 않았음을 의미

인덱스 크기 확인

인덱스 크기는 DBA_SEGMENTS 뷰를 통해 확인할 수 있습니다.

SELECT 
    SEGMENT_NAME AS INDEX_NAME, 
    SEGMENT_TYPE, 
    TABLESPACE_NAME, 
    BYTES/1024/1024 AS SIZE_MB 
FROM 
    USER_SEGMENTS
WHERE 
    SEGMENT_TYPE = 'INDEX'
    AND SEGMENT_NAME = '인덱스명';