◀ 3.

Oracle 19cのDROP INDEX｜制約依存・ONLINEとINVISIBLE

Oracle 19cの索引削除と制約・可視性の境界を扱います。以下のSQLは独立した教材例で、Oracle実行・復旧・性能は未検証です。製品別比較は各製品の条件で読み、連続した本番手順として実行しないでください。

Oracle 19cのVISIBLEとINVISIBLEは索引が存在し保守が続く状態、DROP後は索引が存在しないことを比較する模式図
INVISIBLEでも索引保守と制約の保護は残ります。存在する索引のVISIBLEへの切替と、DROP後に必要になる再作成は別です。無停止や削除安全性を証明した図ではありません。
この記事の要点
  • 索引が不要になった場合は DROP INDEX 索引名; で削除する
  • 有効な主キー・UNIQUE制約を強制する索引は直接削除できない。制約維持が必要なら削除を進めず、制約解除を一律の解決策にしない
  • 索引削除の検索・DML性能への効果は実行計画と負荷次第。索引保守・容量・制約への影響を分けて判断する
  • ONLINEやINVISIBLEは無停止・安全の保証ではない。可視性切替とUNUSABLE・DROPは別の操作

索引の削除とは

Oracle 19cで不要と判断した索引を削除する構文を説明します。索引には書込み保守と容量のコストがありますが、すべてのDMLが同じコストになるわけではありません。利用が少ないという観測だけで不要と断定せず、定期処理・hint・制約の強制・参照FKや実行計画への影響を確認します。削除をしない判断も含めて検討してください。

構文

Oracle 19cの構文表記で、角括弧は省略可能部分です。所有schema・対象・権限と制約依存を確認してください。ONLINEは適用対象が限られ、無停止を保証しません。教材SQLでありOracle実行は未検証です。

DROP INDEX [スキーマ.]索引名 [ONLINE];

スキーマ名を省略した場合は自分のスキーマが前提です。schema・所有者・対象名を照合してください。他ユーザーが所有する索引を消すには DROP ANY INDEX 権限が必要です。

基本的な使用例

同じ索引を別の所有schema表記で示す独立した削除例です。2文を連続実行しないでください。自己所有または必要なDROP ANY INDEX等の既存権限と制約依存を確認します。権限追加を勧める例ではありません。

-- emp 表に作っていた sal 列の索引を削除
DROP INDEX emp_sal_idx;

-- 別スキーマの索引を指定して削除
DROP INDEX hr.emp_sal_idx;

ONLINE オプション

Oracle 19cのONLINEは削除中の対象表・partitionへのDMLを許容する指定です。無待機・無ロック・無停止や本番での安全を保証するものではありません。domain索引・cluster索引・queue table上の索引には指定できません。対象種別・競合・実行時間とアプリへの影響を別に評価します。

ONLINEはDMLを許容する指定であり無待機・無停止の保証ではありません。domain索引・cluster索引・queue tableの索引には指定できません。対象と競合の確認が必要で、この例のOracle実行・ロック・性能は未検証です。

DROP INDEX emp_sal_idx ONLINE;

主キー・ユニーク制約に紐づく索引

Oracle 19cは主キー・UNIQUE制約の強制に適格な既存索引を再利用でき、一意索引だけでなく非一意索引も使えます。適格な索引がない場合に通常は一意索引を作成するので、制約と索引は同じobjectではありません。有効な制約を強制する索引の直接DROPでORA-02429となっても、制約を先に削除する一律手順にはしません。整合性保護が必要なら索引削除を中止します。

制約を廃止する別の設計判断では、その索引を残すKEEP INDEXと削除するDROP INDEX、既存索引の再利用・作成由来による既定動作を区別します。KEEP INDEXは制約を維持する指定ではありません。残った索引がUNIQUEならその索引自身の一意性強制は残り得ます。参照FKがある主キー・UNIQUEはCASCADEなしでは削除できず、CASCADEは参照制約も失わせます。権限・参照先と業務要件の確認を省略しないでください。

