パーティション・プルーニングの有効性を検証するOracleパーティショニング実践講座(2)(1/4 ページ)

本連載では、大規模データベースでのパフォーマンス・チューニングの手法として、Oracleパーティショニングを解説する。単なる機能説明にとどまらず、実機による検証結果を加えて、より実践的な内容をお届けする。(編集部)

» 2006年12月27日 00時00分 公開
[高田春子アシスト]

この記事は会員限定です。会員登録(無料)すると全てご覧いただけます。

 前回「パーティショニングは大規模DBの性能向上に効く」で説明したように、パーティショニングは、大規模なデータを扱ううえでポイントとなる機能です。特にアクセスするパーティションを自動的に絞り込むパーティション・プルーニング機能にパフォーマンス効果を期待する方は多いのではないでしょうか。今回はパーティション・プルーニング機能の有効性について紹介したいと思います。

パーティション・プルーニングが機能する条件

 パーティション表にアクセスしただけで、パーティション・プルーニングが機能するわけではありません。パーティショニングではパーティション・キーと呼ばれる、データを分割する際に基準となる列を設定しますが、このパーティション・キーをパーティション表にアクセスするSQLのWHERE句の条件として指定しなければ、アクセスするパーティションを絞り込むことはできないのです。

 例えば、リスト1のように定義したsales_range表の場合、パーティション・プルーニングを機能させるには、リスト2のようにパーティション・キーであるsales_date列をWHERE句の条件に指定する必要があります。

CREATE TABLE sales_range
(salesman_id   NUMBER(5)   ,
 salesman_name VARCHAR2(30),
 sales_amount  NUMBER(10)  ,
 sales_date    DATE
)PARTITION BY RANGE(sales_date)
 (PARTITION p2006q1 VALUES LESS THAN(TO_DATE('2006-04-01','YYYY-MM-
DD')),
  PARTITION p2006q2 VALUES LESS THAN(TO_DATE('2006-07-01','YYYY-MM-
DD')),
  PARTITION p2006q3 VALUES LESS THAN(TO_DATE('2006-10-01','YYYY-MM-
DD')),
  PARTITION p2006q4 VALUES LESS THAN(TO_DATE('2007-01-01','YYYY-MM-
DD')));
リスト1 sales_date列でレンジ・パーティション化される表(拡大表示)


set autotrace on explain

SQL> SELECT count(*) FROM sales_range
  2  WHERE sales_date BETWEEN TO_DATE('20060501','YYYYMMDD')
  3  AND TO_DATE('20060531','YYYYMMDD');

  COUNT(*)
----------
     43202
---------------------------------------------------------------------
----------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost 
(%CPU)| Time     | PstartPstop |
---------------------------------------------------------------------
----------------------------------
|   0 | SELECT STATEMENT        |             |     1 |     8 |   172
  (20)| 00:00:03 |       |       |
|   1 |  SORT AGGREGATE         |             |     1 |     8 |      
      |          |       |       |
|   2 |   PARTITION RANGE SINGLE|             | 43146 |   337K|   172
  (20)| 00:00:03 |     2 |     2 |
|*  3 |    TABLE ACCESS FULL    | SALES_RANGE | 43146 |   337K|   172
  (20)| 00:00:03 |     2 |     2 |
---------------------------------------------------------------------
----------------------------------
リスト2 パーティション・プルーニングを機能させるSQL(拡大表示)
SQLの実行計画の確認にはSQL*PlusのAUTOTRACE機能を利用します。

 パーティション・プルーニングが行われているかどうかはSQLの実行計画を確認します。リスト2の実行計画を参照すると、アクセスしたパーティションの範囲を示す「Pstart」「Pstop」がそれぞれ「2、2」であることから2番目のパーティションがアクセスされたこと、また、「Operation」に「PARTITION RANGE SINGLE」が表示されていることから、パーティション・プルーニングが行われたことが分かります。

 またリスト3のように、パーティション・キーではないsalesman_name列の値を条件とした場合は、パーティション・プルーニングは機能しません。

SQL> SELECT * FROM sales_range
  2  WHERE salesman_name='ASHISUTO1';

SALESMAN_ID SALESMAN_NAME                  SALES_AMOUNT SALES_DA
----------- ------------------------------ ------------ --------
          1 ASHISUTO1                               100 06-05-25

-----------------------------------------------------------------
----------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost 
(%CPU)| Time     | PstartPstop |
-----------------------------------------------------------------
----------------------------------
|   0 | SELECT STATEMENT    |             |   526K|    13M|   669
  (19)| 00:00:09 |       |       |
|   1 |  PARTITION RANGE ALL|             |   526K|    13M|   669
  (19)| 00:00:09 |     1 |     4 |
|*  2 |   TABLE ACCESS FULL | SALES_RANGE |   526K|    13M|   669
  (19)| 00:00:09 |     1 |     4 |
-----------------------------------------------------------------
----------------------------------
リスト3 パーティション・キーではないsalesman_name列の値を条件とした場合(拡大表示)

 リスト3の実行計画では、アクセスしたパーティションの範囲を示す「Pstart」「Pstop」が「1、4」であることから、1〜4番目のパーティションまでアクセスしたこと、「Operation」に「PARTITION RANGE ALL」が表示されていることから、sales_range表を構成する4つのパーティションのすべてにアクセスしたことが分かります。

 パーティション・キーであるsales_date列をWHERE句の条件にしても、リスト4のようなSQLの場合、パーティション・プルーニングは機能しません。

SQL> SELECT COUNT(*) FROM sales_range
  2  WHERE TO_CHAR(sales_date,'YYYYMM')='200605';

  COUNT(*)
----------
     44641
------------------------------------------------------------------
----------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost 
(%CPU)| Time     | PstartPstop |
------------------------------------------------------------------
----------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     8 |   877
  (38)| 00:00:11 |       |       |
|   1 |  SORT AGGREGATE      |             |     1 |     8 |      
      |          |       |       |
|   2 |   PARTITION RANGE ALL|             |  5267 | 42136 |   877
  (38)| 00:00:11 |     1 |     4 |
|*  3 |    TABLE ACCESS FULL | SALES_RANGE |  5267 | 42136 |   877
  (38)| 00:00:11 |     1 |     4 |
------------------------------------------------------------------
----------------------------------
リスト4 パーティション・キーを条件にしても機能しない例(拡大表示)

 リスト4は2006年5月の情報にアクセスするという意味ではリスト2のSQLと同じですし、パーティション・キーであるsales_date列もWHERE句の条件に指定されています。2つのSQLとの違いは、パーティション・キーであるsales_date列が修飾されているかどうかです。リスト4のSQLではTO_CHAR関数がsales_date列に使用されています。

 このように、パーティション・キーに対して関数や算術を実施したり、暗黙の型変換が行われたりする場合、パーティション・プルーニングは機能しないということです。索引を利用したアクセスと同様に、パーティション・プルーニングを機能させる場合には、SQLの記述ルールを守る必要があります。

       1|2|3|4 次のページへ

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。