はじめに

Oracle Database では、様々なパフォーマンスチューニングの方法があります。そのなかで強力なものがパーティショニングです。1個のテーブルを複数の領域に分割して管理する機能で、たくさんの行があるテーブルから効率的にデータを取得できます。例えば、過去5年間の売り上げデータを保管しているときに、直近1年分のデータを集計するとします。取得したいデータより古いデータは集計に不要です。パーティションを使うことで不要なデータの読み込みを省略でき、高速なデータアクセスが出来ます。

文章ではわかりにくい、かつ詳細は省略しているため、興味があるかたはこちらの資料をどうぞ。わかりやすいと思います。
今回の記事では、パーティションテーブルを作成して、実行計画を観察していきます。

検証で使った環境

OCI DBCS High Performance 19c

パーティション表の作り方

パーティショニングは、表を作るときに有効化します。分割方法は基本的に3つあります。

レンジ・パーティション : 値の範囲で分割。例えば日付
リスト・パーティション : 特定の値をカテゴライズして分割。例えば地域名
ハッシュ・パーティション : ハッシュ化した数値で分割

レンジ・パーティションは、特定の年・月・日でパーティション化が出来ます。扱いやすい分割方式だと思います。

今回は、DATE 型の列をつかって、毎月のレンジ・パーティションをしていきます。まず、はじめに、基本的なパーティション表の作り方を見てみましょう。CREATE TABLE でパーティション化をしています。

CREATE TABLE sales
(
    product_id NUMBER(4) NOT NULL,
    sales_date DATE,
    Customer_id VARCHAR2(40)
)
PARTITION BY RANGE(sales_date)
(
    PARTITION sales_p01 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD')),
    PARTITION sales_p02 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD')),
    PARTITION sales_p03 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD')),
    PARTITION sales_p04 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD')),
    PARTITION sales_p05 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD')),
    PARTITION sales_p06 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD')),
    PARTITION sales_p07 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD')),
    PARTITION sales_p08 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD')),
    PARTITION sales_p09 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD')),
    PARTITION sales_p10 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD')),
    PARTITION sales_p11 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD')),
    PARTITION sales_p12 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
);
COMMIT;

PARTITION BY RANGE(sales_date) で、DATE型の sales_date 列をつかってレンジ・パーティションを宣言しています。
その次の PARTITION sales_p01 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD')), は、sales_date 列の日付が、2020/02/01 未満だったときに、sales_p01 という名前のパーティションにデータを入れる宣言をしています。それ以降、月ごとのパーティション宣言が続いています。

これを見た時の自分の第一印象は、月ごとに指定するのめんどくさいな、自動的に毎月の設定してくれないかな、です。
このめんどくささを解消するために、インターバルパーティションを使って自動的に設定が出来ます。見て行きましょう。

CREATE TABLE sales
(
    product_id NUMBER(4) NOT NULL,
    sales_date DATE,
    Customer_id VARCHAR2(40)
)
PARTITION BY RANGE(sales_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
    PARTITION sales_p01 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
);
COMMIT;

ずいぶんシンプルな設定になりました。INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) を追加しており、月毎にパーティションを自動的に作ってくれます。PARTITION sales_p01 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD')) の指定も、1行の指定で大丈夫です。複数行の指定は省略できます。

memo : Table の削除

DROP TABLE sales;
COMMIT;

それでは、パーティション化されているか確認するために、一旦、統計情報を最新にします。SQL*Plus などで実行してください。

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'sugi',tabname=>'sales',cascade=>false);

USER_TAB_PARTITIONS 表から、パーティションの一覧を確認してみましょう。Table 内で構成されているパーティション一覧が分かります。次のものを確認できます。

テーブル名 (TABLE_NAME)
パーティション名 (PARTITION_NAME) : CREATE TABLE で指定した名前が表示されています
Table の中の各パーティションの順番 (PARTITION_POSITION)
パーティションに含まれる行数 (NUM_ROWS : 統計情報を最新にしないと見えない)
パーティションにデータを格納する条件 (HGH_VALUE)

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, NUM_ROWS, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES';

なお、CREATE TABLE で指定した、自動パーティション作成の INTERVAL 条件なども確認できます。なお、PARTITION_COUNT は本来、テーブルの中にパーティションがどれくらいあるかを表しています。ですが、インターバルパーティションを使うことで、 1048575 固定で表示されます。理解しにくい部分もありますが、こういうものなんだな、と覚えておくとよいでしょう。

SELECT TABLE_NAME, PARTITIONING_TYPE, PARTITION_COUNT, INTERVAL FROM USER_PART_TABLES WHERE TABLE_NAME = 'SALES';

パーティション自動生成

実際ににデータを INSERT してみて、自動パーティション作成がどのようにされるか見てみましょう。まず、作成済みのsales_p01パーティションにデータを追加します。sales_p01 は、2020/02/01 未満のデータがすべて格納されるパーティションです。

INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0001,
    TO_DATE('2020/01/01','YYYY/MM/DD'),
    'A'
);
COMMIT;

統計情報を更新

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'sugi',tabname=>'sales',cascade=>false);

