오라클 파티션 인덱스 상태 조회 쿼리

 파티션 인덱스 상태 조회

SELECT 
    INDEX_NAME, 
    PARTITION_NAME, 
    STATUS,        -- VALID or UNUSABLE
    TABLESPACE_NAME, 
    LAST_ANALYZED  -- 최근 분석 날짜
FROM 
    USER_IND_PARTITIONS
WHERE 
    INDEX_NAME = '인덱스명';

• INDEX_NAME: 인덱스 이름
• PARTITION_NAME: 파티션 이름
• STATUS:
  • VALID: 파티션 인덱스가 정상적으로 사용 가능
  • UNUSABLE: 파티션 인덱스가 비활성화됨 (재구성 필요)

특정 테이블의 모든 파티션 인덱스 상태 조회

SELECT 
    p.INDEX_NAME, 
    p.PARTITION_NAME, 
    p.STATUS, 
    i.TABLE_NAME, 
    i.TABLESPACE_NAME
FROM 
    USER_INDEXES i
JOIN 
    USER_IND_PARTITIONS p
ON 
    i.INDEX_NAME = p.INDEX_NAME
WHERE 
    i.TABLE_NAME = '테이블명';

 
 이 쿼리는 특정 테이블의 모든 파티션 인덱스 상태를 한 번에 확인합니다.
 

UNUSABLE 상태인 파티션 인덱스 조회

SELECT 
    INDEX_NAME, 
    PARTITION_NAME, 
    STATUS 
FROM 
    USER_IND_PARTITIONS
WHERE 
    STATUS = 'UNUSABLE';

파티션 별 인덱스 크기 조회

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

파티션 인덱스 사용 여부 조회

인덱스의 사용 여부는 V$OBJECT_USAGE 뷰를 통해 확인 가능합니다. 파티션별 사용 여부를 확인하려면 아래를 실행합니다.
먼저 모니터링 활성화를 합니다.

ALTER INDEX 인덱스명 MONITORING USAGE;

 이후 사용 여부 확인

SELECT 
    INDEX_NAME, 
    TABLE_NAME, 
    PARTITION_NAME, 
    MONITORING, 
    USED 
FROM 
    V$OBJECT_USAGE
WHERE 
    INDEX_NAME = '인덱스명';

파티션 인덱스 상세 정보(컬럼 및 구성)

SELECT 
    a.INDEX_NAME, 
    a.PARTITION_NAME, 
    b.COLUMN_NAME, 
    b.COLUMN_POSITION
FROM 
    USER_IND_PARTITIONS a
JOIN 
    USER_IND_COLUMNS b
ON 
    a.INDEX_NAME = b.INDEX_NAME
WHERE 
    a.INDEX_NAME = '인덱스명'
ORDER BY 
    a.PARTITION_NAME, b.COLUMN_POSITION;