※資料中に掲載されている検証内容は、特定の環境における検証結果についての報告であり、
すべての環境において同様の動作を保証するものではありませんので予めご了承下さい。
パーティション検証
1.
パーティションを利用すると本当に早くなるのか。2.
パーティションは細かく切れば切るほどよいのか。3.
複数のパーティションをまたぐ検索ではパフォーマンスが落ちるのでは。4.
検索以外の処理でも効果はあるのか。参考 : 検証環境
HP BladeSystem c-Class
ストレージ
HP StorageWorks MSA1000
• ハードウェア構成
•
サーバーマシン• ProLiant DL380 G3
•
インテル® Xeon™
プロセッサ3.20GHz x2
•
ストレージ装置• HP StorageWorks MSA1000
• ソフトウェア構成
• Red Hat Enterprise Linux 5
• Oracle Database 11g Enterprise Ediditon R2 (11.2.0.1)
テーブル情報
• テーブル属性
• 10
カラム,
約100Byte / record
• テーブル・タイプ
•
パーティション・テーブル（レンジ・パーティション）•
通常表• パーティション・サイズ
• 1
ヶ月毎/72 Partition
• 4
半期毎/24 Partition
• 1
年毎/6 Partition
• テーブル・サイズ
• 3GB
（5
年分） ・・・売上表
検索処理時間 (1 回あたりの検索処理時間 )
テーブル・サイズ : ３ GB
※ パーティション・テーブルの1件検索処理時間を 10msecとした場合の相対処理時間
( 実際の処理時間に任意の数を掛けています)
0 10 20 30 40 50 60 70
1
日1ケ月分 3
ケ月分1
年分 全データ一ヶ月ごと
4
半期ごと1
年毎 非パーティション 処理時間 取得データ量（5000万件）
（1000万件）
（300万件）
（100万件）
（2万件）
検索以外の処理時間
テーブル・サイズ : ３ GB
0 200 400 600 800 1000
更新＆削除 挿入 統計情報取得 索引再作成
パーティション 非パーティション 処理時間 操作内容
※ パーティション・テーブルの1件検索処理時間を 10msecとした場合の相対処理時間
( 実際の処理時間に任意の数を掛けています)
検証結果
1.
パーティションを利用すると本当に早くなるのか。0 10 20 30 40 50 60 70
1年分
全データ一ヶ月ごと
4
半期ごと1
年毎 非パーティション検索時間は半分以下に 全てのデータを取ってくる
場合には性能差はない
•
全てのデータを取ってくる場合は、非パーティション表と比べても検索時 間に差はでない•
一方、範囲を絞った検索（例：1
年分のデータ）を行った場合、非パーティション表と比べ、大幅に検索時間を削減
検証結果
2.
パーティションは細かく切れば切るほどよいのか。•
パーティションの単位が細かければ細かいほど、より範囲を絞った検索 を行った際に、検索時間は早くなる•
一方、パーティションの単位が細かければ細かいほど、管理が必要なパーティション表の数は増える
0 5 10 15 20
1
ケ月分3
ケ月分1
年分一ヶ月ごと
4
半期ごと1
年毎検証結果
3.
複数のパーティションをまたぐ検索ではパフォーマンスが落ちるのでは。一ケ月ごとのパーティション表は １２個のパーティション表に アクセス
•
パーティション表をまたがった検索を行った場合でも、さほど検索時間には影響を与えない
0 5 10 15 20
1
ケ月分3
ケ月分1
年分一ヶ月ごと
4
半期ごと1
年毎検証結果
4.
検索以外の処理でも効果はあるのか。•
索引の再作成、統計情報の取得といったメンテナンス作業も パーティション単位で行えるので高速• DML
処理に関しても、非パーティション化と比べ、パフォーマンスが悪く なることはない0 200 400 600 800 1000
更新＆削除 挿入 統計情報取得 索引再作成
パーティション 非パーティション
まとめ
• パーティションの概要
大きな表や索引をデータベース内部で複数の領域に 分割して管理する機能
• パーティションの管理
通常の表と同様にメンテナンス作業が可能
• パーティション検証
必要なデータのみにアクセスすることで
大幅に処理時間を短縮
• パーティションの概要
• パーティションの管理
• パーティション検証
• まとめ
• Appendix
Agenda
※資料中に掲載されている検証内容は、特定の環境における検証結果についての報告であり、
すべての環境において同様の動作を保証するものではありませんので予めご了承下さい。
パーティション情報の確認方法
• パーティションの種類の確認
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‟
・・・パーティション・プルーニング
• パーティション・プルーニングが機能するには 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 | 2 |
|* 3 | TABLE ACCESS FULL |
”売上表”| 43146 | 337K| 172 (20)| 00:00:03 | 2 | 2 |
---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|
---
---パーティション・プルーニング
• パーティション・キーに対して関数や算術を使用した場合、
パーティション・プルーニングは機能しない
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 句にバインド変数を利用した場合、
解析時には判断できない
•
実行時にオプティマイザによりパーティション・プルーニングが 機能するかどうかを決定パーティション表の移動を伴う更新
• パーティション表の移動が伴う更新（ 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 を有効にする
ALTER TABLE
“売上表”ENABLE ROW MOVEMENT ;
パーティションの種類
リスト・パーティション
• 特定のデータ（製品名、店舗名 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 („
“宮城”‟,‟
“青森”‟)
売上表