以下の事項は 弊社の一般的な製品の方向性に関する概要を説明するものです また 情報提供を唯一の目的とするものであり いかなる契約にも組み込むことはできません 以下の事項は マテリアルやコード 機能を提供することをコミットメント ( 確約 ) するものではないため 購買決定を行う際の判断材料になさらな

Transcription

1 Oracle Direct Seminar 実践!! データベース管理 ~ パーティション詳細編 ~ 日本オラクル株式会社

2 以下の事項は 弊社の一般的な製品の方向性に関する概要を説明するものです また 情報提供を唯一の目的とするものであり いかなる契約にも組み込むことはできません 以下の事項は マテリアルやコード 機能を提供することをコミットメント ( 確約 ) するものではないため 購買決定を行う際の判断材料になさらないで下さい オラクル製品に関して記載されている機能の開発 リリースおよび時期については 弊社の裁量により決定されます Oracle と Java は Oracle Corporation 及びその子会社 関連会社の米国及びその他の国における登録商標です 文中の社名 商品名等は各社の商標または登録商標である場合があります 2

3 Agenda パーティションの概要 パーティションの管理 パーティション検証 まとめ Appendix 資料中に掲載されている検証内容は 特定の環境における検証結果についての報告であり すべての環境において同様の動作を保証するものではありませんので予めご了承下さい 3

4 大規模テーブル運用の問題点 データ量が増えてくると 売上表 第 4 四半期の合計売上高が見たい 結果 全く同じ Select 文を投げたとしても データ量によって結果が返ってくる時間が著しく異なる 4

5 パーティションとは 大きな表や索引をデータベース内部で複数の領域に分割して管理する Oracle Database Enterprise Edition Oracle Partitioning オプションが必要 通常の 1 つの表 パーティション化された表 内部的に表を分割 ユーザやアプリケーションからは ひとつの表に見える 5

6 パーティションのメリット パフォーマンスの向上 必要なデータのみを読み込むので DISKI/O を削減できる 管理性の向上 バックアップやデータのローディングをパーティション単位で実行できる 障害 / 保守時の影響を最小化 障害やメンテナンスの際 パーティション単位で操作することができ 障害 / 保守時の影響を最小化できる 6

7 1. パフォーマンスの向上 パーティション プルーニング 対象のデータが格納されているパーティションだけにアクセスし 不要なパーティションを読み飛ばす 10 月 ~12 月のデータが見たい Oracle Database 売上表 1~3 月パーティション 4~6 月パーティション オプティマイザ SELECT sum( 売上高 ) FROM 売上表 WHERE 売上月 between 10 and 12 7~9 月パーティション 10~12 月パーティション 7

8 Demonstration パーティションプルーニングの効果 8

9 Demonstration 1 ヶ月の売上を計算するまでにかかる時間を比較 5 年分のデータ (5000 万件 ) を格納 1 ヶ月の売上合計を計算する SQL を発行 売上表 1 ヶ月単位で表を分割 (72 パーティションに分割 ) 売上表 1998 年 1 月パーティション 1 ヶ月の売上が見たい VS 1998 年 2 月パーティション 1998 年 3 月パーティション 9

10 2. 管理作業もパーティション単位 例えば 統計情報の取得もパーティション単位 作業中は他のパーティションは影響を受けない 売上表 Q1 パーティション Q2 パーティション 他のパーティションは影響を受けない!! Q3 パーティション Q4 パーティション 10

11 3. パーティション単位で障害の影響を限定 特定のパーティションに障害が発生しても 他のパーティションは影響を受けない パーティション単位でリカバリを行えるので 障害から短時間で復旧できる売上表 Q1~Q3 のデータは リカバリ中でも通常通りアクセス可 2010 年 Q1 パーティション 2010 年 Q2 パーティション 2010 年 Q3 パーティション パーティション単位でリカバリ実行 2010 年 Q4 パーティション 2010 年 Q4 パーティション 11

12 パーティションの種類 単一レベル パーティション レンジ パーティション リスト パーティション ハッシュ パーティション コンポジット パーティション メイン パーティション化の拡張 サブ 8i 9i 8i レンジリストハッシュ レンジ 11g 9i 8i リスト 11g 11g 11g インターバル パーティション 11g リファレンス パーティション 11g バーチャルカラム パーティション 11g 12

13 Agenda パーティションの概要 パーティションの管理 パーティション表の作成と移行 パーティション索引 パーティション表の結合 パーティション表のメンテナンス パーティション検証 まとめ Appendix 13

