Oracle どんなに巨大なテーブルでもカラム定義変更を即時で完了させる方法
巨大なテーブルでカラムの定義変更中はDML処理が
enq: TM - contention
やlibrary cache lock
などで待機してしまう。
待機させないためにカラム定義変更を即時で完了させることのできる方法をまとめたメモ。
とあるテーブル
SQL>desc ab Name Null? Type ------------- -------- ---------------------------- ID NOT NULL NUMBER(38) TEXT VARCHAR2(30) DATED DATE SQL>select count(*) from ab; COUNT(*) ---------- 1000000
カラム追加
基本的には即時で完了する。
SQL>alter table ab add (tuika number(11)); Table altered. Elapsed: 00:00:00.01
また、11g以降であればALTER TABLE...ADD COLUMNの最適化
により、
列のデフォルト値とNOT NULLを同時に指定してあげれば即時で終わる。
列のデフォルト値のみであれば処理に時間がかかる。
- デフォルト値とNOT NULLを同時に指定した場合
SQL>alter table ab add (tuika number(11) default 9999999 not null); Table altered. Elapsed: 00:00:00.08
- デフォルト値のみ指定した場合
SQL>alter table ab add (tuika number(11) default 9999999 ); Table altered. Elapsed: 00:00:30.70
カラム削除
削除の場合はDROP COLUMN
を使用すると処理に時間がかかるため、UNUSED
を使用する。
DROP COLUMN
の場合
SQL>alter table ab drop column tuika; Table altered. Elapsed: 00:00:16.35
UNUSED
の場合
SQL>alter table ab set unused column tuika; Table altered. Elapsed: 00:00:00.01
しかし、UNUSED
は実質上削除と同等であるが、データ自体は残っているため
メンテナンスのタイミング等でデータの削除をする必要がある。
このときはDROP COLUMN
と同等の時間が掛かってしまう。
SQL>alter table ab drop unused column; Table altered. Elapsed: 00:00:16.61
カラム変更
カラムのリネーム
リネームの場合は即時で可能。
SQL>alter table ab rename column tuika to henkou; Table altered. Elapsed: 00:00:00.05
カラムサイズ変更
対象のカラムが空でなければ縮小はできない。 拡大の場合はデータが入っていても即時で可能。
SQL>alter table ab modify (tuika number(12)); Table altered. Elapsed: 00:00:00.00
SQL>update ab set tuika=null; SQL>alter table ab modify (tuika number(7)); Table altered. Elapsed: 00:00:00.12
まとめ
- カラム追加時は11g以降であればデフォルト値があったとしても即時で反映される。
- カラム削除時は
UNUSED
を使用する。 - カラム変更時も即時で反映される。