Oracle-semi-onlineDDL作成した
結構前にJPOUGでLTしたOracleDatabaseセミオンラインDDLを作成しました。
ベンチマークツールのswingbenchで試したりしたのですが、たぶん動くはず。。。
OracleDatabase11gR2に対応。
仕組みは以下を参照
kenken0807.hatenablog.com
できることとできないこと
できること
カラムの追加(add)、変更(modify)や削除(drop)ができる
- 例えばnumber型からvarchar2型へ変更する際はテーブルが空でないといけないがデータがあってもできる
インデックスの追加
インデックスの削除
できないこと
カラム名の変更(RENAME)
対象のテーブルのPKが外部参照されている場合、実行不可
- 外部参照しているテーブルのForeign Keyを削除またはdisableにしてあげる必要がある。
注意点
対象のテーブルが外部参照している場合、
--disable-foreignkeyで新規作成されるテーブルの外部参照はdisableになるが 既存のテーブルは外部参照したままなのでテーブルをrenameする際に待たされる場合がある。セミオンラインDDLを実行する前に外部参照をdisableしてあげたほうが良い。PK必須
データ容量が対象のテーブルの約３倍使用する
オプション
$ perl oracle-semi-onlineDDL.pl
16:27:14 :[ERROR] Check Options
Usage: perl oracle-semi-onlineDDL.pl [options]
--dryrun Create and alter the new table, but do not copy table[default none(MUST choose --dryrun or --execute)]
--execute Create and alter the new table and DO copy table[default none(MUST choose --dryrun or --execute)]
Connect OPTIONS:
--db SID[dafault none]
--user username[default none]
--password user's password[default none]
--host hostname or IP[default localhost]
--port listener port[default 1521]
--table tablename [dafault none]
CREATE NEW INDEX:
--idx [create index statement] the tablename should be "--TABLENAME--"
ex. --idx "create index TEXT_IDX on --TABLENAME-- (col1,col2) tablespace test_ts"
ex. --idx "create index TEXT_IDX2 on --TABLENAME-- (col2 desc) tablespace test_ts"
DROP INDEX:
--dropidx [INDEX NAME] Drop Index
ex. --dropidx "IDX_TEST"
REBUILD INDEX:
--rebuild REcreate Table and Indexes
ALTER TABLE:
--alter [alter statement after ALTER TABLE XXXX]
ex. --alter "drop (text_col)"
ex. --alter "modify id varchar2(10) not null"
OTHERS:
--auto Rename Table Auto[default none]
--disable-foreignkey The Option is to disable the foreign key that has the target table.After finish to alter table,the foreign key is still disable.
実行
swingbenchで実行中に試してみた。
テーブル情報
約3Gのテーブル
>desc ORDERS
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ORDER_ID NOT NULL NUMBER(12)
ORDER_DATE NOT NULL TIMESTAMP(6) WITH LOCAL TIMEZONE
ORDER_MODE VARCHAR2(8)
CUSTOMER_ID NOT NULL NUMBER(12)
ORDER_STATUS NUMBER(2)
ORDER_TOTAL NUMBER(8,2)
SALES_REP_ID NUMBER(6)
PROMOTION_ID NUMBER(6)
WAREHOUSE_ID NUMBER(6)
DELIVERY_TYPE VARCHAR2(15)
COST_OF_DELIVERY NUMBER(6)
WAIT_TILL_ALL_AVAILABLE VARCHAR2(15)
DELIVERY_ADDRESS_ID NUMBER(12)
CUSTOMER_CLASS VARCHAR2(30)
CARD_ID NUMBER(12)
INVOICE_ADDRESS_ID NUMBER(12)
>select count(*) from ORDERS;
COUNT(*)
----------
14297900
Alter Tableしたい内容
PROMOTION_IDNUMBER(6)→PROMOTION_IDVARCHAR(6)へ変更DELIVERY_ADDRESS_IDにインデックスを追加する
セミオンラインDDLをまずドライランで実行
$ perl oracle-semi-onlineDDL.pl
--db orcl \
--user soe \
--password soe \
--table ORDERS \
--idx "create index IDX_ORDERS on --TABLENAME-- (DELIVERY_ADDRESS_ID)" \
--alter "modify PROMOTION_ID VARCHAR(6)" \
--dryrun
17:18:52 :[WARNING]Stop alter table because ORDERS has Foreign Key(ORDERS_CUSTOMER_ID_FK).
17:18:52 :You need --disable-foreignkey option.
17:18:52 :Recommend to disable or drop Foreign key(ORDERS_CUSTOMER_ID_FK) first if alter table
Foreign Keyが設定されいるため、実行できない。
Foreign Keyをdisableにして、--disable-foreignkeyを指定して実行
>alter table ORDERS modify constraint ORDERS_CUSTOMER_ID_FK disable;
表が変更されました。
$ perl oracle-semi-onlineDDL.pl
--db orcl \
--user soe \
--password soe \
--table ORDERS \
--idx "create index IDX_ORDERS on --TABLENAME-- (DELIVERY_ADDRESS_ID)" \
--alter "modify PROMOTION_ID VARCHAR(6)" \
--dryrun \
--disable-foreignkey
ドライランで実行されるSQLなどが表示される。
17:27:33 :Tablename(ORDERS) NewTablename(NEW__ORDERS) OldTablename(OLD__ORDERS) Mvname(MV__ORDERS) Triggername(TRG__ORDERS)
17:27:33 :PK (ORDER_PK) is not used by Foreign Key
----------------------------------------------------------
[DEBUG]
CREATE INDEX "SOE"."NEW__ORD_SALES_REP_IX" ON "SOE"."NEW__ORDERS" ("SALES_REP_ID") REVERSE
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
TABLESPACE "SOE"
PARALLEL 48
----------------------------------------------------------
17:27:47 :idx(ORD_SALES_REP_IX) newidx(NEW__ORD_SALES_REP_IX) oldidx(OLD__ORD_SALES_REP_IX)
----------------------------------------------------------
[DEBUG]
CREATE INDEX "SOE"."NEW__ORD_ORDER_DATE_IX" ON "SOE"."NEW__ORDERS" ("ORDER_DATE") REVERSE
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
TABLESPACE "SOE"
PARALLEL 48
----------------------------------------------------------
17:27:47 :idx(ORD_ORDER_DATE_IX) newidx(NEW__ORD_ORDER_DATE_IX) oldidx(OLD__ORD_ORDER_DATE_IX)
----------------------------------------------------------
[DEBUG]
CREATE INDEX "SOE"."NEW__ORD_WAREHOUSE_IX" ON "SOE"."NEW__ORDERS" ("WAREHOUSE_ID", "ORDER_STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
TABLESPACE "SOE"
PARALLEL 48
----------------------------------------------------------
17:27:47 :idx(ORD_WAREHOUSE_IX) newidx(NEW__ORD_WAREHOUSE_IX) oldidx(OLD__ORD_WAREHOUSE_IX)
----------------------------------------------------------
[DEBUG]
CREATE INDEX "SOE"."NEW__ORD_CUSTOMER_IX" ON "SOE"."NEW__ORDERS" ("CUSTOMER_ID") REVERSE
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
TABLESPACE "SOE"
PARALLEL 48
----------------------------------------------------------
17:27:47 :idx(ORD_CUSTOMER_IX) newidx(NEW__ORD_CUSTOMER_IX) oldidx(OLD__ORD_CUSTOMER_IX)
17:27:50 :DIABLE FOREIGN KEY(NEW__ORDERS_CUSTOMER_ID_FK)
----------------------------------------------------------
[DEBUG]
CREATE TABLE "SOE"."NEW__ORDERS"
( "ORDER_ID" NUMBER(12,0) CONSTRAINT "NEW__ORDER_ORDER_ID_NN" NOT NULL ENABLE,
"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "NEW__ORDER_DATE_NN" NOT NULL ENABLE,
"ORDER_MODE" VARCHAR2(8),
"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "NEW__ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE,
"ORDER_STATUS" NUMBER(2,0),
"ORDER_TOTAL" NUMBER(8,2),
"SALES_REP_ID" NUMBER(6,0),
"PROMOTION_ID" NUMBER(6,0),
"WAREHOUSE_ID" NUMBER(6,0),
"DELIVERY_TYPE" VARCHAR2(15),
"COST_OF_DELIVERY" NUMBER(6,0),
"WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15),
"DELIVERY_ADDRESS_ID" NUMBER(12,0),
"CUSTOMER_CLASS" VARCHAR2(30),
"CARD_ID" NUMBER(12,0),
"INVOICE_ADDRESS_ID" NUMBER(12,0),
CONSTRAINT "NEW__ORDER_PK" PRIMARY KEY ("ORDER_ID")
USING INDEX REVERSE PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
TABLESPACE "SOE" ENABLE NOVALIDATE,
CONSTRAINT "NEW__ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) DEFERRABLE ENABLE NOVALIDATE,
CONSTRAINT "NEW__ORDER_TOTAL_MIN" CHECK (order_total >= 0) DEFERRABLE ENABLE NOVALIDATE,
CONSTRAINT "NEW__ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
REFERENCES "SOE"."CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL DISABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 16 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "SOE"
----------------------------------------------------------
17:27:50 :Create Table (NEW__ORDERS)
----------------------------------------------------------
[DEBUG]
ALTER TABLE NEW__ORDERS MODIFY PROMOTION_ID VARCHAR(6)
----------------------------------------------------------
17:27:51 :ALTER (NEW__ORDERS) (ALTER TABLE NEW__ORDERS MODIFY PROMOTION_ID VARCHAR(6))
17:27:51 :Create Index (NEW__ORD_WAREHOUSE_IX)
17:27:51 :Create Index (NEW__ORD_CUSTOMER_IX)
17:27:51 :Create Index (NEW__ORD_SALES_REP_IX)
17:27:51 :Create Index (NEW__ORD_ORDER_DATE_IX)
17:27:51 :>>>>>CREATE NEW INDEX(create index IDX_ORDERS on NEW__ORDERS (DELIVERY_ADDRESS_ID))
17:27:51 :Create MVLOG (ORDERS)
----------------------------------------------------------
[DEBUG]
create materialized view log on ORDERS NOLOGGING
----------------------------------------------------------
17:27:53 :[DRY-RUN] Drop MVLOG(ORDERS)
17:27:54 :Create Mview (MV__ORDERS)
----------------------------------------------------------
[DEBUG]
create materialized view MV__ORDERS NOLOGGING as select ORDER_ID,ORDER_DATE,ORDER_MODE,CUSTOMER_ID,ORDER_STATUS,ORDER_TOTAL,SALES_REP_ID,PROMOTION_ID,WAREHOUSE_ID,DELIVERY_TYPE,COST_OF_DELIVERY,WAIT_TILL_ALL_AVAILABLE,DELIVERY_ADDRESS_ID,CUSTOMER_CLASS,CARD_ID,INVOICE_ADDRESS_ID from ORDERS where rownum < 2
----------------------------------------------------------
----------------------------------------------------------
[DEBUG]
insert /*+ APPEND */ into NEW__ORDERS(ORDER_ID,ORDER_DATE,ORDER_MODE,CUSTOMER_ID,ORDER_STATUS,ORDER_TOTAL,SALES_REP_ID,PROMOTION_ID,WAREHOUSE_ID,DELIVERY_TYPE,COST_OF_DELIVERY,WAIT_TILL_ALL_AVAILABLE,DELIVERY_ADDRESS_ID,CUSTOMER_CLASS,CARD_ID,INVOICE_ADDRESS_ID) select ORDER_ID,ORDER_DATE,ORDER_MODE,CUSTOMER_ID,ORDER_STATUS,ORDER_TOTAL,SALES_REP_ID,PROMOTION_ID,WAREHOUSE_ID,DELIVERY_TYPE,COST_OF_DELIVERY,WAIT_TILL_ALL_AVAILABLE,DELIVERY_ADDRESS_ID,CUSTOMER_CLASS,CARD_ID,INVOICE_ADDRESS_ID from MV__ORDERS where rownum < 2
----------------------------------------------------------
17:27:56 :[DRY-RUN]COPY TABLE (MV__ORDERS to NEW__ORDERS) 1 row
----------------------------------------------------------
[DEBUG]
create or replace trigger TRG__ORDERS
after insert or delete or update on MV__ORDERS for each row
begin
IF (INSERTING) THEN
INSERT INTO NEW__ORDERS (ORDER_ID,ORDER_DATE,ORDER_MODE,CUSTOMER_ID,ORDER_STATUS,ORDER_TOTAL,SALES_REP_ID,PROMOTION_ID,WAREHOUSE_ID,DELIVERY_TYPE,COST_OF_DELIVERY,WAIT_TILL_ALL_AVAILABLE,DELIVERY_ADDRESS_ID,CUSTOMER_CLASS,CARD_ID,INVOICE_ADDRESS_ID) VALUES ( :NEW.ORDER_ID , :NEW.ORDER_DATE , :NEW.ORDER_MODE , :NEW.CUSTOMER_ID , :NEW.ORDER_STATUS , :NEW.ORDER_TOTAL , :NEW.SALES_REP_ID , :NEW.PROMOTION_ID , :NEW.WAREHOUSE_ID , :NEW.DELIVERY_TYPE , :NEW.COST_OF_DELIVERY , :NEW.WAIT_TILL_ALL_AVAILABLE , :NEW.DELIVERY_ADDRESS_ID , :NEW.CUSTOMER_CLASS , :NEW.CARD_ID , :NEW.INVOICE_ADDRESS_ID );
END IF;
IF (UPDATING) THEN
UPDATE NEW__ORDERS SET CARD_ID = :NEW.CARD_ID , ORDER_STATUS = :NEW.ORDER_STATUS , WAIT_TILL_ALL_AVAILABLE = :NEW.WAIT_TILL_ALL_AVAILABLE , ORDER_TOTAL = :NEW.ORDER_TOTAL , ORDER_MODE = :NEW.ORDER_MODE , ORDER_DATE = :NEW.ORDER_DATE , SALES_REP_ID = :NEW.SALES_REP_ID , PROMOTION_ID = :NEW.PROMOTION_ID , CUSTOMER_CLASS = :NEW.CUSTOMER_CLASS , ORDER_ID = :NEW.ORDER_ID , DELIVERY_ADDRESS_ID = :NEW.DELIVERY_ADDRESS_ID , CUSTOMER_ID = :NEW.CUSTOMER_ID , WAREHOUSE_ID = :NEW.WAREHOUSE_ID , INVOICE_ADDRESS_ID = :NEW.INVOICE_ADDRESS_ID , DELIVERY_TYPE = :NEW.DELIVERY_TYPE , COST_OF_DELIVERY = :NEW.COST_OF_DELIVERY WHERE ORDER_ID = :OLD.ORDER_ID ;
END IF;
IF (DELETING) THEN
DELETE FROM NEW__ORDERS WHERE ORDER_ID = :OLD.ORDER_ID ;
END IF;
end;
----------------------------------------------------------
17:27:56 :Create Trigger (TRG__ORDERS)
17:27:57 :Drop Trigger (TRG__ORDERS)
17:27:57 :Drop Mview (MV__ORDERS)
17:27:58 :Drop Table NEW__ORDERS
17:27:58 :[DRY-RUN] complete, Does not alter
セミオンラインDDL実行
ドライランで問題なければ、executeする。
今回はIO性能が低いもので行ったので時間がかかった
$ perl oracle-semi-onlineDDL.pl
--db orcl \
--user soe \
--password soe \
--table ORDERS \
--idx "create index IDX_ORDERS on --TABLENAME-- (DELIVERY_ADDRESS_ID)" \
--alter "modify PROMOTION_ID VARCHAR(6)" \
--execute \
--disable-foreignkey
17:32:27 :Tablename(ORDERS) NewTablename(NEW__ORDERS) OldTablename(OLD__ORDERS) Mvname(MV__ORDERS) Triggername(TRG__ORDERS)
17:32:27 :PK (ORDER_PK) is not used by Foreign Key
17:32:32 :idx(ORD_SALES_REP_IX) newidx(NEW__ORD_SALES_REP_IX) oldidx(OLD__ORD_SALES_REP_IX)
17:32:32 :idx(ORD_ORDER_DATE_IX) newidx(NEW__ORD_ORDER_DATE_IX) oldidx(OLD__ORD_ORDER_DATE_IX)
17:32:32 :idx(ORD_WAREHOUSE_IX) newidx(NEW__ORD_WAREHOUSE_IX) oldidx(OLD__ORD_WAREHOUSE_IX)
17:32:32 :idx(ORD_CUSTOMER_IX) newidx(NEW__ORD_CUSTOMER_IX) oldidx(OLD__ORD_CUSTOMER_IX)
17:32:32 :DIABLE FOREIGN KEY(NEW__ORDERS_CUSTOMER_ID_FK)
17:32:32 :Create Table (NEW__ORDERS)
17:32:32 :ALTER (NEW__ORDERS) (ALTER TABLE NEW__ORDERS MODIFY PROMOTION_ID VARCHAR(6)) ←カラム変更
17:32:33 :Create Index (NEW__ORD_WAREHOUSE_IX) ←既存インデックス
17:32:33 :Create Index (NEW__ORD_CUSTOMER_IX) ←既存インデックス
17:32:33 :Create Index (NEW__ORD_SALES_REP_IX) ←既存インデックス
17:32:33 :Create Index (NEW__ORD_ORDER_DATE_IX) ←既存インデックス
17:32:33 :>>>>>CREATE NEW INDEX(create index IDX_ORDERS on NEW__ORDERS (DELIVERY_ADDRESS_ID)) ←新規インデックス追加
17:32:33 :Create MVLOG (ORDERS) ←MVIEWログ作成
17:32:34 :Create Mview (MV__ORDERS) ←MVIEW作成
17:35:20 :Copy Table (MV__ORDERS to NEW__ORDERS) ←テーブルデータのコピーがすごく重い
18:20:00 :Create Trigger (TRG__ORDERS) ←トリガー作成
18:20:10 :Fast Refresh Start (MV__ORDERS)
18:20:31 : End
=============================================
Choose Number and Enter key
1 or Only EnterKey: Refresh Mview(MV__ORDERS)
2: Rename Table(ORDERS)
9: Cancel
=============================================
18:21:31 :Mview log cnt: 547 ←差分の更新数
18:21:31 :Fast Refresh Start (MV__ORDERS)
18:21:33 : End
=============================================
Choose Number and Enter key
1 or Only EnterKey: Refresh Mview(MV__ORDERS)
2: Rename Table(ORDERS)
9: Cancel
=============================================
1 ← 1 で再度差分の更新
18:21:35 :Mview log cnt: 54
18:21:35 :Fast Refresh Start (MV__ORDERS)
18:21:35 : End
=============================================
Choose Number and Enter key
1 or Only EnterKey: Refresh Mview(MV__ORDERS)
2: Rename Table(ORDERS)
9: Cancel
=============================================
2 ← 2 でテーブルの入れ替えを実施
18:21:36 :Fast Refresh Start (MV__ORDERS)
18:21:36 : End
18:21:37 :>>>Read Only Table (ORDERS) ←ここから
18:21:38 :Fast Refresh Start (MV__ORDERS) ｜
18:21:38 : End ｜
18:21:38 :Drop Mview log(ORDERS) ｜約４秒ほどアクセスできない時間が発生。
18:21:41 :Rename Table (ORDERS to OLD__ORDERS) ｜差分の更新が多いとMview logの削除に時間がかかる模様
18:21:41 :Rename Table (NEW__ORDERS to ORDERS) ｜
18:21:41 :>>>Finish ReadOnly ←ここまで
18:21:42 :Drop Trigger (TRG__ORDERS)
18:21:42 :Drop Mview (MV__ORDERS)
18:21:43 :Drop Table (OLD__ORDERS)
18:21:43 :Finish Completely
18:21:43 :You need to rename CONSTRAINT_NAME and INDEX_NAME
というように最小限の停止時間でDDLが可能となる。
Mviewの作成とテーブルデータコピーが非常に重いため、IO負荷が高いので注意。