14 パーティション表の作成 CREATE TABLE PARTITION 句を使用して作成 レンジパーティションの場合 VALUES LESS THAN キーワードを使用し 分割する範囲を決定 売上表 2009 年 1~3 月 ( 売上日 ) Sales_p1 VALUES LESS THAN (2009/04/01) 2009 年 4~6 月 ( 売上日 ) Sales_p2 VALUES LESS THAN (2009/07/01) 2009 年 7~9 月 ( 売上日 ) Sales_p3 VALUES LESS THAN (2009/10/01) CREATE TABLE 売上表 ( 製品 ID number(4) not null, 売上日 date, 2009 年 10~12 月 ( 売上日 ) Sales_p4 VALUES LESS THAN (2010/01/01) パーティション化するキー列を指定 顧客 ID varchar2(40)) PARTITION BY RANGE( 売上日 ) VALUES LESS THANキーワードを使用 (PARTITION SALES_P1 VALUES LESS THAN(TO_DATE('2009/04/01','YYYY/MM/DD')) TABLESPACE Q1, PARTITION SALES_P2 VALUES LESS THAN(TO_DATE('2009/07/01','YYYY/MM/DD')) TABLESPACE Q2, PARTITION SALES_P3 VALUES LESS THAN(TO_DATE('2009/10/01','YYYY/MM/DD')) TABLESPACE Q3, PARTITION SALES_P4 VALUES LESS THAN(TO_DATE('2010/1/01','YYYY/MM/DD')) TABLESPACE Q4); 14

15 パーティション アドバイザ SQL アドバイザ機能の一部 ワークロード ( システムで実行されるSQL 文 ) を解析し 適切なパーティション構成を推奨する機能 1 Oracle Database11g 新機能 2 Diagnostics Option Tuning Optionが必要性能向上のための パーティション構成がアドバイスされる SQL アクセス アドバイザがアクセスパターンや列の使用を分析 15

16 既存の表からパーティション表への移行 INSERT... SELECT による移行 空のパーティション表を作り 非パーティション表からデータを INSERT SELECT によって挿入し その後 RENAME する 1INSERT 非パーティション表 売上表 空のパーティション表 仮売上表 表の再定義による移行 2DROP 非パーティション表 売上表 パーティション表 ORDERITEM 空のパーティション表 仮売上表 表の可用性に影響を与えずに表の構造を変更できる機能 再定義中も 問合せおよび DML を使用してその表にアクセス可能 3RENAME 1 再定義 非パーティション表 売上表 パーティション表 仮売上表 16

17 Agenda パーティションの概要 パーティションの管理 パーティション表の作成と移行 パーティション索引 パーティション表の結合 パーティション表のメンテナンス パーティション検証 まとめ Appendix 資料中に掲載されている検証内容は 特定の環境における検証結果についての報告であり すべての環境において同様の動作を保証するものではありませんので予めご了承下さい 17

18 パーティション索引 2 種類のパーティション索引 ローカル索引 ローカル同一キー索引 ローカル非同一キー索引 グローバル索引 グローバル同一キー索引 索引 パーティション索引 18

19 ローカル同一キー索引 表パーティションと同一のキーでパーティション化する索引 索引のキー列も表パーティションのキー列と同一である索引 基礎となるパーティション表に変更があった場合 索引パーティションも自動でメンテナンスが行われる CREATE INDEX 索引名 ON パーティション表名 ( 列名 ) LOCAL; 特定の索引パーティションのみにアクセス 注文番号 10 のデータがみたい 索引 注文番号 1~99 注文番号 注文番号 注文番号 100~ ~ ~399 パーティション プルーニングが機能するにはパーティション キーを条件に指定する必要がある 売上表 注文番号 1~99 注文番号 注文番号 注文番号 100~ ~ ~399 19

20 ローカル非同一キー索引 表パーティションと同一のキーでパーティション化する索引 索引のキー列が表パーティションのキー列と同一でない索引 CREATE INDEX 索引名 ON パーティション表名 ( 列名 ) LOCAL; 全ての索引パーティションにアクセス 顧客 ID21 のデータを見たい 索引 顧客 ID 1 顧客 ID 21 顧客 ID 7 顧客 ID 21 顧客 ID 2 顧客 ID 12 顧客 ID 97 顧客 ID 81 検索条件によってはパーティションプルーニングが機能しない Order 表 注文番号 注文番号 注文番号 注文番号 1~99 100~ ~ ~399 20

21 ローカル非同一キー索引 表パーティションと同一のキーでパーティション化する索引 索引のキー列が表パーティションのキー列と同一でない索引 特定の索引パーティションのみにアクセス 注文番号が 1~99 の間でかつ顧客 ID21 のデータを見たい 索引 顧客 ID 1 顧客 ID 21 顧客 ID 7 顧客 ID 21 顧客 ID 2 顧客 ID 12 顧客 ID 97 顧客 ID 81 検索条件によってはパーティションプルーニングが機能しない Order 表 注文番号 注文番号 注文番号 注文番号 1~99 100~ ~ ~399 21

