note2self

Oracleでのインデックス運用に関するメモ書きです。

▼インデックスの一覧を抽出

以下のクエリを所有者ユーザにて実行することで、インデックス名、対象のテーブル名、オーナーの情報が確認できます。

SELECT INDEX_NAME, TABLE_NAME, TABLE_OWNER FROM USER_INDEXES ORDER BY INDEX_NAME;

▼無効なインデックスを抽出

上記のクエリに「STATUS <> ‘VALID’」を条件に加えて抽出できます。

SELECT * FROM USER_INDEXES WHERE STATUS <> 'VALID' AND TABLE_OWNER = 'XXXX';

▼インデックスが使用中か確認する

Oracle12.1より前のバージョンかどうかで変わるのですが、前のバージョンにおいては以下の2つのステップを実行することでインデックスの利用中状況が確認できます。

1. インデックスのモニタリングを有効化する
2. 利用状況を照会
3. （不要であれば）インデックスのモニタリングを無効化する

インデックスのモニタリングを有効化する

ALTER INDEX index_name MONITORING USAGE;

インデックスの利用状況照会

「used」の列に「YES」の値があれば、モニタリングを開始して以降に当該インデックスが使用されたことが「ある」ことを示します。

SELECT index_name,  table_name, monitoring, used, start_monitoring, end_monitoring
FROM   v$object_usage WHERE  INDEX_NAME = 'index_name1' ORDER BY index_name;

インデックスのモニタリングを無効化する

ALTER INDEX index_name NOMONITORING USAGE;

(参考)

https://oracle-base.com/articles/10g/index-monitoring

(更新履歴)

2025/05/30 「無効なインデックスの抽出」を追加