制約解除は索引削除エラーの一律解決策ではありません。以下は独立した破壊的比較例で、PK・UNIQUEの整合性保護を失います。最初の文が索引も消すかは既存索引再利用等に依存し、コメントの無条件保証は誤りです。KEEP INDEXは索引を残す指定で制約維持ではなく、参照FKの制限と権限確認も必要です。必要な制約を外して続行しないでください。

-- 主キー制約と索引をまとめて削除
ALTER TABLE emp DROP CONSTRAINT emp_pk;

-- ユニーク制約に紐づく索引は KEEP / DROP INDEX 句で挙動を指定
ALTER TABLE emp DROP CONSTRAINT emp_email_uk DROP INDEX;

削除前に検討したい安全策

1. まずは INVISIBLE 化して影響を確認

INVISIBLEは通常のoptimizer利用から外す可視性の切替で、索引は存在しDML保守も続きます。OPTIMIZER_USE_INVISIBLE_INDEXESによる利用の例外があり、制約の保護や書込み保守を除去した試験ではありません。必要な負荷・定期処理の範囲で観察しても、削除後の影響ゼロを証明しません。索引が存在する間の不可視化の取消しにはVISIBLEを使えますが、DROP後にVISIBLEとしても索引は復活しません。

UNUSABLEは単なる不可視化ではなく、利用不能として索引保守・DMLや制約にも影響し得る状態です。INVISIBLEの代用品にしません。DROPはobjectの削除であり、必要になれば定義・権限・容量・データの一意性等を確認して再作成を検討します。再作成の成功・所要時間を保証せず、通常のROLLBACKや表のFlashback Dropによる単独索引の復活とも区別します。

3文を連続実行しないでください。INVISIBLEは索引が残りDML保守も継続する可視性切替です。UNUSABLEやDROPとは異なります。DROP後のVISIBLEは索引を復活させません。VISIBLEへ戻せるのは索引がまだ存在する場合だけで、削除後には別途再作成の検討が必要です。観察期間だけで削除の安全は証明できません。

-- 索引を不可視（オプティマイザから無視される）にする
ALTER INDEX emp_sal_idx INVISIBLE;

-- しばらく運用して問題なければ削除
DROP INDEX emp_sal_idx;

-- 戻したいとき
ALTER INDEX emp_sal_idx VISIBLE;

2. 利用統計を見て判断する

DBA_INDEXESは索引の定義・状態・統計等を示す一覧で、索引が一度も使われなかったことを証明する利用履歴ではありません。参照できる既存の実行計画や監視記録と観測期間・リセット・定期処理を照合します。AWRはOracle Diagnostics Packのライセンス等の利用条件を先に確認し、利用権のない環境へ実行を勧めません。一覧や利用回数0だけで制約・FK・hintを含む不要判定はできません。

削除によるメリットとデメリット

メリットデメリット
当該索引の保守コスト削減が見込める場合がある（DML高速化は保証しない）当該索引を使う検索が遅くなる場合がある。変わらない場合もあり実測で判断
索引の割当blockを表領域へ返す（OSのファイル縮小とは別）実行計画が変わって他クエリにも影響することがある
バックアップ容量・復旧時間の改善は方式と対象範囲次第削除後に必要になると再作成コストが高い

関連記事

公式資料と確認範囲

2026-10-27にOracle 19cの公式資料を照合しました。掲載SQLは独立した教材で、Oracleでの実行・復旧・ロック・性能は未検証です。設定変更・権限追加やschema変更を伴う診断手順ではありません。

Post Share
子ページ

子ページはありません

同階層のページ
  1. 索引の作成 (索引関連 SQL)
  2. 索引の再作成
  3. 索引の削除（DROP INDEX）
2026-10-26 17:21:20 atom 245

人気ページ

最近更新/作成されたページ