CREATE TABLE
“売上表”(
”売上日”
DATE, “地域” VARCHAR2(20), “売上” NUMBER )
PARTITION BY RANGE (”売上日”) SUBPARTITION BY LIST (
“地域”) (PARTITION P2009Q1
VALUES LESS THAN(to_date('2009-04-01','YYYY-MM-DD')) (SUBPARTITION P2009Q1_kanto VALUES (‘kanagawa','Tokyo'),
SUBPARTITION P2009Q1_kansai VALUES ('Osaka',‘kyoto')
),
INSERT … SELECT による移行
INSERT ... SELECT による移行手順
(1)
パーティション表のための表領域を作成(2)
空のパーティション表を作成(3)
非パーティション表が格納されている表領域をREAD ONLY
に変更(4)
非パーティション表のデータを(2)
へ挿入(5)
非パーティション表が格納されていた表領域をREAD WRITE
に 変更し、非パーティション表を削除(6) (2)
を、(7)
で削除した非パーティション表の表名へリネームする(7)
パーティション表にローカル索引を作成(8)
パーティション表に一意キー制約を追加INSERT … SELECT による移行
INSERT ... SELECT による移行手順
(1)
パーティション表のための表領域を作成SQL> CREATE TABLESPACE tbs200301 DATAFILE 'D:¥ORACLE¥DATA¥USER01.dbf' SIZE 5G;
SQL> CREATE TABLESPACE tbs200302 DATAFILE 'D:¥ORACLE¥DATA¥USER01.dbf' SIZE 5G;
…(中略)…
SQL> CREATE TABLESPACE tbs200810 DATAFILE 'D:¥ORACLE¥DATA¥USER01.dbf' SIZE 5G;
SQL> CREATE TABLESPACE tbs200811 DATAFILE 'D:¥ORACLE¥DATA¥USER01.dbf' SIZE 5G;
SQL> CREATE TABLESPACE tbs200812 DATAFILE 'D:¥ORACLE¥DATA¥USER01.dbf' SIZE 5G;
SQL> ALTER USER SHOPMSTR QUOTA UNLIMITED ON tbs200301;
SQL> ALTER USER SHOPMSTR QUOTA UNLIMITED ON tbs200302;
…(中略)…
SQL> ALTER USER SHOPMSTR QUOTA UNLIMITED ON tbs200810;
SQL> ALTER USER SHOPMSTR QUOTA UNLIMITED ON tbs200811;
SQL> ALTER USER SHOPMSTR QUOTA UNLIMITED ON tbs200812;
作成した表領域を移行を実行するユーザーが使用できるようにし ます。
DBA
権限のユーザーで行います。INSERT … SELECT による移行
INSERT ... SELECT による移行手順
(2)
空のパーティション表を作成移行を実行するユーザーで行います。
作成するパーティション表の定義は、非パーティション表と同一でなければなりません。
なお、maxvalueを指定すると、以後partitionの追加ができなくなってしまうため指定しないで下さい。
SQL> CREATE TABLE int_orderitem(
shipdate date,
orderkey number NOT NULL, quantity number NOT NULL, comment VARCHAR2(44) )
PARTITION by range (shipdate) (
PARTITION testpt200201 values less than
(to_date('2002-02-01','YYYY-MM-DD')) TABLESPACE tbs200201, PARTITION testpt200202 values less than
(to_date('2002-03-01','YYYY-MM-DD')) TABLESPACE tbs200202,
…(中略)…
PARTITION testpt200811 values less than
(to_date('2008-12-01','YYYY-MM-DD')) TABLESPACE tbs200811, PARTITION testpt200812 values less than
(to_date('2009-01-01','YYYY-MM-DD')) TABLESPACE tbs200812);
INSERT … SELECT による移行
INSERT ... SELECT による移行手順
(3)
非パーティション表が格納されている表領域をREAD ONLY
に変更※DBA権限ユーザーで行います。
SQL> ALTER TABLESPACE USERS READ ONLY;
(4)
非パーティション表のデータをパーティション表へ挿入※移行を実行するユーザーで行います。
ヒント文
/*+ APPEND */
を加えると、バッファキャッシュを使用せず直接データ ファイルへ書き込むダイレクトロードインサートが行われるため、INSERT
にか かる時間が短縮されます。SQL> INSERT /*+ APPEND */ INTO int_orderitem SELECT * FROM orderitem;
COMMIT;
INSERT … SELECT による移行
INSERT ... SELECT による移行手順
(5)
非パーティション表が格納されていた表領域をREAD WRITE
に変更し、非パーティション表を削除
DBA
権限のユーザーで行います。SQL> ALTER TABLESPACE USERS READ WRITE;
SQL> DROP TABLE orderitem;
(6)
パーティション表を、非パーティション表の表名へリネームする移行を実行するユーザーで行います。
SQL> RENAME int_orderitem TO orderitem;
INSERT … SELECT による移行
INSERT ... SELECT による移行手順
(7)
パーティション表にローカル索引作成移行を実行するユーザーで行います。
ローカル索引を作成する際は必ずパーティション・キーを含む必要があります。
SQL> CREATE INDEX line_local_idx ON orderitem(orderkey, shipdate) local;
(8)
パーティション表に一意キー制約を追加移行を実行するユーザーで行います。
SQL> ALTER TABLE orderitem ADD CONSTRAINT pk_line PRIMARY KEY (orderkey, linenumber) USING INDEX;
オンライン再定義による移行
オンライン再定義による移行手順
(1)
移行を実行するユーザーに権限を付与(2)
パーティション表のための表領域を作成(3)
非パーティション表(
元表)
がオンラインで再定義可能か確認(4)
移行用のパーティション表(
仮表)
を作成(5)
オンライン再定義を開始(6)
仮表に対してローカル索引を作成(7)
元表と仮表の差分を同期(8)
オンライン再定義を終了(9)
元表を削除オンライン再定義による移行
オンライン再定義による移行手順
(1)
移行を実行するユーザーに権限を付与SQL > grant execute on dbms_redefinition to shopmstr;
SQL > grant create any table to shopmstr;
SQL > grant alter any table to shopmstr;
SQL > grant drop any table to shopmstr;
SQL > grant lock any table to shopmstr;
SQL > grant select any table to shopmstr;
オンライン再定義による移行
オンライン再定義による移行手順
(2)
パーティション表のための表領域を作成SQL> CREATE TABLESPACE tbs200301 DATAFILE 'D:¥ORACLE¥DATA¥USER01.dbf' SIZE 5G;
SQL> CREATE TABLESPACE tbs200302 DATAFILE 'D:¥ORACLE¥DATA¥USER01.dbf' SIZE 5G;
…(中略)…
SQL> CREATE TABLESPACE tbs200810 DATAFILE 'D:¥ORACLE¥DATA¥USER01.dbf' SIZE 5G;
SQL> CREATE TABLESPACE tbs200811 DATAFILE 'D:¥ORACLE¥DATA¥USER01.dbf' SIZE 5G;
SQL> CREATE TABLESPACE tbs200812 DATAFILE 'D:¥ORACLE¥DATA¥USER01.dbf' SIZE 5G;
SQL> ALTER USER SHOPMSTR QUOTA UNLIMITED ON tbs200301;
SQL> ALTER USER SHOPMSTR QUOTA UNLIMITED ON tbs200302;
…(中略)…
SQL> ALTER USER SHOPMSTR QUOTA UNLIMITED ON tbs200810;
SQL> ALTER USER SHOPMSTR QUOTA UNLIMITED ON tbs200811;
SQL> ALTER USER SHOPMSTR QUOTA UNLIMITED ON tbs200812;
作成した表領域を移行を実行するユーザーが使用できるようにします。
DBA権限のユーザーで行います。
オンライン再定義による移行
オンライン再定義による移行手順
(3)
非パーティション表(
元表)
がオンラインで再定義可能か確認SQL> BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
UNAME => 'SHOPMSTR', TNAME => 'ORDERITEM',
OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK, PART_NAME => NULL);
END;
/
オンライン再定義による移行
オンライン再定義による移行手順
(4)
移行用のパーティション表(
仮表)
を作成移行を実行するユーザーで行います。
作成するパーティション表の定義は、非パーティション表と同一でなければなりません。
SQL> CREATE TABLE int_orderitem(
shipdate date,
orderkey number NOT NULL, quantity number NOT NULL, comment VARCHAR2(44) )
PARTITION by range (shipdate) (
PARTITION testpt200201 values less than
(to_date('2002-02-01','YYYY-MM-DD')) TABLESPACE tbs200201, PARTITION testpt200202 values less than
(to_date('2002-03-01','YYYY-MM-DD')) TABLESPACE tbs200202,
…(中略)…
PARTITION testpt200811 values less than
(to_date('2008-12-01','YYYY-MM-DD')) TABLESPACE tbs200811, PARTITION testpt200812 values less than
(to_date('2009-01-01','YYYY-MM-DD')) TABLESPACE tbs200812);
オンライン再定義による移行
オンライン再定義による移行手順
(5)
オンライン再定義を開始以下の例では元表に主キーがあるため、使用する再定義方法のタイプを指定するパラメ ータの
OPTION_FLAG
で、主キー（DBMS_REDEFINITION.CONS_USE_PK
）を指定 しています。SQL> BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'SHOPMSTR',
ORIG_TABLE => 'ORDERITEM', INT_TABLE => 'INT_ORDERITEM', COL_MAPPING => NULL,
OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK, PART_NAME => NULL);
END;
/
オンライン再定義による移行
オンライン再定義による移行手順
(6)
仮表に対してローカル索引を作成移行を実行するユーザーで行います。
ローカル索引を作成する際は必ずパーティション・キーを含む必要があります。
SQL> CREATE INDEX line_local_idx ON int_orderitem(orderkey, shipdate) local;
(7)
元表と仮表の差分を同期(5)
、(6)
の実行中に生じた元表と仮表の差分を同期します。この操作は(8)のFINISH_REDEF_TABLEを実行するまで何度行っても構いません。
SYNC_INTERIM_TABLEを実行することにより、FINISH_REDEF_TABLEを短時間で
完了できます。SQL> BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
UNAME => 'SHOPMSTR',
ORIG_TABLE=> 'ORDERITEM', INT_TABLE => 'INT_ORDERITEM', PART_NAME => NULL);
END;
オンライン再定義による移行
オンライン再定義による移行手順
(8)
オンライン再定義を終了このプロシージャによって元表と仮表の表名がリネームされます。ディクショナリが書き 換えられるわずかな間、表が排他ロックされます。
FINISH_REDEF_TABLEでも内部的に元表と仮表の同期が行われています。
SQL> BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
UNAME => 'SHOPMSTR',
ORIG_TABLE=> 'ORDERITEM', INT_TABLE => 'INT_ORDERITEM', PART_NAME => NULL);
END;
/
(9)
元表を削除元表と仮表の表名が入れ替わっているため、削除する表の名前は(4)で作成した仮表の名前で あることに注意してください。
SQL> DROP TABLE int_orderitem;
グローバル・パーティション索引
CREATE INDEX month_ix ON
“オーダー表”(
”顧客ID”) GLOBAL PARTITION BY RANGE(
”顧客ID”)
(PARTITION pm1_ix VALUES LESS THAN (1000), PARTITION pm2_ix VALUES LESS THAN (2000),
PARTITION pm3_ix VALUES LESS THAN (MAXVALUE));
• グローバル・パーティション作成例
•
”注文番号”列でパーティション化されているパーティション表に対して、”顧客
ID”
列をキーとしたグローバル・パーティション索引を作成オーダー表
”注文番号”
1~99
”注文番号”
100~199
”注文番号”
200~299
”注文番号”
300~399
”顧客
ID”
100~199
”顧客
ID”
200~299
”顧客
ID”
300~399
”顧客
ID”
1~99
• パーティション・ワイズ・ジョインが行われる為には以下の条 件を満たしている必要が有ります
1.
同じ型のカラムをパーティション・キーとし、同じパーティショニング手 法によって同じパーティション数に分割されている2.
レンジ・パーティションの場合、対応するパーティション・バウンド（パ ーティションの境界値）が指定されている3.
少なくとも一方のテーブルがコンポジット・パーティショニングされて いる場合には、レンジ・パーティショニングまたはハッシュ・パーティ ショニングのいずれかの面で上記の条件を満たしている4.
検索時にパーティション・キー列を使って結合する必要があるパーティション単位での結合処理
パーティション・ワイズ・ジョイン利用条件
表の設計の段階からパーティション・ワイズ・ジョインが 頻繁に起こるようにします
パーティション表とメンテナンス
• パーティションの追加
既存のパーティション表に新しくパーティションを追加しますリスト・パーティション表に関連付けられたローカル索引およびグローバル索引は、そのまま使用可能です
ALTER TABLE sales ADD PARTITION p_2 TABLESPACE Users2;
ALTER TABLE q1_sales_by_region ADD PARTITION q1_KANTO VALUES („TOKYO', „KANAGAWA') TABLESPACE KANTO;
【ハッシュ・パーティションの場合】
【リスト・パーティションの場合】