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

(1)

Oracle

Direct Seminar

日本オラクル株式会社

実践!!データベース管理

～パーティション詳細編～

(2)

以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。

また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことは

できません。以下の事項は、マテリアルやコード、機能を提供することをコミットメン

ト（確約）するものではないため、購買決定を行う際の判断材料になさらないで下さ

い。オラクル製品に関して記載されている機能の開発、リリースおよび時期につい

ては、弊社の裁量により決定されます。

OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。文中の社名、商品名等は各社の商標または登録商標である場合があります。

(3)

• パーティションの概要

• パーティションの管理

• パーティション検証

• まとめ

• Appendix

Agenda

※ 資料中に掲載されている検証内容は、特定の環境における検証結果についての報告であり、すべての環境において同様の動作を保証するものではありませんので予めご了承下さい。

(4)

大規模テーブル運用の問題点

結果

売上表

全く同じSelect文を投げたとしても、

データ量によって結果が返ってくる時間が著しく異なる

• データ量が増えてくると･･･

第4四半期の合計売上

高が見たい

(5)

パーティションとは

通常の１つの表

パーティション化された表

大きな表や索引をデータベース内部で

複数の領域に分割して管理する

内部的に表を分割

ユーザやアプリケーションからは

ひとつの表に見える

(6)

パーティションのメリット

• パフォーマンスの向上

• 必要なデータのみを読み込むのでDISKI/Oを削減できる

• 管理性の向上

• バックアップやデータのローディングをパーティション単位で実行できる

• 障害/保守時の影響を最小化

• 障害やメンテナンスの際、パーティション単位で操作することができ、

障害/保守時の影響を最小化できる

(7)

1.パフォーマンスの向上

パーティション・プルーニング

オプティマイザ

SELECT sum(“売上高”)

FROM “売上表”

WHERE “売上月” between 10 and 12

Oracle

Database

_1～3月

パーティション

4～6月

パーティション

7～9月

パーティション

10～12月

パーティション

10月～12月のデータ

が見たい

売上表

• 対象のデータが

格納されているパーティションだけ

に

アクセスし、不要なパーティションを読み飛ばす

(8)

Demonstration

(9)

Demonstration

1ヶ月の売上を計算するまでにかかる時間を比較

1998年1月

パーティション

1998年2月

パーティション

1998年3月

パーティション

売上表

・

• 5年分のデータ（5000万件）を格納

• １ヶ月の売上合計を計算するSQLを発行

売上表

１ヶ月単位で表を分割

（72パーティションに分割）

VS

１ヶ月の売上が見たい

(10)

2.管理作業もパーティション単位

他のパーティションは

影響を受けない!!

Q1 パーティション Q2 パーティション Q3 パーティション Q4 パーティション売上表

• 例えば、統計情報の取得もパーティション単位

• 作業中は他のパーティションは影響を受けない

(11)

3.パーティション単位で障害の影響を限定

• 特定のパーティションに障害が発生しても、

他のパーティションは影響を受けない

• パーティション単位でリカバリを行えるので、障害から短時間

で復旧できる

パーティション単位でリカバリ実行売上表 2010年Q4 パーティション 2010年Q1 パーティション 2010年Q2 パーティション 2010年Q3 パーティション 2010年Q4 パーティション

Q1~Q3のデータは、

リカバリ中でも

通常通りアクセス可

(12)

パーティションの種類

• 単一レベル・パーティション

• レンジ・パーティション

8i

• リスト・パーティション

9i

• ハッシュ・パーティション

8i

• コンポジット・パーティション

• パーティション化の拡張

• インターバル・パーティション

11g

• リファレンス・パーティション

11g

• バーチャルカラム・パーティション

11g

サブ

レンジ

ハッシュ

メ

イ

ン

レンジ

11g

9i

8i

11g

(13)

• パーティションの概要

• パーティションの管理

• パーティション表の作成と移行

• パーティション索引

• パーティション表の結合

• パーティション表のメンテナンス

• パーティション検証

• まとめ

• Appendix

Agenda

(14)

CREATE TABLE “売上表” (“製品ID” number(4) not null, ”売上日” date,

“顧客ID” varchar2(40))

PARTITION BY RANGE(”売上日”)

(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);

パーティション表の作成

• CREATE TABLE・・・・・・PARTITION句を使用して作成