パーティションに格納されている行が、0 から 1 に増えていることを確認します。

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, NUM_ROWS, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES';

では次に、範囲外のデータを格納して自動的にパーティションが作成されるか確認します。

INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0002,
    TO_DATE('2020/02/01','YYYY/MM/DD'),
    'B'
);
COMMIT;

統計情報を更新

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'sugi',tabname=>'sales',cascade=>false);

パーティション一覧を見ると、SYS_P273 が増えています！NUM_ROWS が 1 、かつ、2020-03-01 未満のデータが格納されるルールとなっています。毎月で自動的にパーティションが作成されていますね。

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, NUM_ROWS, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES';

ちなみにの検証です。SALES_P01 は、2020-01月分のデータを入れる意図で作っています。これよりも古いデータを入れてみるとどうなるのでしょうか？

INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0003,
    TO_DATE('2019/12/01','YYYY/MM/DD'),
    'C'
);
COMMIT;

統計情報更新後確認です。SALES_P01 ` パーティションに 2019/12/01 のデータが格納されます。パーティションで作った日付より古いデータは全部格納される動きでした。

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, NUM_ROWS, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES';

では次に、数カ月飛ばした未来のデータと入れてみるとどうなるのでしょうか。2021年のデータを入れます。

INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0004,
    TO_DATE('2021/01/01','YYYY/MM/DD'),
    'D'
);
COMMIT;

統計情報更新後確認です。時期が飛んでパーティションが作られます。意図した通りに作られております。

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, NUM_ROWS, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES';

では、次に飛ばした月のデータを入れてみましょう。新たにパーティションが作られるのでしょうか？

INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0005,
    TO_DATE('2020/08/01','YYYY/MM/DD'),
    'E'
);
COMMIT;

統計情報更新後確認です。飛ばした月のデータでも、新たにパーティションが作られます。なかなかスマートな動きになっていますね。これは便利です。

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, NUM_ROWS, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES';

SELECT 実行計画

では、パーティション表からデータを取得してみましょう。5行しか入れていないので、性能はみれないので、実行計画を見て行きましょう。SQL Monitor 機能を使って、Enterprise Manager からリアルタイムに実行計画を見るために、/*+ MONITOR */ というヒント句を入れています。

SELECT /*+ MONITOR */ * FROM SALES;

実行例

実行計画です。1行目に PARTITION RANGE ALL とあり、すべてのパーティションからデータを取得していることが分かります。

なお、Enterprise Manager からでは、どのパーティションを使ったか表示されません。EXPLAIN コマンドでみると、使用するパーティションが具体的にわかります。

EXPLAIN PLAN FOR SELECT * FROM SALES;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

実行例です。
Pstart, Pstop が、どのパーティションから読み込み、どのパーティションで終わったかを表しています。Partition Position が 1 から全てのパーティションを読み込んでいることが分かります。

---------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     5 |    65 |  1092   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|       |     5 |    65 |  1092   (1)| 00:00:01 |     1 |1048575|
|   2 |   TABLE ACCESS FULL | SALES |     5 |    65 |  1092   (1)| 00:00:01 |     1 |1048575|
---------------------------------------------------------------------------------------------

それでは、実際にパーティションを有効活用する SQL クエリーを投げてみましょう。パーティションで指定した、sales_date を WHERE で指定するのが条件です。
パーティションで指定していないものを WHERE で指定すると、パーティション機能は無効になります。

SELECT /*+ MONITOR */ * FROM SALES WHERE sales_date BETWEEN TO_DATE('2020/01/01','YYYY/MM/DD') AND TO_DATE('2020/01/31','YYYY/MM/DD');

実行例

実行計画をみてみます。

EXPLAIN PLAN FOR SELECT * FROM SALES WHERE sales_date BETWEEN TO_DATE('2020/01/01','YYYY/MM/DD') AND TO_DATE('2020/01/31','YYYY/MM/DD');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

実行例
Pstart, Pstop が両方とも 1 となっています。特定のパーティションのみ取得できていることがわかります。関係ないデータはまったく読み込まずにスキップしているため、効率的に特定期間のデータを取得できます。

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    13 |   274   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       |     1 |    13 |   274   (0)| 00:00:01 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL    | SALES |     1 |    13 |   274   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------

なお、特定のパーティションを指定して、すべてのデータを取得できます。

SELECT * FROM SALES PARTITION(SALES_P01);

実行計画もみましょう。

EXPLAIN PLAN FOR SELECT * FROM SALES PARTITION(SALES_P01);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

実行例
1 のパーティションのみ使われていることが分かります。

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     2 |    26 |   274   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       |     2 |    26 |   274   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS FULL    | SALES |     2 |    26 |   274   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------

では次に、WHERE の絞り込み条件に、パーティションで指定した列以外の列を追加してみたらどういった動きになるでしょう？テストデータを格納するために、2020年1月のパーティションに10件のデータを Insert します。

INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0001,
    TO_DATE('2020/01/02','YYYY/MM/DD'),
    'A'
);
INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0001,
    TO_DATE('2020/01/03','YYYY/MM/DD'),
    'A'
);
INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0001,
    TO_DATE('2020/01/04','YYYY/MM/DD'),
    'A'
);
INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0002,
    TO_DATE('2020/01/05','YYYY/MM/DD'),
    'B'
);
INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0002,
    TO_DATE('2020/01/06','YYYY/MM/DD'),
    'B'
);
INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0002,
    TO_DATE('2020/01/07','YYYY/MM/DD'),
    'B'
);
INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0002,
    TO_DATE('2020/01/08','YYYY/MM/DD'),
    'B'
);
INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0003,
    TO_DATE('2020/01/09','YYYY/MM/DD'),
    'C'
);
INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0003,
    TO_DATE('2020/01/10','YYYY/MM/DD'),
    'C'
);
INSERT INTO sales (product_id, sales_date, Customer_id) VALUES (
    0003,
    TO_DATE('2020/01/11','YYYY/MM/DD'),
    'C'
);
COMMIT;

WHERE での条件指定

sales_date : パーティションで指定
product_id : パーティション無し

SELECT /*+ MONITOR */ * FROM SALES WHERE sales_date BETWEEN TO_DATE('2020/01/01','YYYY/MM/DD') AND TO_DATE('2020/01/31','YYYY/MM/DD') AND product_id = 2;

実行結果

PRODUCT_ID SALES_DAT CUSTOMER_ID                             
---------- --------- ----------------------------------------
         2 05-JAN-20 B                                       
         2 06-JAN-20 B                                       
         2 07-JAN-20 B                                       
         2 08-JAN-20 B

実行計画

EXPLAIN PLAN FOR SELECT /*+ MONITOR */ * FROM SALES WHERE sales_date BETWEEN TO_DATE('2020/01/01','YYYY/MM/DD') AND TO_DATE('2020/01/31','YYYY/MM/DD') AND product_id = 2;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY())

実行結果
Pstart と Pstop が両方とも 1 となっているため、パーティション 1 しかデータを読んでいないことが分かります。

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    13 |   274   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       |     1 |    13 |   274   (0)| 00:00:01 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL    | SALES |     1 |    13 |   274   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------

WHERE の順番を逆にしてみるとどうでしょう？

SELECT /*+ MONITOR */ * FROM SALES WHERE product_id = 2 AND sales_date BETWEEN TO_DATE('2020/01/01','YYYY/MM/DD') AND TO_DATE('2020/01/31','YYYY/MM/DD');

実行結果

PRODUCT_ID SALES_DAT CUSTOMER_ID                             
---------- --------- ----------------------------------------
         2 05-JAN-20 B                                       
         2 06-JAN-20 B                                       
         2 07-JAN-20 B                                       
         2 08-JAN-20 B

実行計画の表示

EXPLAIN PLAN FOR SELECT /*+ MONITOR */ * FROM SALES WHERE product_id = 2 AND sales_date BETWEEN TO_DATE('2020/01/01','YYYY/MM/DD') AND TO_DATE('2020/01/31','YYYY/MM/DD');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY())

実行結果
WHERE で指定する列の順番を変更しても変わりません

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    13 |   274   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       |     1 |    13 |   274   (0)| 00:00:01 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL    | SALES |     1 |    13 |   274   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------

パーティションの一部削除

一部のパーティションを一瞬で削除(DROP) が出来ます。たくさんの行があるテーブルでも、一瞬で削除出来るのもパーティションの強みです。
DELETE で大量のデータを削除するのは、非常に多くの時間とリソースがかかるので、パーティションのメリットはここでも活かせます。

まず、削除前のパーティション一覧を見ます。

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, NUM_ROWS, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES';

実行例
オレンジ色の SYS_P273 の、2020年2月分のパーティションを削除します。

実際に削除される行はこれです。2020年2月1日のデータが、パーティション削除で消えます。

ALTER TABLE でパーティションを指定して削除します。

ALTER TABLE sales DROP PARTITION SYS_P272;
COMMIT;

パーティション一覧を確認します。SYS_P273 が消えています。

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, NUM_ROWS, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES';

実際のデータも消えています

未検証 memo

次の2つのものを検証したいと思っています。そのうちやります！

既存テーブルをパーティション表に変更
Index

参考URL

パーティション化の概念
https://docs.oracle.com/cd/F19136_01/vldbg/partition-concepts.html#GUID-EA7EF5CB-DD49-43AF-889A-F83AAC0D7D51

実践！パーティションの基本から実装例まで
https://www.oracle.com/technetwork/jp/ondemand/database/db-technique/d-12-disk-1484778-ja.pdf

[超図解]パーティション化(1/5) パーティション事始め
https://xn--w8j8bac3czf5bl7e.com/2018/12/25/%E8%B6%85%E5%9B%B3%E8%A7%A3%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%B3%E5%8C%961-5-%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%B3%E4%BA%8B%E5%A7%8B%E3%82%81/

しばちょう先生の試して納得！DBAへの道　第48回 [Oracle Database 12c] 時間隔パーティション表（インターバル・パーティション表）のクセ
https://blogs.oracle.com/otnjp/shibacho-048

Oracle 11g　インターバル・パーティションに関する検証　その２
https://old.insight-tec.com/mailmagazine/ora3/vol403.html