UNIQUEインデックスのステータスがUNUSABLEの場合に、VALIDにするための方法

投稿の経緯

実務でORACLEデータベースを使用時にインデックスの絡んだSQLに触れたので備忘用に作成。 インデックスとは何か少し復習した後に、実務で躓いた点を紹介する。

そもそもインデックスとは

検索するデータ量が多い場合に検索対象のカラムに対して作成するもの。 インデックスを作成しない場合selectする度に検索対象のカラムを全て参照するため、DBに負担がかかりすぎて落ちることがある。検索に時間がかかってしまう原因にもなる。

なお、主キー（PRIMARY KEY）を設定したカラム名には自動的にインデックスが作成されるため、作成する必要はない。

インデックス作成の例文

UNIQUE制約を付けたインデックスの作成

CREATE UNIQUE INDEX インデックス名
on テーブル名(カラム名1, カラム名2...);

・UNIQUEインデックス作成時のポイント
複数のカラムの組み合わせに対してUNIQUEインデックスを指定した場合、
それぞれのカラムの中で重複した値があっても、指定した全てのカラムの値の組み合わせが重複していなければ、UNIQUEインデックスを作成できる。

解決したい事象

あるテーブル(ここではTABLE_Xとする)に値をINSERTしようとすると、 インデックスのステータスがUNUSABLEになっているため実行できない旨のエラーが返る。

ORA-01502: index 'TABLE_Xのインデックス名' or partition of such index is in unusable state

(ちなみに全てのUNUSABLEになっているINDEX_NAMEを確認するコマンドは以下)

select index_name, status from all_indexes where status = 'UNUSABLE'

調べたところ、インデックスを再構築(rebuild)すればUNUSABLE状態が解除されるようなので試しに実行

alter index TABLE_Xのインデックス名 rebuild;

するとORA-01452エラーが発生

ORA-01452: UNIQUEなインデックスが作れません。重複するキーがあります。

要は「UNIQUE制約を課しているカラムに入っているデータがなんらかの原因で重複しUNIQUEでなくなっている」
「再構築するなら重複しているデータを削除してUNIQUEな状態に戻してからやってくれBaby」ってことだ。

テスト環境なのでデータを削除することは問題ない。
ならば、重複したデータを特定して削除する必要がある。

重複したデータを特定する

重複したデータを特定するにはまず、テーブルに設定されたインデックスの情報をselectしてどのカラムにインデックスが指定されているのか確かめる必要がある。

select * from user_ind_columns
where index_name = 'インデックス名';

ここで出てきたカラムを4つ、それぞれcolumnA, columnB, columnC, columnDとしておく。

この4つはすべてUNIQUE制約が課されているので、4つ全てのカラムの値の組み合わせが重複しているデータを削除するため、まずはselect文で対象レコードを洗い出す。

/* インデックスの対象テーブルを指定し、別名aを付与 */
select a.* from TABLE_X a,

/* 4つのカラムの値全てが重複しているレコードを取得 */
/* すべてUNIQUEなので(columnA)のところはcolumnBでもcolumnCでも結果は同じ */
(select columnA,columnB,columnC,columnD from table_X
group by columnA,columnB,columnC,columnD
having count(columnA) > 1) b

/* 重複しているカラムを含む全カラム取得 */
where a.columnA = b.columnA and
a.columnB = b.columnB and
a.columnC = b.columnC and
a.columnD = b.columnD;

これで対象レコードが洗い出せるので、出てきた値を元にDELETE文を作成する。※1

/* ※1 */
delete from table_X a

where (a.columnA = 'columnAの値1' and a.columnB = 'columnBの値1' and 
a.columnC = 'columnCの値1' and a.columnD = 'columnDの値1')
or (a.columnA = 'columnAの値2' and a.columnB = 'columnBの値2' and 
a.columnC = 'columnCの値2' and a.columnD = 'columnDの値2')...

実行しようとするとここでまた同じエラーが発生

ORA-01502: index 'TABLE_Xのインデックス名' or partition of such index is in unusable state

エラーを解消するためのコマンドがエラーのためにできない状態になってしまった。
ここで、これはもうインデックスを削除してから再構築をしないと問題が解決しないだろうし、
今後同じ問題が起こったら初めからそうすべきだと感じた。

インデックスを削除して、再作成する

流れ 1.インデックスの削除 2.重複データをテーブルから削除 3.インデックスの再作成

1.インデックス削除

alter table table_X drop constraint インデックス名 cascade drop index;

2.重複しているデータの削除
上記※1のSQL文を実行

3.インデックスの再作成

alter table table_X add constraint インデックス名 
primary key(columnA, columnB, columnC, columnD)
USING index tablespace cspwwpcidx logging pctfree to initrans 2 maxtrans 255
STORAGE(initial 1000K next 1000K minextents 1 maxextents
unlimited pctincrease 0 buffer_pool default);

インデックスのステータスがVALIDになっているかどうか確認の後、コミット

select index_name, status from all_indexes where status = 'UNUSABLE'

COMMIT;

VALIDになっていれば、これでようやくデータのINSERTが可能になる。
途中にも書いたが、インデックスが再作成できる状態ならば
一度削除して再作成することが最短で問題解決する道だと思う。

参考文献

インデックスの意味とメリット・デメリット https://www.dbonline.jp/sqlite/index/index1.html

重複した値をselectする方法
https://qiita.com/necoyama3/items/4c24defd6f504366aebe