• レンジパーティションの場合

• VALUES LESS THANキーワードを使用し、分割する範囲を決定

2009年 1～3月 (”売上日”) 2009年 4～6月 (”売上日”) 2009年 7～9月 (”売上日”)

Sales_p1

Sales_p2

Sales_p3

VALUES LESS THAN (2009/04/01)

VALUES LESS THAN (2009/07/01)

VALUES LESS THAN (2009/10/01)

2009年 10～12月

(”売上日”)

Sales_p4 VALUES LESS THAN (2010/01/01)

パーティション化するキー列を指定

VALUES LESS THANキーワードを使用

(15)

パーティション・アドバイザ

• SQLアドバイザ機能の一部

• ワークロード(システムで実行されるSQL文)を解析し、

適切なパーティション構成を推奨する機能

性能向上のための、パーティション構成がアドバイスされる

※1 Oracle Database11g 新機能

※2 Diagnostics Option、Tuning Optionが必要

SQL アクセス・アドバイザが

アクセスパターンや

(16)

空のパーティション表

”仮売上表”

• INSERT ... SELECTによる移行

• 空のパーティション表を作り、非パーティション表からデータを

INSERT … SELECTによって挿入し、その後RENAMEする

既存の表からパーティション表への移行

非パーティション表

”売上表”

×

非パーティション表

”

売上表

”

②DROP

空のパーティション表

”仮売上表”

パーティション表

ORDERITEM

③RENAME

• 表の再定義による移行

• 表の可用性に影響を与えずに表の構造を変更できる機能

• 再定義中も、問合せおよびDMLを使用してその表にアクセス可能

非パーティション表

”売上表”

パーティション表

”仮売上表”

①INSERT

①再定義

(17)

• パーティションの概要

• パーティションの管理

• パーティション表の作成と移行

• パーティション索引

• パーティション表の結合

• パーティション表のメンテナンス

• パーティション検証

• まとめ

• Appendix

Agenda

(18)

パーティション索引

• 2種類のパーティション索引

• ローカル索引



ローカル同一キー索引



ローカル非同一キー索引

• グローバル索引



グローバル同一キー索引

索引

パーティション索引

(19)

ローカル同一キー索引

索引

売上表

CREATE INDEX 索引名 ON パーティション表名(列名)

LOCAL

;

• 表パーティションと

同一

のキーでパーティション化する索引

• 索引のキー列も表パーティションのキー列と

同一である索引

• 基礎となるパーティション表に変更があった場合、

索引パーティションも自動でメンテナンスが行われる

”注文番号” 1~99 ”注文番号” 100~199 ”注文番号” 200~299 ”注文番号” 300~399 ”注文番号” 1~99 ”注文番号” 100~199 ”注文番号” 200~299 ”注文番号” 300~399

パーティション・プルーニングが

機能するにはパーティション・キーを

条件に指定する必要がある

特定の索引パーティションのみにアクセス

注文番号10の

データがみたい

(20)

ローカル非同一キー索引

索引

Order表

CREATE INDEX 索引名 ON パーティション表名(列名)

LOCAL

;

• 表パーティションと

同一

のキーでパーティション化する索引

• 索引のキー列が表パーティションのキー列と

同一でない索引

”注文番号” 1~99 ”注文番号” 100~199 ”注文番号” 200~299 ”注文番号” 300~399 ”顧客ID” 7 ”顧客ID” 12 ・・・ ”顧客ID” 21 ”顧客ID” 97 ・・・ ”顧客ID” 2 ”顧客ID” 81 ・・・ ”顧客ID” 1 ”顧客ID” 21 ・・・

検索条件によっては

パーティションプルーニングが

機能しない

全ての索引パーティションにアクセス

顧客ID21のデータを見たい

(21)

ローカル非同一キー索引

索引

Order表

”注文番号” 1~99 ”注文番号” 100~199 ”注文番号” 200~299 ”注文番号” 300~399 ”顧客ID” 7 ”顧客ID” 12 ・・・ ”顧客ID” 21 ”顧客ID” 97 ・・・ ”顧客ID” 2 ”顧客ID” 81 ・・・ ”顧客ID” 1 ”顧客ID” 21 ・・・

検索条件によっては

パーティションプルーニングが

機能しない

注文番号が1~99の間で

かつ顧客ID21のデータを見たい

