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

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

 今回から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」であることを知っていることが必要です。

今回は、リファレンス・パーティションの基本的な作成方法と性能について
検証を行いました。

▼ここまでにわかったこと

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

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

最近、肩が痛く年寄りになったのかなと実感している爺でした。