[Oracle] SELECT Query를 사용하여 Table Index 확인하는 방법

사진: Unsplash 의 Clay Banks

현재 로그인한 사용자가 조회 권한이 있는 테이블에 대해, 미리 설정된 인덱스 목록과 인덱스의 컬럼 정보를 확인하는 방법입니다.
사실 DB에 직접 접근할 수 있거나, DBA에게 문의할 수 있는 상황이라면 권장되지 않는 방법 입니다. 불가피한 상황에서는 아래 방법을 사용할 수 있습니다. 기본적으로 ALL_INDEXES, ALL_IND_COLUMNS View를 조합하여 사용합니다.

현재 사용자 정보 확인

먼저 기본적인 현황을 파악합니다.
user 는 현재 로그인한 사용자 이름(=스키마 이름)을 반환하는 오라클 예약어이자 내장 함수입니다. 대소문자를 구분하지 않습니다.
dual 테이블은 오라클에 기본 내장된 가상 테이블로 1개 행, 1개 더미 컬럼을 가지고 있습니다. 주로 SELECT 문에서 테이블 이름이 반드시 필요하지만, 실제 테이블 데이터가 필요 없을 때 사용 합니다.

SELECT
    USER                                     AS user_function,
    SYS_CONTEXT('USERENV', 'SESSION_USER')   AS session_user,
    SYS_CONTEXT('USERENV', 'CURRENT_USER')   AS current_user,
    SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS current_schema
FROM
    dual;

이 쿼리는 오라클 메모리에서 즉시 반환되는 값들이기 때문에, 운영 환경에서도 부하 없이 안전하게 사용할 수 있습니다.

Index 정보 조회 쿼리

ALL_INDEXES 테이블에서 인덱스 정보를 조회하면 현재 로그인한 사용자에게 조회 권한이 있는 모든 테이블에 대한 인덱스를 조회할 수 있습니다.
이 쿼리는 실제 테이블이 아니라 메타 데이터의 view에서 데이터를 조회하는 쿼리이기 때문에 디스크 I/O는 발생하지 않습니다.
하지만 인덱스 개수가 수천 개 이상일 경우 일시적 부하가 발생할 수 있으므로, fetch first N rows only; 조건을 추가 합니다.

SELECT
    ai.owner,
    ai.table_name,
    ai.index_name,
    ai.uniqueness,
    aic.column_name,
    aic.column_position,
    ai.index_type
FROM
    all_indexes ai
JOIN
    all_ind_columns aic
    ON ai.index_name = aic.index_name
    AND ai.owner = aic.index_owner
    AND ai.table_name = aic.table_name
WHERE
    ai.owner = USER -- builtin function
    AND ai.table_owner NOT IN ('SYS', 'SYSTEM')
FETCH FIRST 100 ROWS ONLY; -- 결과 수 제한

• owner: 테이블의 소유자 (스키마 이름)
• table_name: 인덱스가 설정된 테이블 이름
• index_name: 인덱스 이름
• uniqueness: 유니크 인덱스인지 여부
• column_name: 인덱스를 구성하는 컬럼 이름
• column_position: 인덱스 내부 컬럼순서
• index_type: 인덱스 타입 (NORMAL, BITMAP, FUNCTION-BASED NORMAL 등)