特定の索引パーティションのみにアクセス

• 表パーティションと

同一

のキーでパーティション化する索引

• 索引のキー列が表パーティションのキー列と

同一でない索引

(22)

グローバル・パーティション索引

• 表パーティションとは

別のキー

でパーティション化する索引

• 基礎となるパーティション表に変更があった場合、

手動で索引パーティションのメンテナンスが必要になる

• パーティション化された表に関係なく、

自由に索引のパーティション化が可能

CREATE INDEX 索引名 ON 表名(列名)

GLOBAL PARTITION BY …

索引

Order表

”注文番号” 1~99 ”注文番号” 100~199 ”注文番号” 200~299 ”注文番号” 300~399

特定の索引パーティションにア

クセスするが、複数のパーティ

ション表にアクセス

顧客ID21のデータを見たい

”顧客

ID”

100~199

”顧客

ID”

200~299

”顧客

ID”

300~399

”顧客

ID”

1~99

※グローバル・パーティション索引の作成例についてはAPPENDIXをご確認ください。

(23)

パーティション索引のガイドライン

表にアクセスするアプリケーションの特性を考慮に入れ作成

索引を作成する列がパーティション表のパーティション・キーと同一

ローカル同一キー索引

対象のパーティション表や索引に対してメンテナンスを頻繁に行う

ローカル非同一キー索引

システムのタイプはDSSタイプかOLTPタイプか

ローカル非同一キー索引

グローバル索引

はい

いいえ

はい

いいえ

DSS

OLTP

(24)

• パーティションの概要

• パーティションの管理

• パーティション表の作成と移行

• パーティション索引

• パーティション表の結合

• パーティション表のメンテナンス

• パーティション検証

• まとめ

• Appendix

Agenda

(25)

パーティション単位での結合処理

パーティション・ワイズ・ジョイン

• パーティション表同士をパーティション・キー列によって、

パーティション単位で結合処理を行うことができる

• ジョイン作業の回数が少なくなり、レスポンス時間を短縮

• 結合に必要なメモリー量の減少

読み込み＆

結合処理

読み込み＆

結合処理

読み込み＆

結合処理

読み込み＆

結合処理

顧客表

_売上表

(26)

• パーティションの概要

• パーティションの管理

• パーティション表の作成と移行

• パーティション索引

• パーティション表の結合

• パーティション表のメンテナンス

• パーティション検証

• まとめ

• Appendix

Agenda

(27)

パーティション表に行えるメンテナンス作業

• 通常の表と同様、パーティション表に対しても

メンテナンス作業を行う事が可能

• パーティションの追加

• ADD PARTITION

• パーティションの削除

• DROP PARTITION

• パーティションのマージ

• MERGE PARTITIONS

• パーティションの結合

• COALESCE PARTITION

ADD PARTITION

DROP PARTITION

Q1

Q2

Q3

Q4

(28)

パーティションの追加

• 既存のパーティション表に新しくパーティションの追加が可能

【レンジ・パーティションの場合】

ALTER TABLE ”売上表”

ADD PARTITION

P200805

VALUES LESS

THAN

(TO_DATE('2009/4/01','YYYY/MM/DD')) TABLESPACE Users1;

MAXVALUE句を使用すると、

パーティションの追加が

できない

売上表

MAXVALUE 2008年10~12月 2008年7~9月 2008年4~6月 2008年1~3月 2009年1~3月

追加パーティション

VALUE句で指定した範囲の

一番最後にしか追加できない

2008年6~8月

追加パーティション

(29)

インターバルパーティションによる自動追加

• 定義したインターバル期間に従い、レコードの挿入時に

必要に応じて自動的にパーティションが追加される

• DBAの

管理業務コストを軽減

することが可能

• レンジ・パーティションから変更することも可能

2007年11月売上表 2008年2月・・・

DBA

USER

自動で追加

Z

ZZ

○

CREATE TABLE ”売上表”(“売上日” date, ・・・) PARTITION BY RANGE(“売上日”)

INTERVAL(NUMTOYMINTERVAL(1, „month‟))STORE IN (TS1, TS2, TS3)

(PARTITION P200811 VALUES LESS THAN(TO_DATE('2008/12/01','YYYY/MM/DD')));

通常のレンジ・パーティションに

INTERVAL句

を追加。キーには、

