PSI Labs RSS feed

Oracle:ローカルインデックスで作成した主キーを無効化、有効化するとグローバルインデックスに変わってしまう

こんにちは。shintaniです。

データをUPDATEする際や大量データをimportする際など、
テーブルに作成されているインデックスを一時的に無効化しないと非常に時間が掛かります。

この際、単なるインデックスを ALTER INDEX [インデックス名] UNUSABLE によって無効化する場合は問題無いのですが(※1)
ALTER TABLE [テーブル名] DISABLE CONSTRAINTS [主キー名] で主キー制約を無効化すると問題が起こる場合があります。

なぜかというと「主キー制約を無効化すると主キーインデックス自体が物理的に削除される。そして有効化によって新しく作り直される」ためです。

そのため主キー制約作成の際に USING INDEX LOCAL や TABLESPACE [表領域名] などのオプションを付けていた場合、
主キー制約有効化のSQLでも同じようにしないと「全てデフォルト設定」で主キーのインデックスが作成され、タイトルのような問題が発生してしまいます。

【検証:Oracle 12.1.0.2 EE】

-- インデックス情報取得用 SQL
select index_name , status , partitioned ,  tablespace_name
  from dba_indexes
where index_name = 'PK_TEST'
  and owner = 'TEST'

-- 主キー制約:無効化 SQL
alter table TEST.TBL_TEST  disable constraints  PK_TEST

-- 主キー制約:有効化 SQL
alter table TEST.TBL_TEST  enable constraints  PK_TEST

--***********************************************************************

-- ① 開始前 (パーティション表なので dba_indexes では TABLESPACE_NAME はnull。STATUSは N/A。 dba_ind_partitions で IDX_TBS に作成されていることを別途確認)
INDEX_NAME               STATUS   PARTITIONED TABLESPACE_NAME              
------------------------ -------- ----------- ------------------------------
PK_TEST                  N/A      YES         

-- ② 主キー制約:無効化後   ※ インデックスが削除されてしまっている
行が選択されていません

--③ 主キー制約:有効化後  ※オプション無しのデフォルト設定でインデックスが作成
INDEX_NAME               STATUS   PARTITIONED TABLESPACE_NAME   
------------------------ -------- ----------- ------------------------------
PK_TEST                  VALID    NO          DEFAULT_TBS

対応方法としては2つあります。

【方法1】 
① 主キー制約無効化SQLに  keep index オプションを付けて実行してインデックスが削除されないようにする。
② UNIQUEインデックスとしては有効のままなので、インデックスのunusableコマンドを実行する。
※ 有効化する場合は、インデックスのrebuild、および 主キー制約有効化の2つのSQL実行が必要。

【方法2】
 主キー制約有効化の際に「USING INDEX LOCAL TABLESPACE USERS_IDX」 などオプションを付けて、元と同じ設定のインデックスが作成されるようにする。

alter table TEST.TBL_TEST  enable constraints  PK_TEST     USING INDEX LOCAL TABLESPACE IDX_TBS

みなさんもご注意を。

ではでは。

-------------------------------------------

※1【インデックスの無効化について】

[oracle インデックス 無効化]などでぐぐると上位にくる下記のHPですが、恐ろしい罠があります。

https://okwave.jp/qa/q7842465.html
https://oshiete.goo.ne.jp/qa/7842465.html

ここでは無効化のコマンドで invisible とunusage (正しくは unusable)と書かれています。
そして有効化では rebuild だけが書かれています。

しかし、invisible はOracle11g からの「不可視化」という機能で、これは rebuild では元に戻りません。
visible というコマンドを打つ必要があります。

というか両方実行するくらいならば unusable だけで十分です。
invisible は「有効だがオプティマイザからは見えない」という状態にするもので、
DBA_INDEX では VISIBILITY という項目を見ないと分かりません。

両方実行して「無効かつオプティマイザから見えない」とした場合、
rebuild を実行して「STATUSがVALIDになった」とOKだと思っていると、
SQLでインデックスを使ってくれないという問題が起こります。