22 グローバル パーティション索引 表パーティションとは別のキーでパーティション化する索引 基礎となるパーティション表に変更があった場合 手動で索引パーティションのメンテナンスが必要になる パーティション化された表に関係なく 自由に索引のパーティション化が可能 CREATE INDEX 索引名 ON 表名 ( 列名 ) GLOBAL PARTITION BY 顧客 ID21 のデータを見たい 索引 顧客 ID 顧客 ID 顧客 ID 顧客 ID 1~99 100~ ~ ~399 特定の索引パーティションにアクセスするが 複数のパーティション表にアクセス Order 表 注文番号 注文番号 注文番号 注文番号 1~99 100~ ~ ~399 グローバル パーティション索引の作成例については APPENDIX をご確認ください 22

23 パーティション索引のガイドライン表にアクセスするアプリケーションの特性を考慮に入れ作成 索引を作成する列がパーティション表のパーティション キーと同一 はい ローカル同一キー索引 いいえ 対象のパーティション表や索引に対してメンテナンスを頻繁に行う はい ローカル非同一キー索引 いいえ システムのタイプは DSS タイプか OLTP タイプか DSS ローカル非同一キー索引 OLTP グローバル索引 23

24 Agenda パーティションの概要 パーティションの管理 パーティション表の作成と移行 パーティション索引 パーティション表の結合 パーティション表のメンテナンス パーティション検証 まとめ Appendix 資料中に掲載されている検証内容は 特定の環境における検証結果についての報告であり すべての環境において同様の動作を保証するものではありませんので予めご了承下さい 24

25 パーティション単位での結合処理パーティション ワイズ ジョイン パーティション表同士をパーティション キー列によって パーティション単位で結合処理を行うことができる ジョイン作業の回数が少なくなり レスポンス時間を短縮 結合に必要なメモリー量の減少 顧客表 売上表 読み込み & 結合処理 読み込み & 結合処理 読み込み & 結合処理 読み込み & 結合処理 25

26 Agenda パーティションの概要 パーティションの管理 パーティション表の作成と移行 パーティション索引 パーティション表の結合 パーティション表のメンテナンス パーティション検証 まとめ Appendix 資料中に掲載されている検証内容は 特定の環境における検証結果についての報告であり すべての環境において同様の動作を保証するものではありませんので予めご了承下さい 26

27 パーティション表に行えるメンテナンス作業 通常の表と同様 パーティション表に対してもメンテナンス作業を行う事が可能 パーティションの追加 ADD PARTITION パーティションの削除 DROP PARTITION パーティションのマージ MERGE PARTITIONS パーティションの結合 COALESCE PARTITION DROP PARTITION Q1 Q2 Q3 Q1 ADD PARTITION Q4 27

28 パーティションの追加 既存のパーティション表に新しくパーティションの追加が可能 レンジ パーティションの場合 ALTER TABLE 売上表 ADD PARTITION P VALUES LESS THAN (TO_DATE('2009/4/01','YYYY/MM/DD')) TABLESPACE Users1; 売上表 VALUE 句で指定した範囲の一番最後にしか追加できない 追加パーティション 2008 年 6~8 月 2008 年 1~3 月 2008 年 4~6 月 2008 年 7~9 月 2008 年 10~12 月 MAXVALUE MAXVALUE 句を使用すると パーティションの追加ができない 追加パーティション 2009 年 1~3 月 28

29 インターバルパーティションによる自動追加 定義したインターバル期間に従い レコードの挿入時に必要に応じて自動的にパーティションが追加される DBA の管理業務コストを軽減することが可能 レンジ パーティションから変更することも可能 ALTER TABLE テーブル名 SET INTERVAL( インターバル期間 ); 11g 新機能 自動で追加 ZZZ 売上表 2007 年 11 月 2008 年 2 月 2008 年 3 月 USER CREATE TABLE 売上表 ( 売上日 date, ) PARTITION BY RANGE( 売上日 ) INTERVAL(NUMTOYMINTERVAL(1, month )) STORE IN (TS1, TS2, TS3) (PARTITION P VALUES LESS THAN(TO_DATE('2008/12/01','YYYY/MM/DD'))); DBA 通常のレンジ パーティションに INTERVAL 句を追加 キーには NUMBER 型 DATE 型のみ指定可能 29

