読者です 読者をやめる 読者になる 読者になる

kenken0807_DBメモ

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

Oracle-semi-onlineDDL作成した

結構前にJPOUGでLTしたOracleDatabaseセミオンラインDDLを作成しました。
ベンチマークツールのswingbenchで試したりしたのですが、たぶん動くはず。。。 OracleDatabase11gR2に対応。

仕組みは以下を参照
kenken0807.hatenablog.com

github.com

できることとできないこと

できること

  • カラムの追加(add)、変更(modify)や削除(drop)ができる

    • 例えばnumber型からvarchar2型へ変更する際はテーブルが空でないといけないがデータがあってもできる
  • インデックスの追加

  • インデックスの削除

できないこと

  • カラム名の変更(RENAME)

  • 対象のテーブルのPKが外部参照されている場合、実行不可

    • 外部参照しているテーブルのForeign Keyを削除またはdisableにしてあげる必要がある。

注意点

  • 対象のテーブルが外部参照している場合、--disable-foreignkeyで新規作成されるテーブルの外部参照はdisableになるが 既存のテーブルは外部参照したままなのでテーブルをrenameする際に待たされる場合がある。セミオンラインDDLを実行する前に外部参照をdisableしてあげたほうが良い。

  • PK必須

  • データ容量が対象のテーブルの約3倍使用する

オプション

$ 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したい内容

  1. PROMOTION_ID NUMBER(6)PROMOTION_ID VARCHAR(6)へ変更
  2. 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)               |約4秒ほどアクセスできない時間が発生。
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負荷が高いので注意。