Oracle Database unusableなインデックスを無効にできない

Question

#事象
運用の対応で、重複レコードが何かの原因で入ってしまったテーブルがあるため、重複レコードを削除しようと思っています。対象のテーブルの主キー制約を定義してあるインデックスstatusを確認しますとunusableとなっていましたので、以下の手順を開発環境で試し成功しましたので、本番環境で試しましたがうまくいきませんでした。

1. 対象のテーブルの主キー制約を定義してあるインデックスをalter table disable constraint インデックス名で無効にする。
2. 重複レコードを削除するdelete文を実行する。

1番目のDDL文はエラーもなく成功しました。
しかし、2番目のDML文を流すと「ORA-01502: 索引'インデックス名'またはそのパーティションが使用不可の状態です。」と表示され、失敗しました。

どうやら、1番目のDDL文でインデックスが無効の状態になっていなかったようです。開発環境では１番目のDDL文で無効になったのに。

#調べたこと
無効にする方法がほかにないかを調べてみますと、インデックスを再作成するや、削除して再度作り直すことによりunusable状態をなくしてから、無効にすればいいのかなとおもいました。

・インデックス再作成について
https://ameblo.jp/archive-redo-blog/entry-10035203640.html

・インデックス削除し、作り直す
http://tmftake.hateblo.jp/entry/20130125/oracle_constraint_unusable

#アドバイスをいただきたいこと
・調べたことに書いてある内容のことをすればインデックスのunusable状態をなくし、無効にすることができますでしょうか?
・インデックスの再作成や削除(Drop)し再度作り直すことで生じる不具合とかはありますでしょうか？
・開発環境でできて、本番環境でできてなかったような、同じような体験をしたことがある人がいれば、なぜできなかったかなどを教えていただきたいです。

#環境
Oracle Database 10g

#補足
開発環境と本番環境の内容についての情報は職場でないとかくにんできないため、それらに関する追加情報はあまり期待しないでほしいです。

#sql
・create tableで定義しているプライマリーキー対象のカラム(具体的な列名は控えさせていただきます。)
create table テーブル名(
column_1、
column_2、
column_3、
column_4、
column_5
) テーブルスペース名

・index
CREATE UNIQUE INDEX インデックス名 ON テーブル名(column_1、column_2、column_3、column_4、column_5)

・delete文
delete from テーブル名 where column_1 = パラメータ、column_2 = パラメータ、column_3 = パラメータ、column_4 = パラメータ、column_5 = パラメータ、

Answer 1

unusableになっているindexを
<ALTER TABLE テーブル名 DROP CONSTRAINT プライマリキー名 CASCADE DROP INDEX>で
削除後に重複レコードを削除し、再度indexを作成し解決しました。
注意点がindexを削除する際にCASCADE DROP INDEXをつけていないと、プライマリーキのみ削除され、indexが削除されないらしいです。
https://qiita.com/takiru/items/85556663100ed2c4f3bf

Comments

[Orlofsky]

URLは https://teratail.com/help/question-tips#questionTips3-7 の [リンク] で [リンク先のタイトル](http...) に修正してください。

Answer 2

通常、PRIMARY KEYと主キーは同じ意味で使うっているユーザーが多いように思えます。PRIMARY KEYはユニーク制約が付いているはずですし、主キーと称するインデックスも CREATE UNIQUE INDEX ... になっているはずなので、そもそも重複するデータがINSERTされるはずはありません。

重複すると困るキーがあるならPRIMARY KEYや主キーでなくても CREATE UNIQUE INDEX を設定することがごく稀にあります。

重複するデータを削除するのに ALTER TABLE ... MOVE TABLESPACE を使う必要はありません。

CREATE TABLE(PRIMARY KEYや主キーと右のSQLを実行するのに必要な列だけで良い), CREATE INDEX, 実際に重複するデータを削除するのに実行したSQLをその結果を質問に追記してください。

簡単な重複データを削除する方法

Oracle Database 10g

バージョンが古過ぎ。サポート契約が有効ならライセンスのバージョンアップは無料です。

Comments

[montai21]

回答ありがとうございます!

解決したい内容について特に重要な要素でなかったので、省略したのですが、tableにデータを追加するのにsqlloaderを用いてdatファイルの中身を追加しています。作業を引き継いでこの問題にとりかかっていたので、sqlloaderの処理の内容は見てはいないのですが、オプションでdirect=trueとすることで、重複データの挿入を許すそうなので、おそらくそれが原因で重複データが入ったと思われます。
https://rmrmrmarmrmrm.hatenablog.com/entry/449452315.html

重複データを削除するのにインデックスの操作は特に必要ないんですね。勉強になりました。

sql文の内容については職場に行かないと環境がみれないため、また後日連携しますね。

あと、教えていただいた方法で重複データを削除しますと、indexがunusableな状態も解除されるのでしょうか？

[Orlofsky]

削除ってDELETEで使う人とDROPで使う人がいるので英語で書いてください。質問から修正しておくと良いでしょう。

ALTER TABLE ... MOVE TABLESPACE ってテーブルを別の表領域に移動する方法で、重複データのDELETEとは関係ありません。
indexがunusableな状態で重複データのDELETEDEできるかはやってみないとわかりません。その前にindexがunusableのまま放置したい理由がわかりません。

データベースは定期的にきちんとバックアップを取っていますか？
わたしがmontai21さんの上司ならデータベースを壊してリカバリできなくなる前に1ヶ月でも2ヶ月でも良いからきちんとした技術者を雇ってmontai21さんに教えさせます。

[montai21]

Orlofskyさんが指摘している部分はアドバイスいただきたいことで記載しているところの削除についてのことでよろしかったでしょうか？

indexがunssableのまま放置したいとは思っておりません。
「簡単な重複データを削除する方法についてのリンク」や「重複するデータを削除するのに ALTER TABLE ... MOVE TABLESPACE を使う必要はありません。」などindexの操作には関係ないことを提示してきましたので、関係ないとこちらが誤解しました。

「indexがunusableな状態で重複データのDELETEDEできるかはやってみないとわかりません。」については事象のところで書いている内容の通りできないことを試しております。

DBは定期的にbkupとっています。