Oracle 11g リファレンス・パーティションに関する検証 その2

<Oracle 11g リファレンス・パーティションに関する検証 その2>
ペンネーム: パンダおやじ

 前回の検証では、以下のことがわかりました。

・リファレンス・パーティションを使用しても性能が向上しない場合が
 あります。
・パーティションを全て参照しなくてよいクエリーの場合、性能向上が
 大きく期待できます。
・子表ごとに長いパーティション化句を明示的に宣言する必要が無いので
 管理が容易です。

今回は、、一般的なパーティションを作成した場合や、親子関係が複雑な
場合にどのようにリファレンス・パーティションを作成すればよいのか、
その時、性能差はあるのかについて検証します。

▼リファレンス・パーティションの制約

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さんにリファレンス・パーティションとインターバル・パーティションが
コンポジット・パーティション化できる機能の追加を期待して、リファレンス・
パーティションについて検証を終わります。

肩が痛い原因は、運動不足ですと医者に言われてリハビリを始めた爺でした。