kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

Oracle どんなに巨大なテーブルでもカラム定義変更を即時で完了させる方法

巨大なテーブルでカラムの定義変更中はDML処理が
enq: TM - contentionlibrary 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

まとめ

  1. カラム追加時は11g以降であればデフォルト値があったとしても即時で反映される。
  2. カラム削除時はUNUSEDを使用する。
  3. カラム変更時も即時で反映される。