Oracle 11g リファレンス・パーティションに関する検証 その２
＜Oracle 11g リファレンス・パーティションに関する検証 その２＞
ペンネーム： パンダおやじ
前回の検証では、以下のことがわかりました。
・リファレンス・パーティションを使用しても性能が向上しない場合が あります。 ・パーティションを全て参照しなくてよいクエリーの場合、性能向上が 大きく期待できます。 ・子表ごとに長いパーティション化句を明示的に宣言する必要が無いので 管理が容易です。
今回は、、一般的なパーティションを作成した場合や、親子関係が複雑な
場合にどのようにリファレンス・パーティションを作成すればよいのか、
その時、性能差はあるのかについて検証します。
▼リファレンス・パーティションの制約
CREATE TABLE構文を確認すると以下のことがわかりました。 ・リファレンス・パーティションをコンポジット・パーティション化できない つまり、単一のパーティションしか定義できません。 ・親表のインターバル・パーティションをリファレンスすることができない
前回の例であげた、売上明細表は、2つの親を持っているため（売上表と
製品表）のどちらか片方のリファレンス・パーティションしか作成でき
ません。
つまり、売上明細をどのように検索する場合が多いかによって、作成すべき
リファレンス・パーティションを決定する必要があります。
たとえば、製品種別ごとに検索するケースが多い場合は、製品表を親表にし、
県別に検索するケースが多い場合は、売上表を親表にします。
▼前回提示したER図
＋－－－－－－－－－－－＋ ＋－－－－－－－－－－－＋ ｜JIS住所表（ADDRESS） ｜ ＋－＜ ｜顧客表（CUSTOMER） ｜ ＋－－－－－－－－－－－＋ ＋－－－－－－－－－－－＋ ＋ ｜ ＾ ＋－－－－－－－－－－－＋ ｜売上表（SALES） ｜ ＋－－－－－－－－－－－＋ ＋ ｜ ＾ ＋－－－－－－－－－－－＋ ＋－－－－－－－－－－－－－＋ ｜製品表（PRODUCT） ｜ ＋－＜ ｜売上明細表（SALES_DETAIL）｜ ＋－－－－－－－－－－－＋ ＋－－－－－－－－－－－－－＋
顧客表はJIS住所表の子表で、県コードは持っていません。
売上表は顧客表の子表で、住所に関する情報は持っていません。
売上明細表は、製品表と売上表の子表で、製品種別は持っていません。
▼リファレンス・パーティション以外でパーティションを作成
・顧客表に県コードを追加します。
・売上表にも県コードを追加します。
・売上明細表には、県コードと、製品種別を追加します。
JIS住所表はリファレンス用に作成したものと同じです。
この表はリスト・パーティションで作成します。
create table ADDRESS_LIST ( 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_LIST
(
CUST_ID number primary key,
CUST_NAME varchar2(200),
ADR_CD number not null,
KEN_CD number,
constraint FK_CUSTOMER_LIST_01
foreign key (ADR_CD)
references ADDRESS_LIST
)
partition by list (KEN_CD)
(
partition HOKKAIDO values (1),
～～
partition OKINAWA values (47)
)
売上表を作成します。
create table SALES_LIST
(
SALES_ID number primary key,
CUST_ID number not null,
KEN_CD number,
SALES_DATE date,
constraint FK_SALES_LIST_01
foreign key (CUST_ID)
references CUSTOMER_LIST
)
partition by list (KEN_CD)
(
partition HOKKAIDO values (1),
～～
partition OKINAWA values (47)
);
製品表はリファレンス用に作成したものと同じです。
この表はリスト・パーティションで作成します。
create table PRODUCT_LIST ( 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_LIST
(
SALES_ID number not null,
DETAIL_ID number not null,
PROD_CD number not null,
SYUBETU_CD number,
SALES_CNT number,
primary key( SALES_ID,DETAIL_ID,PROD_CD ),
constraint FK_SALES_DETAIL_LIST_01
foreign key (PROD_CD)
references PRODUCT_LIST
)
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)
);
前回の検証結果、パーティション化によって検索性能が向上するのは、単一の
パーティションを使用する検索でしたので、全国の顧客数を調べる性能を検証
しません。
▼北海道の顧客数を調べる
— リファレンスパーティションを使用
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)
— リスト・パーティションを使用する
select A.KEN_CD,A.KEN_NAME,count(B.CUST_ID)
from ADDRESS_LIST A , CUSTOMER_LIST 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.02 0.01 95 285 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.22 0.22 2631 3388 0 1
------- ------ ------ -------- ------- ------- -------- ------
total 4 0.24 0.24 2726 3673 0 1
実行計画
Rows Row Source Operation
------- ---------------------------------------------------
1 HASH GROUP BY (cr=3388 pr=2631 pw=0 time=0 us cost=719 size=396585 card=8813)
17994 HASH JOIN (cr=3388 pr=2631 pw=0 time=29988 us cost=718 size=396585 card=8813)
8997 PARTITION LIST ALL PARTITION: 1 47 (cr=1610 pr=1209 pw=0 time=8996 us cost=336 size=282016 card=8813)
8997 TABLE ACCESS FULL ADDRESS_LIST PARTITION: 1 47 (cr=1610 pr=1209 pw=0 time=6461 us cost=336 size=282016 card=8813)
250738 PARTITION LIST ALL PARTITION: 1 47 (cr=1778 pr=1422 pw=0 time=159606 us cost=380 size=2434744 card=187288)
250738 TABLE ACCESS FULL CUSTOMER_LIST PARTITION: 1 47 (cr=1778 pr=1422 pw=0 time=78184 us cost=380 size=2434744 card=187288)
トレースの結果を比較してみると、リファレンス・パーティションを使用
した場合のほうが早いことがわかります。
詳しく見てみるとJIS住所表と、顧客表ともにリストパーティションを
持っていても顧客表のパーティションをすべて検索してしまっていますね。
その結果、ディスクアクセスブロック数が多くなり、実行時間に差がでて
います。
実行時間が4分の1程度ですのでリファレンス・パーティションの勝利です。
▼顧客表だけを検索して性能差を比較
前回大きな効果があった、顧客表だけを参照した場合の差はどうでしょう
— リファレンス・パーティションを使用
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)
— リスト・パーティションを使用
select count(*) from CUSTOMER_LIST partition(HOKKAIDO);
トレースの結果
call count cpu elapsed disk query current rows
------- ------ ------ -------- ------- ------- -------- ------
Parse 1 0.00 0.00 2 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 18 92 0 1
------- ------ ------ -------- ------- ------- -------- ------
total 4 0.01 0.01 20 94 0 1
実行計画
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=92 pr=18 pw=0 time=0 us)
17994 PARTITION LIST SINGLE PARTITION: 1 1 (cr=92 pr=18 pw=0 time=10208 us cost=25 size=0 card=19509)
17994 TABLE ACCESS FULL CUSTOMER_LIST PARTITION: 1 1 (cr=92 pr=18 pw=0 time=3828 us cost=25 size=0 card=19509)
結果は、ほぼ同じでした。
しかし、リスト・パーティションを独自に作成したCUSTOMER_LISTを検索した
場合は、ディスクアクセスブロック数が少なくなっています。
もっと大きな表の場合は、個別にリストパーティションを作成したほうに
軍配が上がるかもしれません。
▼売上明細表より製品種別コードが109の県別の個数を調べる
— リファレンス・パーティションを使用する
select C.KEN_NAME,count(*)
from SALES_DETAIL A,PRODUCT B,ADDRESS C,SALES D,CUSTOMER E
where B.PROD_CD = A.PROD_CD
and B.SYUBETU_CD = 109
and A.SALES_ID = D.SALES_ID
and D.CUST_ID = E.CUST_ID
and E.ADR_CD = C.ADR_CD
group by C.KEN_NAME;
トレースの結果
call count cpu elapsed disk query current rows
------- ------ ------ -------- ------- ------- -------- ------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.25 0.25 3997 4300 0 47
------- ------ ------ -------- ------- ------- -------- ------
total 7 0.28 0.29 3997 4300 0 47
実行計画
Rows Row Source Operation
------- ---------------------------------------------------
47 HASH GROUP BY (cr=4300 pr=3997 pw=0 time=0 us cost=1281 size=2538 card=47)
23243 HASH JOIN (cr=4300 pr=3997 pw=0 time=56317 us cost=1280 size=1285416 card=23804)
23243 HASH JOIN (cr=2931 pr=2722 pw=0 time=69087 us cost=729 size=952160 card=23804)
23243 HASH JOIN (cr=1497 pr=1382 pw=0 time=43546 us cost=374 size=690316 card=23804)
23243 PARTITION LIST SINGLE PARTITION: 10 10 (cr=767 pr=737 pw=0 time=31542 us cost=208 size=452276 card=23804)
23243 NESTED LOOPS (cr=767 pr=737 pw=0 time=27967 us cost=208 size=452276 card=23804)
10 TABLE ACCESS FULL PRODUCT PARTITION: 10 10 (cr=7 pr=6 pw=0 time=45 us cost=3 size=90 card=10)
23243 TABLE ACCESS FULL SALES_DETAIL PARTITION: 10 10 (cr=760 pr=731 pw=0 time=23719 us cost=21 size=25060 card=2506)
125369 PARTITION REFERENCE ALL PARTITION: 1 47 (cr=730 pr=645 pw=0 time=76244 us cost=165 size=1253690 card=125369)
125369 TABLE ACCESS FULL SALES PARTITION: 1 47 (cr=730 pr=645 pw=0 time=43159 us cost=165 size=1253690 card=125369)
250738 PARTITION REFERENCE ALL PARTITION: 1 47 (cr=1434 pr=1340 pw=0 time=103673 us cost=354 size=2758118 card=250738)
250738 TABLE ACCESS FULL CUSTOMER PARTITION: 1 47 (cr=1434 pr=1340 pw=0 time=50288 us cost=354 size=2758118 card=250738)
125369 PARTITION LIST ALL PARTITION: 1 47 (cr=1369 pr=1275 pw=0 time=45158 us cost=335 size=1755166 card=125369)
125369 TABLE ACCESS FULL ADDRESS PARTITION: 1 47 (cr=1369 pr=1275 pw=0 time=27142 us cost=335 size=1755166 card=125369)
— リスト・パーティションを使用する
select C.KEN_NAME,count(*)
from SALES_DETAIL_LIST A,PRODUCT_LIST B,ADDRESS_LIST C,SALES_LIST D,CUSTOMER_LIST E
where B.PROD_CD = A.PROD_CD
and B.SYUBETU_CD = 109
and A.SALES_ID = D.SALES_ID
and D.CUST_ID = E.CUST_ID
and E.ADR_CD = C.ADR_CD
group by C.KEN_NAME;
トレースの結果
call count cpu elapsed disk query current rows
------- ------ ------ -------- ------- ------- -------- ------
Parse 1 0.05 0.05 157 1872 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.32 0.85 4639 6797 0 47
------- ------ ------ -------- ------- ------- -------- ------
total 7 0.38 0.91 4796 8669 0 47
実行計画
Rows Row Source Operation
------- ---------------------------------------------------
47 HASH GROUP BY (cr=6797 pr=4639 pw=0 time=0 us cost=2401 size=3080658 card=25046)
23243 HASH JOIN (cr=6797 pr=4639 pw=0 time=59509 us cost=2399 size=3080658 card=25046)
23243 HASH JOIN (cr=5187 pr=3363 pw=0 time=59892 us cost=1642 size=2604784 card=25046)
23243 HASH JOIN (cr=3409 pr=1959 pw=0 time=45462 us cost=815 size=1953588 card=25046)
23243 HASH JOIN (cr=1716 pr=872 pw=0 time=8428 us cost=244 size=1302392 card=25046)
10 PARTITION LIST SINGLE PARTITION: 10 10 (cr=7 pr=0 pw=0 time=0 us cost=3 size=260 card=10)
10 TABLE ACCESS FULL PRODUCT_LIST PARTITION: 10 10 (cr=7 pr=0 pw=0 time=0 us cost=3 size=260 card=10)
250568 PARTITION LIST ALL PARTITION: 1 10 (cr=1709 pr=872 pw=0 time=88939 us cost=240 size=6511908 card=250458)
250568 TABLE ACCESS FULL SALES_DETAIL_LIST PARTITION: 1 10 (cr=1709 pr=872 pw=0 time=54264 us cost=240 size=6511908 card=250458)
125369 PARTITION LIST ALL PARTITION: 1 47 (cr=1693 pr=1087 pw=0 time=53729 us cost=299 size=2816086 card=108311)
125369 TABLE ACCESS FULL SALES_LIST PARTITION: 1 47 (cr=1693 pr=1087 pw=0 time=16872 us cost=299 size=2816086 card=108311)
250738 PARTITION LIST ALL PARTITION: 1 47 (cr=1778 pr=1404 pw=0 time=86010 us cost=380 size=4869488 card=187288)
250738 TABLE ACCESS FULL CUSTOMER_LIST PARTITION: 1 47 (cr=1778 pr=1404 pw=0 time=42532 us cost=380 size=4869488 card=187288)
125369 PARTITION LIST ALL PARTITION: 1 47 (cr=1610 pr=1276 pw=0 time=47716 us cost=336 size=3653510 card=192290)
125369 TABLE ACCESS FULL ADDRESS_LIST PARTITION: 1 47 (cr=1610 pr=1276 pw=0 time=30688 us cost=336 size=3653510 card=192290)
リファレンス・パーティションを使用した場合ディスクアクセスブロック数が
少なくなって、結果として早いことがわかりました。
なぜ、ディスクアクセスブロック数に差があるのでしょう。
これは、リファレンス・パーティションでパーティション用のカラムの定義が
必要ないからですね。
▼今回わかったこと
・リファレンス・パーティションは親子関係が正しく定義できれば、性能もよい
・単一のパーティションを検索する場合、リスト・パーティションが有利
・リファレンス・パーティションは複雑な親子関係には向かない
Oracleさんにリファレンス・パーティションとインターバル・パーティションが
コンポジット・パーティション化できる機能の追加を期待して、リファレンス・
パーティションについて検証を終わります。
肩が痛い原因は、運動不足ですと医者に言われてリハビリを始めた爺でした。