パーティション・プルーニングの有効性を検証する：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     | Pstart| Pstop |

---------------------------------------------------------------------

----------------------------------

|   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     | Pstart| Pstop |

-----------------------------------------------------------------

----------------------------------

|   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     | Pstart| Pstop |

------------------------------------------------------------------

----------------------------------

|   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 次のページへ

SpecialPR