30 パーティションの削除 パーティション表内のデータも削除 ハッシュ パーティション以外のパーティションで実行可能 ハッシュ パーティションの場合は結合操作を実行 ALTER TABLE 売上表 DROP PARTITION p_2; 削除後 削除したパーティションはフラッシュ バックで再現不可 売上表 2008 年 1~3 月 売上表 2008 年 1~3 月 リサイクルビン 2008 年 4~6 月 2008 年 4~6 月 削除パーティション 2008 年 7~9 月 2008 年 7~9 月 2008 年 10~12 月 2008 年 7~12 月 30

31 パーティションの結合 データを残しパーティション表のみを削除する場合は結合操作を実行 ハッシュ パーティション表の場合 : ALTER TABLE 売上表 COALESCE PARTITION; それ以外の場合 : ALTER TABLE 売上表 MERGE PARTITIONS P1, P2 INTO P2 TABLESPACE USERS; 売上表 売上表 2008 年 1~3 月 2008 年 1~3 月 2008 年 4~6 月 2008 年 4~6 月 2008 年 7~9 月 2008 年 7~12 月 2008 年 10~12 月 31

32 Agenda パーティションの概要 パーティションの管理 パーティション検証 まとめ Appendix 資料中に掲載されている検証内容は 特定の環境における検証結果についての報告であり すべての環境において同様の動作を保証するものではありませんので予めご了承下さい 32

33 パーティション検証 1. パーティションを利用すると本当に早くなるのか 2. パーティションは細かく切れば切るほどよいのか 3. 複数のパーティションをまたぐ検索ではパフォーマンスが落ちるのでは 4. 検索以外の処理でも効果はあるのか 33

34 参考 : 検証環境 ハードウェア構成 サーバーマシン ProLiant DL380 G3 インテル Xeon プロセッサ 3.20GHz x2 ストレージ装置 HP StorageWorks MSA1000 HP BladeSystem c-class ソフトウェア構成 Red Hat Enterprise Linux 5 Oracle Database 11g Enterprise Ediditon R2 ( ) ストレージ HP StorageWorks MSA

35 テーブル情報 テーブル属性 10 カラム, 約 100Byte / record テーブル タイプ パーティション テーブル ( レンジ パーティション ) 通常表 パーティション サイズ 1ヶ月毎 /72 Partition 4 半期毎 /24 Partition 1 年毎 /6 Partition テーブル サイズ 3GB (5 年分 ) 売上表 35

36 検索処理時間 (1 回あたりの検索処理時間 ) テーブル サイズ : 3GB パーティション テーブルの 1 件検索処理時間を 10msec とした場合の相対処理時間 取得データ量 ( 実際の処理時間に任意の数を掛けています ) 全データ (5000 万件 ) 1 年分 (1000 万件 ) 3 ケ月分 (300 万件 ) 1 ケ月分 (100 万件 ) 1 日 (2 万件 ) 一ヶ月ごと 4 半期ごと 1 年毎非パーティション処理時間 36

37 検索以外の処理時間テーブル サイズ : 3GB 操作内容 索引再作成 パーティション テーブルの 1 件検索処理時間を 10msec とした場合の相対処理時間 ( 実際の処理時間に任意の数を掛けています ) 統計情報取得 挿入 更新 & 削除 パーティション非パーティション処理時間 37

38 検証結果 全データ 1. パーティションを利用すると本当に早くなるのか 全てのデータを取ってくる場合には性能差はない 1 年分 検索時間は半分以下に 一ヶ月ごと 4 半期ごと 1 年毎非パーティション 全てのデータを取ってくる場合は 非パーティション表と比べても検索時間に差はでない 一方 範囲を絞った検索 ( 例 :1 年分のデータ ) を行った場合 非パーティション表と比べ 大幅に検索時間を削減 38

39 検証結果 2. パーティションは細かく切れば切るほどよいのか 1 年分 3 ケ月分 1 ケ月分 一ヶ月ごと 4 半期ごと 1 年毎 パーティションの単位が細かければ細かいほど より範囲を絞った検索を行った際に 検索時間は早くなる 一方 パーティションの単位が細かければ細かいほど 管理が必要なパーティション表の数は増える 39

40 検証結果 3. 複数のパーティションをまたぐ検索ではパフォーマンスが落ちるのでは 1 年分 3 ケ月分 一ケ月ごとのパーティション表は 12 個のパーティション表にアクセス 1 ケ月分 一ヶ月ごと 4 半期ごと 1 年毎 パーティション表をまたがった検索を行った場合でも さほど検索時間には影響を与えない 40

41 検証結果 4. 検索以外の処理でも効果はあるのか 索引再作成 統計情報取得 挿入 更新 & 削除 パーティション 非パーティション 索引の再作成 統計情報の取得といったメンテナンス作業もパーティション単位で行えるので高速 DML 処理に関しても 非パーティション化と比べ パフォーマンスが悪くなることはない 41