NUMBER型、DATE型のみ指定可能

※11g新機能

ALTER TABLE テーブル名 SET INTERVAL(インターバル期間);

(30)

パーティションの削除

• パーティション表内のデータも削除

• ハッシュ・パーティション以外のパーティションで実行可能

• ハッシュ・パーティションの場合は結合操作を実行

ALTER TABLE ”売上表”

DROP PARTITION

p_2;

削除したパーティションは

フラッシュ・バックで再現不可

売上表

2008年7~9月 2008年4~6月 2008年1~3月 2008年7~9月

削除パーティション

2008年10~12月

リサイクルビン

売上表

2008年4~6月 2008年1~3月 2008年7~12月

削除後

(31)

パーティションの結合

• データを残しパーティション表のみを削除する場合は結合操作を実行

• ハッシュ・パーティション表の場合：

• それ以外の場合：

ALTER TABLE “売上表” COALESCE PARTITION;

ALTER TABLE “売上表” MERGE PARTITIONS P1, P2 INTO P2

TABLESPACE USERS;

売上表

2008年7~9月 2008年4~6月 2008年1~3月 2008年10~12月

売上表

2008年4~6月 2008年1~3月 2008年7~12月

(32)

• パーティションの概要

• パーティションの管理

• パーティション検証

• まとめ

• Appendix

Agenda

(33)

パーティション検証

1.パーティションを利用すると本当に早くなるのか。

2.パーティションは細かく切れば切るほどよいのか。

3.複数のパーティションをまたぐ検索ではパフォーマンスが落ちるのでは。

(34)

参考 : 検証環境

HP BladeSystem c-Class ストレージ HP StorageWorks MSA1000

• ハードウェア構成

• サーバーマシン

• ProLiant DL380 G3

• インテル

® Xeon™ プロセッサ 3.20GHz x2

• ストレージ装置

• HP StorageWorks MSA1000

• ソフトウェア構成

• Red Hat Enterprise Linux 5

(35)

テーブル情報

• テーブル属性

• 10カラム, 約100Byte / record

• テーブル・タイプ

• パーティション・テーブル（レンジ・パーティション）

• 通常表

• パーティション・サイズ

• 1ヶ月毎/72 Partition

• 4半期毎/24 Partition

• 1年毎/6 Partition

• テーブル・サイズ

• 3GB （5年分）

・・・

売上表

(36)

検索処理時間 (1回あたりの検索処理時間)

テーブル・サイズ : ３GB

※ パーティション・テーブルの1件検索処理時間を 10msecとした場合の相対処理時間 ( 実際の処理時間に任意の数を掛けています )

0

10

20

30

40

50

60

70 1日

1ケ月分

3ケ月分

1年分

全データ

一ヶ月ごと

4半期ごと

1年毎

非パーティション

処理時間

取得データ量

（5000万件）

（1000万件）

（300万件）

（100万件）

（2万件）

(37)

検索以外の処理時間

テーブル・サイズ : ３GB

0

200

400

600

800 1000

更新＆削除

挿入

統計情報取得

索引再作成

パーティション

非パーティション

処理時間

操作内容

※ パーティション・テーブルの1件検索処理時間を 10msecとした場合の相対処理時間 ( 実際の処理時間に任意の数を掛けています )

(38)

検証結果

1.パーティションを利用すると本当に早くなるのか。

0

10

20

30

40

50

60

70 1年分

全データ

一ヶ月ごと

4半期ごと

1年毎

非パーティション

検索時間は半分以下に

全てのデータを取ってくる

場合には性能差はない

• 全てのデータを取ってくる場合は、非パーティション表と比べても検索時

間に差はでない

• 一方、範囲を絞った検索（例：1年分のデータ）を行った場合、

非パーティション表と比べ、大幅に検索時間を削減

(39)

検証結果

2.パーティションは細かく切れば切るほどよいのか。

• パーティションの単位が細かければ細かいほど、より範囲を絞った検索

を行った際に、検索時間は早くなる

• 一方、パーティションの単位が細かければ細かいほど、

管理が必要なパーティション表の数は増える

0

5

10

15

20 1ケ月分

3ケ月分

1年分

一ヶ月ごと

4半期ごと

1年毎

(40)

検証結果

3.複数のパーティションをまたぐ検索ではパフォーマンスが落ちるのでは。

