외부에서 생성한 테스트 인덱스, 중복 인덱스, 오래된 인덱스 등 삭제 가능 인덱스 전체 조회 쿼리

 

 

 

아래는 Tibero(Oracle 호환) 에서
“삭제해도 안전한” 인덱스를 **한 번에 전부 찾는 쿼리”입니다.
(1) 중복 인덱스, (2) 외부 테스트 인덱스, (3) 최근 90일간 사용되지 않은 인덱스를 모두 추출합니다.

---

1. 90일 이상 사용되지 않은 인덱스

---

Tibero는 Oracle처럼 V$OBJECT_USAGE 뷰를 제공합니다.
모니터링을 먼저 켜야 통계가 쌓입니다.

sql

-- 1. 모니터링 걸기 (한 번만 실행)
ALTER INDEX [owner].[index_name] MONITORING USAGE;

-- 2. 90일 이상 한 번도 안 쓴 인덱스 리스트
SELECT u.index_name, u.table_name, i.last_analyzed
FROM   user_object_usage u
JOIN   user_indexes      i ON i.index_name = u.index_name
WHERE  u.used = 'NO'
AND    i.last_analyzed < SYSDATE - 90;

---

1. 중복 인덱스 (완전히 같은 컬럼·순서)

---

sql

WITH cols AS (
    SELECT index_name,
           LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_position) AS col_list
    FROM   user_ind_columns
    GROUP BY index_name
)
SELECT a.index_name AS idx1,
       b.index_name AS idx2,
       a.col_list
FROM   cols a
JOIN   cols b
  ON  a.col_list = b.col_list
 AND  a.index_name < b.index_name;

---

1. PK / UK 제약이 아닌 일반 인덱스 전체

---

sql

SELECT index_name, table_name, uniqueness
FROM   user_indexes
WHERE  index_name NOT IN (
        SELECT index_name
        FROM   user_constraints c
        JOIN   user_indexes     i
               ON i.index_name = c.index_name
        WHERE  c.constraint_type IN ('P','U')
      )
ORDER BY table_name, index_name;

---

1. 한방 쿼리로 “삭제 후보” 전체 추출

---

위 3개 결과를 UNION 하면 됩니다.
아래는 하나의 뷰처럼 쓸 수 있는 통합 쿼리:

sql

/* 삭제 안전 후보 인덱스 통합 리스트 */
SELECT DISTINCT
       i.index_name,
       i.table_name,
       CASE
         WHEN u.index_name IS NOT NULL THEN 'UNUSED_90DAYS'
         WHEN d.idx2        IS NOT NULL THEN 'DUPLICATE_WITH_'||d.idx2
         ELSE 'NOT_CONSTRAINT'
       END AS reason
FROM   user_indexes i
LEFT JOIN user_object_usage u
       ON u.index_name = i.index_name AND u.used = 'NO'
LEFT JOIN (
        /* 중복 인덱스 이름 한쪽만 뽑기 */
        WITH cols AS (
            SELECT index_name,
                   LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_position) AS col_list
            FROM   user_ind_columns
            GROUP BY index_name
        )
        SELECT a.index_name AS idx1, b.index_name AS idx2
        FROM   cols a
        JOIN   cols b
          ON  a.col_list = b.col_list
         AND  a.index_name < b.index_name
) d ON d.idx1 = i.index_name
WHERE  i.index_name NOT IN (
        SELECT index_name
        FROM   user_constraints c
        JOIN   user_indexes     i ON i.index_name = c.index_name
        WHERE  c.constraint_type IN ('P','U')
      )
ORDER BY table_name, index_name;

---

1. 삭제 전 확인 체크리스트

---

1. 백업 또는 DDL 스크립트 저장
   sql

   DBMS_METADATA.GET_DDL('INDEX','INDEX_NAME','OWNER')

2. 개발/테스트 환경에서 먼저 삭제 후 애플리케이션 정상 동작 확인
3. 삭제
   sql

   DROP INDEX [owner].[index_name];

---

요약

위 통합 쿼리 한 방으로
“PK/UK 아니면서 90일간 안 쓰였거나 중복된” 인덱스를 한눈에 보고
DROP INDEX 로 안전하게 정리하면 됩니다.