42 まとめ パーティションの概要 大きな表や索引をデータベース内部で複数の領域に分割して管理する機能 パーティションの管理 通常の表と同様にメンテナンス作業が可能 パーティション検証 必要なデータのみにアクセスすることで大幅に処理時間を短縮 42

43 Agenda パーティションの概要 パーティションの管理 パーティション検証 まとめ Appendix 資料中に掲載されている検証内容は 特定の環境における検証結果についての報告であり すべての環境において同様の動作を保証するものではありませんので予めご了承下さい 43

44 パーティション情報の確認方法 パーティションの種類の確認 select TABLE_NAME,PARTITIONING_TYPE from dba_part_tables; TABLE_NAME PARTITIONING_TYPE 売上表 RANGE パーティション表名の確認 select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from dba_tab_partitions; TABLE_NAME PARTITION_NAME HIGH_VALUE 売上表 P_1 TO_DATE( 売上表 P_2 TO_DATE(

45 パーティション プルーニング パーティション プルーニングが機能するには WHERE 句にパーティション キーを指定する必要がある 実行計画を確認してパーティションプルーニングが機能しているか確認する Id Operation Name Rows Bytes Cost (%CPU) Time Pstart Pstop SELECT STATEMENT (20) 00:00:03 1 SORT AGGREGATE PARTITION RANGE SINGLE K 172 (20) 00:00: * 3 TABLE ACCESS FULL 売上表 K 172 (20) 00:00:

46 パーティション プルーニング パーティション キーに対して関数や算術を使用した場合 パーティション プルーニングは機能しない SQL> SELECT COUNT(*) FROM 売上表 2 WHERE TO_CHAR( 売上日,'YYYYMM')='200605'; Id Operation Name Rows Bytes Cost (%CPU) Time Pstart Pstop SELECT STATEMENT 526K 13M 669 (19) 00:00:09 1 PARTITION RANGE ALL 526K 13M 669 (19) 00:00: * 2 TABLE ACCESS FULL 売上表 526K 13M 669 (19) 00:00:

47 パーティション プルーニング WHERE 句にバインド変数を利用した場合 解析時には判断できない 実行時にオプティマイザによりパーティション プルーニングが機能するかどうかを決定 SQL> SELECT count(*) FROM 売上表 2 WHERE 売上日 BETWEEN TO_DATE(:start_date, YYYYMMDD ) 3 AND TO_DATE(:end_date, YYYYMMDD ); Id Operation Name Rows Bytes Cost (%CPU) Time Pstart Pstop 3 PARTITION RANGE ITERATOR (38) 00:00:11 KEY KEY * 4 TABLE ACCESS FULL 売上表 (38) 00:00:11 KEY KEY

48 パーティション表の移動を伴う更新 パーティション表の移動が伴う更新 (Update) を行うとデフォルトではエラーが返る update 売上表 set time_id = to_date(' ','yyyymmdd') where time_id= to_date(' ','yyyymmdd'); update sh.sales_partition_quarter2 set time_id = to_date(' ','yyyymmdd') * 行 1 でエラーが発生しました : ORA-14402: パーティション キー列を更新するとパーティションが変更されます パーティション表の移動が伴う更新 (Update) を許可するには Row Movement を有効にする ALTER TABLE 売上表 ENABLE ROW MOVEMENT ; 48

49 パーティションの種類リスト パーティション 特定のデータ ( 製品名 店舗名 etc) のカテゴリーによって表を分割 VALUES キーワードで値とパーティションの対応を決定 売上表 東京神奈川 関東 VALUES ( 東京, 神奈川 ) 大阪京都 福岡長崎 近畿 九州 VALUES ( 大阪, 京都 ) VALUES ( 福岡, 長崎 ) 宮城青森東北 VALUES ( 宮城, 青森 ) CREATE TABLE 売上表 ( 商品 ID number(4) not null, 場所 varchar2(60), 顧客 ID varchar2(40)) PARTITION BY LIST( 場所 ) パーティション化するキー列を指定 (PARTITION Sales_p_kanto VALUES ( 東京, 神奈川 ') TABLESPACE KANTO, PARTITION Sales_p_kinki VALUES ( 大阪, 京都 ') TABLESPACE KINKI, PARTITION Sales_p_kyushu VALUES ( 福岡, 長崎 ') TABLESPACE KYUSHU, PARTITION Sales_p_tohoku VALUES ( 宮城, 青森 ') TABLESPACE TOHOKU); VALUES キーワードを使用 49

50 リスト パーティションの VALUE 追加 不連続なデータをひとつのカテゴリーとして管理するリスト パーティションは 後からカテゴリーを追加する事もできます ALTER TABLE SALES MODIFY PARTITION kanto ADD VALUES ( 群馬, 千葉 ); カテゴリーを追加するパーティションを指定 追加するカテゴリーを明記 売上表 東京神奈川 大阪京都 関東 近畿 VALUES ( 東京, 神奈川 群馬, 千葉 ) VALUES ( 大阪, 京都 ) 福岡長崎 九州 VALUES ( 福岡, 長崎 ) 宮城青森東北 VALUES ( 宮城, 青森 ) リスト パーティションの場合 複合索引キーは作成できません 50

51 パーティションの種類ハッシュ パーティション ハッシュ値を利用してデータを均等に配分するパーティション表 パーティションの数を指定すると 自動的にデータが分割される 売上表 ハッシュ値 1 ハッシュ値 2 25 万件 25 万件 ハッシュ値 3 ハッシュ値 4 25 万件 25 万件 CREATE TABLE 売上表 ( 商品 ID number(5) not null, 場所 varchar2(60), 顧客 ID number(38)) PARTITION BY HASH ( 商品 ID ) なるべく一意な列にする必要がある PARTITIONS 4 パーティションの数を指定 STORE IN (Users1, Users2, Users3, Users4); ハッシュアルゴリズムの関係で なるべく均等に配分するためには パーティションの数は 2 の累乗に設定します 51

52 パーティションの種類リファレンス パーティション 親子関係を利用したパーティショニング手法 親表を参照する子表の外部キーを利用 例 ) 親子関係の表 Orders と OrderItems におけるリファレンス パーティション 親表 :order_date をキーにしてパーティション化 子表 :orderitems_fk をキーにしてパーティション化 親 :Orders 表名 Orders 列名 order_id order_date customer_id 参照整合性制約名 orderitems_fk 子 :OrderItems 表名 OrderItems 列名 order_id line_item_id product_id quantity 子表には order_date 列が存在しない 52

53 リファレンス パーティション子表の作成 CREATE TABLE orderitems( order_idnumber(12) NOT NULL, line_item_id NUMBER(3) NOT NULL, product_id NUMBER(6) NOT NULL, quantity NUMBER(8), CONSTRAINT orderitems_fk FOREIGN KEY (order_id) REFERENCES orders(order_id) ) PARTITION BY REFERENCE (orderitems_fk) ; 外部キーを設定するカラムに NOT NULL 制約を設定する 2 3 親表の主キー又は一意キーを参照する外部キーを作成する PARTITION BY REFERENCE 句に その外部キーを指定する 53

54 パーティションの種類バーチャルカラム パーティション 実データは持たず 他のカラムから関数によって定義される仮想的な列 例 ) acc_no( アカウント番号 ) の上 2 桁をacc_branch( 支店番号 ) として用いる場合 CREATE TABLE accounts (acc_no number(10) not null, acc_name varchar2(50) not null, acc_branch number(2) GENERATED ALWAYS AS (to_number(substr(to_char(acc_no),1,2))) VIRTUAL ); acc_no acc_name acc_branch Adams Blake King Smith 13 補足 :GENERATED ALWAYS 及び VIRTUAL 句は構文を明確にするためにつける語句であり 省略可能 54