一ケ月ごとのパーティション表は

１２個のパーティション表に

アクセス

• パーティション表をまたがった検索を行った場合でも、

さほど検索時間には影響を与えない

0

5

10

15

20 1ケ月分

3ケ月分

1年分

一ヶ月ごと

4半期ごと

1年毎

(41)

検証結果

4.検索以外の処理でも効果はあるのか。

• 索引の再作成、統計情報の取得といったメンテナンス作業も

パーティション単位で行えるので高速

• DML処理に関しても、非パーティション化と比べ、パフォーマンスが悪く

なることはない

0

200

400

600

800 1000

更新＆削除

挿入

統計情報取得

索引再作成

パーティション

非パーティション

(42)

まとめ

• パーティションの概要

大きな表や索引をデータベース内部で複数の領域に

分割して管理する機能

• パーティションの管理

通常の表と同様にメンテナンス作業が可能

• パーティション検証

必要なデータのみにアクセスすることで

大幅に処理時間を短縮

(43)

• パーティションの概要

• パーティションの管理

• パーティション検証

• まとめ

• Appendix

Agenda

(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(„2001-01-01‟・・・}

”売上表”

_{P_2 TO_DATE(„2002-01-01‟・・・}

(45)

パーティション・プルーニング

• パーティション・プルーニングが機能するにはWHERE句に

パーティション・キーを指定する必要がある

• 実行計画を確認してパーティションプルーニングが

機能しているか確認する

---| Id ---| Operation ---| Name ---| Rows ---| Bytes ---| Cost

(%CPU)| Time |

Pstart

|

Pstop

|

-

---| 0 ---| SELECT STATEMENT ---| ---| 1 ---| 8 ---| 172

(20)| 00:00:03 | | |

| 1 | SORT AGGREGATE | | 1 | 8 |

| | | |

| 2 |

PARTITION RANGE SINGLE

| | 43146 | 337K| 172

(20)| 00:00:03 |

2 |

|* 3 | TABLE ACCESS FULL | ”売上表” | 43146 | 337K| 172

(20)| 00:00:03 |

2 |

(46)

---SQL> SELECT COUNT(*) FROM ”売上表”

2 WHERE TO_CHAR(”売上日”,'YYYYMM')='200605';

---| Id ---|

Operation

| Name

| Rows | Bytes | Cost

(%CPU)| Time

|

Pstart

|

Pstop

|

---

---| 0 ---| SELECT STATEMENT

|

| 526K|

13M| 669

(19)| 00:00:09 |

|

| 1 |

PARTITION RANGE ALL

|

| 526K|

13M| 669

(19)| 00:00:09 |

1 |

4 |

|* 2 | TABLE ACCESS FULL | ”売上表” | 526K|

13M| 669

(19)| 00:00:09 |

1 |

4 |

---

---パーティション・プルーニング

• パーティション・キーに対して関数や算術を使用した場合、

パーティション・プルーニングは機能しない

(47)

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

|

| 1317 | 10536 |

867 (38)| 00:00:11 |

KEY

|

KEY

|

|* 4 |

TABLE ACCESS FULL

| ”売上表” | 1317 | 10536 |

867 (38)| 00:00:11 | KEY | KEY |

---パーティション・プルーニング

• WHERE句にバインド変数を利用した場合、

解析時には判断できない

• 実行時にオプティマイザによりパーティション・プルーニングが

機能するかどうかを決定

(48)

パーティション表の移動を伴う更新

• パーティション表の移動が伴う更新（Update）を行うと

デフォルトではエラーが返る

update “売上表” set time_id = to_date('20000101','YYYYMMDD')

where time_id= to_date('19990101','YYYYMMDD');

update sh.sales_partition_quarter2 set time_id = to_date('20000101','YYYYMMDD')

*

行1でエラーが発生しました。:

ORA-14402: パーティション・キー列を更新するとパーティションが変更されます。

• パーティション表の移動が伴う更新（Update）を行うと

デフォルトではエラーが返る

• パーティション表の移動が伴う更新（Update）を

許可するにはRow Movementを有効にする

(49)

パーティションの種類

リスト・パーティション

• 特定のデータ（製品名、店舗名 etc）のカテゴリーによって

表を分割

• 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

キーワードを

使用

パーティション化するキー列を指定

東京神奈川大阪京都福岡長崎

関東

近畿

九州

VALUES („“東京”‟,‟“神奈川”‟)

VALUES („“大阪”‟,‟“京都”‟)

VALUES („“福岡”‟,‟“長崎”‟)

宮城青森

東北

VALUES („“宮城”‟,‟“青森”‟)

売上表

(50)

ALTER TABLE SALES

MODIFY PARTITION kanto

ADD VALUES („“群馬”‟, „“千葉”„);

リスト・パーティションのVALUE追加

• 不連続なデータをひとつのカテゴリーとして管理するリスト・

パーティションは、後からカテゴリーを追加する事もできます

※リスト・パーティションの場合、複合索引キーは作成できません。

追加するカテゴリーを明記

カテゴリーを追加するパーティションを指定

東京神奈川大阪京都福岡長崎

関東

近畿

九州

VALUES („“東京”‟,‟“神奈川”‟

“群馬”

‟, „“千葉”

)

VALUES („“大阪”‟,‟“京都”‟)

VALUES („“福岡”‟,‟“長崎”‟)

宮城青森

東北

VALUES („“宮城”‟,‟“青森”‟)

売上表

(51)

パーティションの種類

ハッシュ・パーティション

• ハッシュ値を利用してデータを

均等に配分

するパーティション表

• パーティションの数を指定すると、自動的にデータが分割される

※ハッシュアルゴリズムの関係で、なるべく均等に配分するためには、パーティションの数は2の累乗に設定します CREATE TABLE “売上表” (“商品ID” number(5) not null,

“場所” varchar2(60), ”顧客ID” number(38))

PARTITION BY HASH (“商品ID”) PARTITIONS 4

STORE IN (Users1, Users2 , Users3 , Users4);

なるべく一意な列にする必要がある

パーティションの数を指定

ハッシュ値1 ハッシュ値2 ハッシュ値3 ハッシュ値4

売上表

25万件

(52)

パーティションの種類

リファレンス・パーティション

• 親子関係を利用したパーティショニング手法

• 親表を参照する子表の外部キーを利用

• 例) 親子関係の表 OrdersとOrderItemsにおける

リファレンス・パーティション

• 親表：order_dateをキーにしてパーティション化

• 子表：orderitems_fkをキーにしてパーティション化

表名

列名

Orders

order_id

order_date

customer_id

表名

列名

OrderItems order_id

line_item_id

product_id

quantity

子表には order_date列が存在しない親:Orders

参照整合性制約名

orderitems_fk

子：OrderItems

(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)

;

1

2

3

1

3 外部キーを設定するカラムに、NOT NULL制約を設定する

親表の主キー又は一意キーを参照する外部キーを作成する

PARTITION BY REFERENCE句に、その外部キーを指定する

(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

);

補足：GENERATED ALWAYS 及び VIRTUAL句は構文を明確にするためにつける語句であり、省略可能

acc_no

acc_name

acc_branch

12500

Adams

12 12507

Blake

12 12666

King

12

(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),

・・・

(56)

全支店横断的に検索

毎日支店ごとにデータローディング

コンポジット・パーティション

レンジ-リスト・コンポジット・パーティション

• レンジ・パーティションをさらにリスト・パーティションに分割

• 例えば、売上表を「期間」（レンジ）と「地域」（リスト）で分割する

A支店 B支店 C支店 D支店 2008年 6月1日 (”売上日”) 2008年 6月2日 (”売上日”) 2008年 6月3日 (”売上日”) 2008年 6月4日 (”売上日”) レンジ・パーティションリスト・サブパーティション支店ごとの売上データの集計

(57)

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')

),

