Oracle 11g リファレンス・パーティションに関する検証 その１
＜Oracle 11g リファレンス・パーティションに関する検証 その１＞
ペンネーム： パンダおやじ
今回から11gの新機能であるリファレンス（参照）パーティションについて
検証します。
リファレンス・パーティション（マニュアルでは参照パーティション）は、
親子の参照制約（外部キー制約）を使用して子表に親のパーティションの
スキームを継承することができる機能です。
リファレンス・パーティションはどのように作成すればよいのか？
リファレンス・パーティションを使うと性能はどうなるのか？
必ず速くなるのか、遅くなる場合は無いのかを検証していきます。
▼ 想定する検索要件
全国の顧客分布を確認したい。
特定の都道府県の顧客数や売上データを確認したい。
製品種別毎の売上を確認したい。
製品・地域毎の売上を確認したい。
では、リファレンス・パーティションを使って下記表を作ってみましょう。
ER図はこんな感じです。
＋－－－－－－－－－－－＋ ＋－－－－－－－－－－－＋ ｜JIS住所表（ADDRESS） ｜ ＋－＜ ｜顧客表（CUSTOMER） ｜ ＋－－－－－－－－－－－＋ ＋－－－－－－－－－－－＋ ＋ ｜ ＾ ＋－－－－－－－－－－－＋ ｜売上表（SALES） ｜ ＋－－－－－－－－－－－＋ ＋ ｜ ＾ ＋－－－－－－－－－－－＋ ＋－－－－－－－－－－－－－＋ ｜製品表（PRODUCT） ｜ ＋－＜ ｜売上明細表（SALES_DETAIL）｜ ＋－－－－－－－－－－－＋ ＋－－－－－－－－－－－－－＋
顧客表はJIS住所表の子表で、県コードは持っていません。
売上表は顧客表の子表で、住所に関する情報は持っていません。
売上明細表は、製品表と売上表の子表で、製品種別は持っていません。
＜検証環境＞
OS：Red Hat Enterprise Linux Server release 5.4（64Bit）
Oracle：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit
▼リファレンス・パーティションでテーブルを作成
JIS住所表は顧客表の親表です。
この表はリスト・パーティションで作成します。
create table ADDRESS ( ADR_CD number primary key, KEN_CD number, CITY_CD number, TOWN_CD number, POST_ID varchar2(8), KEN_NAME varchar2(8), CITY_NAME varchar2(256), TOWN_NAME varchar2(256), STR_NAME varchar2(256) ) partition by list (KEN_CD) ( partition HOKKAIDO values (1), ～～ partition OKINAWA values (47) );
顧客表を住所表の子表として作成します。
create table CUSTOMER
(
CUST_ID number primary key,
CUST_NAME varchar2(200),
ADR_CD number not null,
constraint FK_CUSTOMER_01
foreign key (ADR_CD)
references ADDRESS
)
partition by reference (FK_CUSTOMER_01);
売上表を顧客表の子表として作成します。
create table SALES
(
SALES_ID number primary key,
CUST_ID number not null,
SALES_DATE date,
constraint FK_SALES_01
foreign key (CUST_ID)
references CUSTOMER
)
partition by reference (FK_SALES_01);
製品は売上明細表の親表です。
この表はリスト・パーティションで作成します。
create table PRODUCT ( PROD_CD number primary key, SYUBETU_CD number, PROD_NAME varchar2(256), SYUBETU_NAME varchar2(256), PROD_AMT number ) partition by list (SYUBETU_CD) ( partition P100 values (100), partition P101 values (101), partition P102 values (102), partition P103 values (103), partition P104 values (104), partition P105 values (105), partition P106 values (106), partition P107 values (107), partition P108 values (108), partition P109 values (109) );
売上明細表を製品表の子表として作成します。
create table SALES_DETAIL
(
SALES_ID number not null,
DETAIL_ID number not null,
PROD_CD number not null,
SALES_CNT number,
primary key( SALES_ID,DETAIL_ID,PROD_CD ),
constraint FK_SALES_DETAIL_01
foreign key (PROD_CD)
references PRODUCT
)
partition by reference (FK_SALES_DETAIL_01);
※リファレンス・パーティションを作成するためには制約句に使用する カラムにNOT NULL制約をつけることが必要です。
▼リファレンス・パーティションの確認
さて、リファレンス・パーティションがどのように作成されたか確認しま
しょう。
select table_name, partition_name, high_value from user_tab_partitions order by table_name,partition_name TABLE_NAME PARTITION_NAME HIGH_VALUE -------------------- -------------------- ---------- ADDRESS HOKKAIDO 1 ～～～ ADDRESS OKINAWA 47 CUSTOMER HOKKAIDO ～～ CUSTOMER OKINAWA 子表にも作成されて います。 PRODUCT P100 100 ～～ PRODUCT P109 109 SALES HOKKAIDO ～～ SALES OKINAWA JIS住所表の孫表にも パーティションが作成 されています。 SALES_DETAIL P100 ～～ SALES_DETAIL P109 製品の子表にも作成 されています。
上記結果より各表のパーティション境界が確認できます。
HIGH_VALUEがリファレンス・パーティションではNULLとなっています。
これは、パーティション境界が親表から導かれていることを示しています。
さらに、パーティション名が、親表のものと同じになっています。
では、パーティションのタイプを確認してみましょう。
select table_name, partitioning_type, ref_ptn_constraint_name from user_part_tables TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME -------------------- --------- ------------------------------ ADDRESS LIST CUSTOMER REFERENCE FK_CUSTOMER_01 PRODUCT LIST SALES REFERENCE FK_SALES_01 SALES_DETAIL REFERENCE FK_SALES_DETAIL_01
REF_PTN_CONSTRAINT_NAME列に外部キーの制約名が示されています。
▼性能比較用にパーティションなしのテーブル作成
性能を比較するためにリファレンス・パーティションなしの各表を作成しま
す。
create table ADDRESS_NOPER
(
ADR_CD number primary key,
KEN_CD number,
CITY_CD number,
TOWN_CD number,
POST_ID varchar2(8),
KEN_NAME varchar2(8),
CITY_NAME varchar2(256),
TOWN_NAME varchar2(256),
STR_NAME varchar2(256)
);
create table CUSTOMER_NOREF
(
CUST_ID number primary key,
CUST_NAME varchar2(200),
ADR_CD number not null,
constraint FK_CUSTOMER_NOREF_01
foreign key (ADR_CD)
references ADDRESS
);
create table SALES_NOREF
(
SALES_ID number primary key,
CUST_ID number not null,
SALES_DATE date,
constraint FK_SALES_NOREF_01
foreign key (CUST_ID)
references CUSTOMER
);
create table PRODUCT_NOPER
(
PROD_CD number primary key,
SYUBETU_CD number,
PROD_NAME varchar2(256),
SYUBETU_NAME varchar2(256),
PROD_AMT number
);
create table SALES_DETAIL_NOREF
(
SALES_ID number not null,
DETAIL_ID number not null,
PROD_CD number not null,
SALES_CNT number,
primary key( SALES_ID,DETAIL_ID,PROD_CD ),
constraint FK_SALES_DETAIL_NOREF_01
foreign key (PROD_CD)
references PRODUCT
);
やっと性能を検証するための準備ができました。
▼全国の県別顧客数を調べてみましょう。
— パーティションを使用
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set events='10046 trace name context forever, level 12';
select A.KEN_CD,A.KEN_NAME,count(B.CUST_ID)
from ADDRESS A , CUSTOMER B
where A.ADR_CD = B.ADR_CD
group by A.KEN_CD,A.KEN_NAME;
KEN_CD KEN_NAME COUNT(B.CUST_ID)
---------- -------- ----------------
1 北海道 17994
2 青森県 5070
～～～
46 鹿児島県 2910
47 沖縄県 1584
トレースの結果
call count cpu elapsed disk query current rows
------- ------ ------ -------- ------- ------- -------- ------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.27 0.28 2615 2805 0 47
------- ------ ------ -------- ------- ------- -------- ------
total 7 0.29 0.29 2615 2805 0 47
実行計画
Rows Row Source Operation
------- ---------------------------------------------------
47 PARTITION LIST ALL PARTITION: 1 47 (cr=2805 pr=2615 pw=0 time=0 us cost=565 size=35926 card=1562)
47 HASH GROUP BY (cr=2805 pr=2615 pw=0 time=0 us cost=565 size=35926 card=1562)
250738 HASH JOIN (cr=2805 pr=2615 pw=0 time=225671 us cost=561 size=2883487 card=125369)
250738 TABLE ACCESS FULL CUSTOMER PARTITION: 1 47 (cr=1435 pr=1340 pw=0 time=45159 us cost=201 size=752214 card=125369)
125369 TABLE ACCESS FULL ADDRESS PARTITION: 1 47 (cr=1370 pr=1275 pw=0 time=53307 us cost=335 size=2131273 card=125369)
— パーティションを使用しない
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set events='10046 trace name context forever, level 12';
select A.KEN_CD,A.KEN_NAME,count(B.CUST_ID)
from ADDRESS_NOPER A , CUSTOMER_NOREF B
where A.ADR_CD = B.ADR_CD
group by A.KEN_CD,A.KEN_NAME;
トレースの結果
call count cpu elapsed disk query current rows
------- ------ ------ -------- ------- ------- -------- ------
Parse 1 0.02 0.02 22 120 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.17 0.24 2569 3309 0 47
------- ------ ------ -------- ------- ------- -------- ------
total 7 0.20 0.26 2591 3429 0 47
実行計画
Rows Row Source Operation
------- ---------------------------------------------------
47 HASH GROUP BY (cr=3309 pr=2569 pw=372 time=0 us cost=764 size=35926 card=1562)
250738 HASH JOIN (cr=3309 pr=2569 pw=372 time=2375537 us cost=760 size=2883487 card=125369)
250738 TABLE ACCESS FULL CUSTOMER_NOREF (cr=2238 pr=1128 pw=0 time=26878 us cost=171 size=752214 card=125369)
125369 TABLE ACCESS FULL ADDRESS_NOPER (cr=1071 pr=1069 pw=0 time=32877 us cost=308 size=2131273 card=125369)
リファレンス・パーティションを使用した場合COSTが低くなっていますが、実行時
間が長くなっています。
これは、I/O量の違いで発生していることが原因のようです。
— 北海道の顧客数を調べる
— パーティションを使用
connect system/insight
alter system flush shared_pool;
alter system flush buffer_cache;
connect scott/tiger
alter session set events='10046 trace name context forever, level 12';
select A.KEN_CD,A.KEN_NAME,count(B.CUST_ID)
from ADDRESS A , CUSTOMER B
where A.ADR_CD = B.ADR_CD
and A.KEN_NAME = '北海道'
group by A.KEN_CD,A.KEN_NAME
トレースの結果
call count cpu elapsed disk query current rows
------- ------ ------ -------- ------- ------- -------- ------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.05 0.05 1356 1454 0 1
------- ------ ------ -------- ------- ------- -------- ------
total 4 0.06 0.06 1356 1454 0 1
実行計画
Rows Row Source Operation
------- ---------------------------------------------------
1 PARTITION LIST ALL PARTITION: 1 47 (cr=1454 pr=1356 pw=0 time=0 us cost=561 size=782 card=34)
1 HASH GROUP BY (cr=1454 pr=1356 pw=0 time=0 us cost=561 size=782 card=34)
17994 HASH JOIN (cr=1454 pr=1356 pw=0 time=17610 us cost=560 size=62445 card=2715)
8997 TABLE ACCESS FULL ADDRESS PARTITION: 1 47 (cr=1370 pr=1275 pw=0 time=19639 us cost=335 size=45339 card=2667)
17994 TABLE ACCESS FULL CUSTOMER PARTITION: 1 47 (cr=84 pr=81 pw=0 time=2552 us cost=201 size=752214 card=125369)
— パーティションを使用しない
connect system/insight
alter system flush shared_pool;
alter system flush buffer_cache;
connect scott/tiger
alter session set events='10046 trace name context forever, level 12';
select A.KEN_CD,A.KEN_NAME,count(B.CUST_ID)
from ADDRESS_NOPER A , CUSTOMER_NOREF B
where A.ADR_CD = B.ADR_CD
and A.KEN_NAME = '北海道'
group by A.KEN_CD,A.KEN_NAME;
トレースの結果
call count cpu elapsed disk query current rows
------- ------ ------ -------- ------- ------- -------- ------
Parse 1 0.03 0.03 22 120 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.11 0.11 2197 3309 0 1
------- ------ ------ -------- ------- ------- -------- ------
total 4 0.14 0.14 2219 3429 0 1
実行計画
Rows Row Source Operation
------- ---------------------------------------------------
1 HASH GROUP BY (cr=3309 pr=2197 pw=0 time=0 us cost=481 size=782 card=34)
17994 HASH JOIN (cr=3309 pr=2197 pw=0 time=17099 us cost=480 size=62445 card=2715)
8997 TABLE ACCESS FULL ADDRESS_NOPER (cr=1071 pr=1069 pw=0 time=16851 us cost=308 size=45339 card=2667)
250738 TABLE ACCESS FULL CUSTOMER_NOREF (cr=2238 pr=1128 pw=0 time=46845 us cost=171 size=752214 card=125369)
おや、今度は逆転しています。
リファレンス・パーティションを使用した場合コストが高いのに実行時間が
短くなっています。
なぜ、実行時間が短くなったのでしょう。
トレースの結果を比較してみると
リファレンス・パーティションを使用した場合ディスクアクセスブロック数が
1356に比べ、使用しない場合は2219と大きく異なります。
この違いが実行時間の違いに表れていますね。
実行時間が半分程度ですので大きな効果がありました。
さらに効果を期待できるのは、親表とジョインしないと速くなるでしょう。
— CUSTOMERだけを参照し北海道の顧客数を求める
connect system/insight
alter system flush shared_pool;
alter system flush buffer_cache;
connect scott/tiger
alter session set events='10046 trace name context forever, level 12';
select count(*) from CUSTOMER partition(HOKKAIDO);
トレースの結果
call count cpu elapsed disk query current rows
------- ------ ------ -------- ------- ------- -------- ------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 81 84 0 1
------- ------ ------ -------- ------- ------- -------- ------
total 4 0.01 0.01 81 84 0 1
実行計画
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=84 pr=81 pw=0 time=0 us)
17994 PARTITION REFERENCE SINGLE PARTITION: 1 1 (cr=84 pr=81 pw=0 time=9315 us cost=13 size=0 card=8997)
17994 TABLE ACCESS FULL CUSTOMER PARTITION: 1 1 (cr=84 pr=81 pw=0 time=3445 us cost=13 size=0 card=8997)
やはり速くなっています。なんと、実行時間が10分の1に短縮されています。
顧客表を親表のJIS住所表とJOINしなくて検索できてしまうため、ディスク
アクセスブロック数が81となり当然の結果ですね。
前提となるのは、
・パーティション名が「HOKKAIDO」であることを知っていることが必要です。
今回は、リファレンス・パーティションの基本的な作成方法と性能について
検証を行いました。
▼ここまでにわかったこと
・リファレンス・パーティションを使用しても性能が向上しない場合が あります。 ・パーティションを全て参照しなくてよいクエリーの場合、性能向上が 大きく期待できます。 ・子表ごとに長いパーティション化句を明示的に宣言する必要が無いので 管理が容易です。
次回は、一般的なパーティションを作成した場合や、親子関係が複雑な
場合にどのようにリファレンス・パーティションを作成すればよいのか、
その時、性能差はあるのかについて検証します。
最近、肩が痛く年寄りになったのかなと実感している爺でした。