55 パーティションの種類バーチャルカラム パーティション バーチャルカラムをパーティション キーとしたパーティショニング手法 実カラムを追加することなく 目的のパーティションを作成することが可能 CREATE TABLE accounts ( acc_no number(10) not null, acc_name varchar2(50) not null,... acc_branch number(2) GENERATED ALWAYS AS ( to_number(substr(to_char(acc_no),1,2)) VIRTUAL ) PARTITION BY LIST (acc_branch) ( PARTITION brunch_p12 VALUES (12), PARTITION others VALUES (default) ); 55

56 コンポジット パーティションレンジ - リスト コンポジット パーティション レンジ パーティションをさらにリスト パーティションに分割 例えば 売上表を 期間 ( レンジ ) と 地域 ( リスト ) で分割する 毎日支店ごとにデータローディング レンジ パーティション リスト サブパーティション A 支店 B 支店 C 支店 D 支店 2008 年 6 月 1 日 ( 売上日 ) 全支店横断的に検索 2008 年 6 月 2 日 ( 売上日 ) 2008 年 6 月 3 日 ( 売上日 ) 2008 年 6 月 4 日 ( 売上日 ) 支店ごとの売上データの集計 56

57 コンポジット パーティションレンジ - リスト コンポジット パーティション作成例 CREATE TABLE 売上表 ( 売上日 DATE, 地域 VARCHAR2(20), 売上 NUMBER ) PARTITION BY RANGE ( 売上日 ) SUBPARTITION BY LIST ( 地域 ) (PARTITION P2009Q1 レンジ パーティションリスト パーティション VALUES LESS THAN(to_date(' ','YYYY-MM-DD')) (SUBPARTITION P2009Q1_kanto VALUES ( kanagawa','tokyo'), SUBPARTITION P2009Q1_kansai VALUES ('Osaka', kyoto') ), PARTITION P2009Q2 VALUES LESS THAN(to_date(' ','YYYY-MM-DD')) (SUBPARTITION P2009Q2_kanto VALUES ( kanagawa','tokyo'), SUBPARTITION P2009Q2_kansai VALUES ('Osaka', kyoto') ) ); 57

58 INSERT SELECT による移行 INSERT... SELECT による移行手順 (1) パーティション表のための表領域を作成 (2) 空のパーティション表を作成 (3) 非パーティション表が格納されている表領域を READ ONLY に変更 (4) 非パーティション表のデータを (2) へ挿入 (5) 非パーティション表が格納されていた表領域を READ WRITE に変更し 非パーティション表を削除 (6) (2) を (7) で削除した非パーティション表の表名へリネームする (7) パーティション表にローカル索引を作成 (8) パーティション表に一意キー制約を追加 58

59 INSERT SELECT による移行 INSERT... SELECT による移行手順 (1) パーティション表のための表領域を作成 SQL> CREATE TABLESPACE tbs DATAFILE 'D: ORACLE DATA USER01.dbf' SIZE 5G; SQL> CREATE TABLESPACE tbs DATAFILE 'D: ORACLE DATA USER01.dbf' SIZE 5G; ( 中略 ) SQL> CREATE TABLESPACE tbs DATAFILE 'D: ORACLE DATA USER01.dbf' SIZE 5G; SQL> CREATE TABLESPACE tbs DATAFILE 'D: ORACLE DATA USER01.dbf' SIZE 5G; SQL> CREATE TABLESPACE tbs DATAFILE 'D: ORACLE DATA USER01.dbf' SIZE 5G; 作成した表領域を移行を実行するユーザーが使用できるようにします DBA 権限のユーザーで行います 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; 59

60 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 testpt values less than (to_date(' ','yyyy-mm-dd')) TABLESPACE tbs200201, PARTITION testpt values less than (to_date(' ','yyyy-mm-dd')) TABLESPACE tbs200202, ( 中略 ) PARTITION testpt values less than (to_date(' ','yyyy-mm-dd')) TABLESPACE tbs200811, PARTITION testpt values less than (to_date(' ','yyyy-mm-dd')) TABLESPACE tbs200812); 60

