Oracleのインデックス管理まとめ。create index・alter index・drop indexの構文とポイント

Oracle Databaseのインデックス管理に必要なSQLを、この記事1本にまとめて解説します。

作成: create index（表領域指定、invisible、online）
再作成・変更: alter index（rebuild、表領域移動、visible/invisible切り替え）
削除: drop index（online句）
確認: dba_indexes によるインデックス一覧

※この記事はB-Treeインデックスを対象とします。ビットマップインデックス等その他の種類は対象外です。

インデックス一覧の確認：dba_indexes

既存のインデックスは dba_indexes ビューで確認できます。

col INDEX_NAME for a30
col TABLE_NAME for a30
select INDEX_NAME, TABLE_NAME, TABLESPACE_NAME, STATUS, VISIBILITY
from dba_indexes where OWNER = 'TEST001';

STATUS が UNUSABLE のインデックスは後述の rebuild で再作成が必要です。

インデックスの作成：create index

create index の基本構文は次のとおりです。

create index <ユーザー名>.<インデックス名> on
<ユーザー名>.<表名>(<列名>, ...)
[tablespace <表領域>];

格納先の表領域を指定：tablespace

create index test001.tab001_idx on
test001.tab001(col01)
tablespace testtbs;

インデックスは思った以上にデータサイズを必要とするため、格納先の表領域は明示的に指定しましょう。 指定しない場合はユーザーのデフォルト表領域に作成されます。デフォルト表領域は次のSQLで確認できます。

select USERNAME, DEFAULT_TABLESPACE from DBA_USERS;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST001                        TESTTBS

オプティマイザに使わせない不可視索引：invisible

オプティマイザがインデックスを使用するかどうかは visible / invisible で指定します（デフォルトは visible）。

create index test001.tab001_idx on
test001.tab001(col01)
invisible;

invisible を指定すると、問い合わせ時にオプティマイザから使用されなくなります。初期化パラメータ OPTIMIZER_USE_INVISIBLE_INDEXES をセッションまたはシステムレベルで TRUE にすると、不可視インデックスも使用されます。

「日中帯はインデックスを使用せず、夜間のバッチ処理でだけ使う」といった使い分けや、削除前に影響を確認する用途で使えます。

作成中もDMLを受け付ける：online

通常、インデックス作成中は対象テーブルの更新がブロックされますが、online 句を指定すると作成中もテーブルへのDMLを許可できます。

create index test001.tab001_idx on
test001.tab001(col01)
online;

ただし次の制限があります。

オンライン索引の作成中はパラレルDMLがサポートされず、発行するとエラーになる
ビットマップ索引・クラスタ索引には指定できない
UROWID列の従来索引には指定できない
索引構成表の一意でない2次索引では、索引キー列数と論理ROWIDの主キー列数の合計を32以下にする必要がある

インデックスの再作成・変更：alter index

alter index の基本構文は次のとおりです。

alter index <ユーザー名>.<インデックス名> [各種操作];

再作成：rebuild

alter index test001.tab001_idx rebuild;

インデックスを長期間使っていると断片化やブロックの偏りが生じ、インデックスが原因の性能劣化が発生します。定期的な再作成で解消できますが、再作成中はインデックスを利用できなくなる点に注意しましょう。

オンライン中の再作成：rebuild online

問い合わせを受けている状態のまま再作成するには rebuild online を指定します。

alter index test001.tab001_idx rebuild online;

制限事項は create index ~ online と同様です（パラレルDML不可、ビットマップ結合・クラスタインデックス不可など）。

格納先表領域の変更：rebuild tablespace

alter index test001.tab001_idx rebuild tablespace TESTTBS;

再作成と同時に、インデックスを別の表領域へ移動できます。

利用有無の切り替え：visible / invisible

既存インデックスの可視性も alter index で変更できます。

alter index test001.tab001_idx invisible;
alter index test001.tab001_idx visible;

動作は create index の項で説明したとおりです。削除を検討しているインデックスをまず invisible にして影響を確認する、という使い方が安全です。

インデックスの削除：drop index

drop index の基本構文は次のとおりです。

drop index test001.tab001_idx;

オンライン中の削除：online

問い合わせを受けている状態のまま削除するには online 句を指定します。

drop index test001.tab001_idx online;

まとめ

操作	SQL	注意点
一覧確認	`select ... from dba_indexes`	STATUS=UNUSABLEはrebuildが必要
作成	`create index ~ tablespace ~`	表領域は明示的に指定する
再作成	`alter index ~ rebuild [online]`	断片化による性能劣化の解消
表領域移動	`alter index ~ rebuild tablespace ~`	再作成と同時に移動
無効化	`alter index ~ invisible`	削除前の影響確認に便利
削除	`drop index ~ [online]`	invisibleで確認してから削除が安全

インデックスが実際に使われているかは実行計画の確認方法、性能への影響は統計情報の解説も合わせて参考にしてください。