PARTITION P2009Q2

VALUES LESS THAN(to_date('2009-07-01','YYYY-MM-DD'))

(

SUBPARTITION P2009Q2_kanto VALUES (‘kanagawa','Tokyo'),

SUBPARTITION P2009Q2_kansai VALUES ('Osaka',‘kyoto')

)

);

コンポジット・パーティション

レンジ-リスト・コンポジット・パーティション作成例

レンジ・パーティション

リスト・パーティション

(58)

INSERT … SELECTによる移行

INSERT ... SELECTによる移行手順

(1) パーティション表のための表領域を作成

(2) 空のパーティション表を作成

(3) 非パーティション表が格納されている表領域をREAD ONLYに変更

(4) 非パーティション表のデータを(2)へ挿入

(5) 非パーティション表が格納されていた表領域をREAD WRITEに

変更し、非パーティション表を削除

(6) (2)を、(7)で削除した非パーティション表の表名へリネームする

(7) パーティション表にローカル索引を作成

(8) パーティション表に一意キー制約を追加

(59)

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権限のユーザーで行います。

(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 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, …(中略)…

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

(62)

INSERT … SELECTによる移行

INSERT ... SELECTによる移行手順

(5) 非パーティション表が格納されていた表領域をREAD WRITEに変更し、

非パーティション表を削除

DBA権限のユーザーで行います。

SQL> ALTER TABLESPACE USERS READ WRITE; SQL> DROP TABLE orderitem;

(6) パーティション表を、非パーティション表の表名へリネームする

移行を実行するユーザーで行います。

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

(64)

オンライン再定義による移行

オンライン再定義による移行手順

(1) 移行を実行するユーザーに権限を付与

(2) パーティション表のための表領域を作成

(3) 非パーティション表(元表)がオンラインで再定義可能か確認

(4) 移行用のパーティション表(仮表)を作成

(5) オンライン再定義を開始

(6) 仮表に対してローカル索引を作成

(7) 元表と仮表の差分を同期

(8) オンライン再定義を終了

(9) 元表を削除

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

(66)

オンライン再定義による移行

オンライン再定義による移行手順

(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権限のユーザーで行います。

(67)

オンライン再定義による移行

オンライン再定義による移行手順

(3) 非パーティション表(元表)がオンラインで再定義可能か確認

SQL> BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE( UNAME => 'SHOPMSTR', TNAME => 'ORDERITEM', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK, PART_NAME => NULL); END; /

(68)

オンライン再定義による移行

オンライン再定義による移行手順

(4) 移行用のパーティション表(仮表)を作成

移行を実行するユーザーで行います。

作成するパーティション表の定義は、非パーティション表と同一でなければなりません。

SQL> CREATE TABLE int_orderitem( shipdate date,

orderkey number NOT NULL, quantity number NOT NULL, comment VARCHAR2(44) )

PARTITION by range (shipdate) (

(to_date('2002-03-01','YYYY-MM-DD')) TABLESPACE tbs200202, …(中略)…

(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; /

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

(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)で作成した仮表の名前であることに注意してください。

(72)

グローバル・パーティション索引

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

(73)

• パーティション・ワイズ・ジョインが行われる為には以下の条

件を満たしている必要が有ります

1. 同じ型のカラムをパーティション・キーとし、同じパーティショニング手

法によって同じパーティション数に分割されている

2. レンジ・パーティションの場合、対応するパーティション・バウンド（パ

ーティションの境界値）が指定されている

3. 少なくとも一方のテーブルがコンポジット・パーティショニングされて

いる場合には、レンジ・パーティショニングまたはハッシュ・パーティ

ショニングのいずれかの面で上記の条件を満たしている

4. 検索時にパーティション・キー列を使って結合する必要がある

パーティション単位での結合処理

パーティション・ワイズ・ジョイン利用条件

表の設計の段階からパーティション・ワイズ・ジョインが

頻繁に起こるようにします

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

【ハッシュ・パーティションの場合】

(75)

パーティション表とメンテナンス

• パーティションのMove



別の表領域へのパーティションの移動



表圧縮を使用した圧縮形式でのデータの格納



索引も併せて移動したい場合は以下のUPDATE INDEXESオプシ

ョン句を使用することで実現できます



コマンド実行中は実行対象となっているパーティションに対するDML

操作が許可されません

ALTER TABLE SALES

MOVE PARTITION

P200901 TABLESPACE Q4

UPDATE INDEXES (IDX1 (PARTITION P200901 TABLESPACE Q4))

;

ALTER TABLE SALES

MOVE PARTITION

P200901 TABLESPACE Q4;

(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 - サブパーティション・レベルの統計情報を収集

します。

(77)

パーティション表とデータ圧縮

• パーティション表とデータ圧縮を組み合わせる事によって

1つの表の中で効率的なデータ管理を実現できます

2005年度売上

2006年度売上

2007年度売上

2008年度売上

P2005

P2006

P2007

P2008

※パーティション表全体、またはパーティション単位に圧縮／非圧縮を指定可能