61 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; 61

62 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; 62

63 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; 63

64 オンライン再定義による移行 オンライン再定義による移行手順 (1) 移行を実行するユーザーに権限を付与 (2) パーティション表のための表領域を作成 (3) 非パーティション表 ( 元表 ) がオンラインで再定義可能か確認 (4) 移行用のパーティション表 ( 仮表 ) を作成 (5) オンライン再定義を開始 (6) 仮表に対してローカル索引を作成 (7) 元表と仮表の差分を同期 (8) オンライン再定義を終了 (9) 元表を削除 64

65 オンライン再定義による移行 オンライン再定義による移行手順 (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; 65

66 オンライン再定義による移行 オンライン再定義による移行手順 (2) パーティション表のための表領域を作成 SQL> CREATE TABLESPACE tbs DATAFILE 'D: ORACLE DATA USER01.dbf' SIZE 5G; SQL> CREATE TABLESPACE tbs DATAFILE 'D: ORACLE DATA USER01.dbf' SIZE 5G; ( 中略 ) SQL> CREATE TABLESPACE tbs DATAFILE 'D: ORACLE DATA USER01.dbf' SIZE 5G; SQL> CREATE TABLESPACE tbs DATAFILE 'D: ORACLE DATA USER01.dbf' SIZE 5G; SQL> CREATE TABLESPACE tbs DATAFILE 'D: ORACLE DATA USER01.dbf' SIZE 5G; 作成した表領域を移行を実行するユーザーが使用できるようにします DBA 権限のユーザーで行います 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; 66

67 オンライン再定義による移行 オンライン再定義による移行手順 (3) 非パーティション表 ( 元表 ) がオンラインで再定義可能か確認 SQL> BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE( UNAME => 'SHOPMSTR', TNAME => 'ORDERITEM', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK, PART_NAME => NULL); END; / 67

68 オンライン再定義による移行 オンライン再定義による移行手順 (4) 移行用のパーティション表 ( 仮表 ) を作成 移行を実行するユーザーで行います 作成するパーティション表の定義は 非パーティション表と同一でなければなりません SQL> CREATE TABLE int_orderitem( shipdate date, orderkey number NOT NULL, quantity number NOT NULL, comment VARCHAR2(44) ) PARTITION by range (shipdate) ( PARTITION testpt values less than (to_date(' ','yyyy-mm-dd')) TABLESPACE tbs200201, PARTITION testpt values less than (to_date(' ','yyyy-mm-dd')) TABLESPACE tbs200202, ( 中略 ) PARTITION testpt values less than (to_date(' ','yyyy-mm-dd')) TABLESPACE tbs200811, PARTITION testpt values less than (to_date(' ','yyyy-mm-dd')) TABLESPACE tbs200812); 68

69 オンライン再定義による移行 オンライン再定義による移行手順 (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; / 69

70 オンライン再定義による移行 オンライン再定義による移行手順 (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; 70

71 オンライン再定義による移行 オンライン再定義による移行手順 (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; 71

72 グローバル パーティション索引 グローバル パーティション作成例 注文番号 列でパーティション化されているパーティション表に対して 顧客 ID 列をキーとしたグローバル パーティション索引を作成 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 顧客 ID 顧客 ID 顧客 ID 1~99 100~ ~ ~399 オーダー表 注文番号 注文番号 注文番号 注文番号 1~99 100~ ~ ~399 72

73 パーティション単位での結合処理パーティション ワイズ ジョイン利用条件 パーティション ワイズ ジョインが行われる為には以下の条件を満たしている必要が有ります 1. 同じ型のカラムをパーティション キーとし 同じパーティショニング手法によって同じパーティション数に分割されている 2. レンジ パーティションの場合 対応するパーティション バウンド ( パーティションの境界値 ) が指定されている 3. 少なくとも一方のテーブルがコンポジット パーティショニングされている場合には レンジ パーティショニングまたはハッシュ パーティショニングのいずれかの面で上記の条件を満たしている 4. 検索時にパーティション キー列を使って結合する必要がある 表の設計の段階からパーティション ワイズ ジョインが頻繁に起こるようにします 73

74 パーティション表とメンテナンス パーティションの追加 既存のパーティション表に新しくパーティションを追加します ハッシュ パーティションの場合 ALTER TABLE sales ADD PARTITION p_2 TABLESPACE Users2; リスト パーティションの場合 ALTER TABLE q1_sales_by_region ADD PARTITION q1_kanto VALUES ( TOKYO', KANAGAWA') TABLESPACE KANTO; リスト パーティション表に関連付けられたローカル索引およびグローバル索引は そのまま使用可能です 74

75 パーティション表とメンテナンス パーティションの Move 別の表領域へのパーティションの移動 表圧縮を使用した圧縮形式でのデータの格納 ALTER TABLE SALES MOVE PARTITION P TABLESPACE Q4; 索引も併せて移動したい場合は以下の UPDATE INDEXES オプション句を使用することで実現できます ALTER TABLE SALES MOVE PARTITION P TABLESPACE Q4 UPDATE INDEXES (IDX1 (PARTITION P TABLESPACE Q4)); コマンド実行中は実行対象となっているパーティションに対する DML 操作が許可されません 75

76 パーティション表とメンテナンス パーティション単位での統計情報の収集方法 EXECUTE DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => USER_NAME, TABNAME => SALES, PARTNAME => P200901, GRANULARITY => PARTITION, DEGREE => DBMS_STATS.AUTO_DEGREE); PARTNAME : パーティションの名前を記載 GRANULARITY: 収集する統計情報の細分化の指定 PARTITION - パーティション レベルの統計情報を収集します SUBPARTITION - サブパーティション レベルの統計情報を収集します 76

77 パーティション表とデータ圧縮 パーティション表とデータ圧縮を組み合わせる事によって 1 つの表の中で効率的なデータ管理を実現できます 2005 年度売上 2006 年度売上 P2005 P2006 データ圧縮 売上表 2007 年度売上 2008 年度売上 P2007 P2008 更新が頻繁に行われる直近のデータは圧縮せずにそのまま保持 パーティション表全体 またはパーティション単位に圧縮 / 非圧縮を指定可能 77

78 パーティション表とデータ圧縮データ サイズ : 4GB Non-Partition Partition Compress Partition ,000 件 (5%) ,000 件 (2%) 時間 ( 秒 ) 日本 HP 社との共同検証結果より 78

79 参考資料 Oracle Database VLDB およびパーティショニング ガイド 11g リリース 2(11.2) 2/b56316/toc.htm 79

80

81