3.1 パーティション・プルーニング
パーティション・プルーニングは、データ・ウェアハウスの重要なパフォーマンス機能です。
パーティション・プルーニングでは、オプティマイザによりSQL文のFROM句とWHERE句が分析され、パーティション・アクセス・リストを構築するときに不要なパーティションが削除されます。この機能によって、Oracle Databaseは、SQL文に関連するパーティションに限定して処理を実行できるようになります。
次の内容について説明します。
3.1.1 パーティション・プルーニングの利点
パーティション・プルーニングを行うと、ディスクから取得するデータ容量が大幅に削減され、処理時間が短縮します。このため、問合せのパフォーマンスが向上し、リソース使用率が最適化されます。
グローバルなパーティション索引を使用して異なる列の索引および表をパーティション化する場合は、基礎となる表が排除できない場合でもパーティション・プルーニングにより索引パーティションが排除されます。
実際のSQL文に応じて、Oracle Databaseにより静的プルーニングおよび動的プルーニングが使用されます。静的プルーニングは、事前にアクセスしたパーティションの情報を使用してコンパイル時に行われます。動的プルーニングは実行時に行われます。つまり、文がアクセスする正確なパーティションは事前にはわかりません。静的プルーニングのサンプルの使用例は、パーティション・キー列に固定リテラルがあるWHERE条件を含むSQL文です。動的プルーニングの例は、WHERE条件内の演算子または関数の使用です。
パーティション・プルーニングは、プルーニングが行われるオブジェクトの統計に影響します。また文の実行計画にも影響します。
3.1.2 パーティション・プルーニングに使用できる情報
パーティション・プルーニングは、パーティション化列で実行できます。
Oracle Databaseでパーティション・プルーニングが行われるのは、レンジ・パーティション化列またはリスト・パーティション化列で、範囲述語、LIKE述語、等価述語、INリスト述語を使用したとき、またハッシュ・パーティション化列で等価述語およびINリスト述語を使用したときです。
コンポジット・パーティション化されたオブジェクトでは、Oracle Databaseは関連する述語を使用して両方のレベルでプルーニングを実行できます。たとえば、表sales_range_hashを確認してください。これは、例3-1に示すように、列s_saledateでレンジ・パーティション化され、列s_productidでハッシュ・サブパーティション化されています。
パーティション列に対する述語を使用して、次のようにパーティション・プルーニングが行われます。
-
レンジ・パーティション化を使用して、1999年の第3四半期と第4四半期に相当するパーティション
sal99q2とsal99q3のみにアクセスします。 -
ハッシュ・サブパーティション化を使用して、各パーティションで、
s_productid=1200の行を格納する1つのサブパーティションのみにアクセスします。サブパーティションと述語のマッピングは、Oracle内部のハッシュ分散関数に基づいて計算されます。
参照パーティション表では、参照表の結合を介してパーティション・プルーニングを利用できます。仮想列に基づくパーティション表では、仮想列定義式を使用するSQL文でパーティション・プルーニングを利用できます。
例3-1 パーティション・プルーニングによる表の作成
CREATE TABLE sales_range_hash(
s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
PARTITION BY RANGE (s_saledate)
SUBPARTITION BY HASH (s_productid) SUBPARTITIONS 8
(PARTITION sal99q1 VALUES LESS THAN
(TO_DATE('01-APR-1999', 'DD-MON-YYYY')),
PARTITION sal99q2 VALUES LESS THAN
(TO_DATE('01-JUL-1999', 'DD-MON-YYYY')),
PARTITION sal99q3 VALUES LESS THAN
(TO_DATE('01-OCT-1999', 'DD-MON-YYYY')),
PARTITION sal99q4 VALUES LESS THAN
(TO_DATE('01-JAN-2000', 'DD-MON-YYYY')));
SELECT * FROM sales_range_hash
WHERE s_saledate BETWEEN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY'))
AND (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')) AND s_productid = 1200;
3.1.3 パーティション・プルーニングが使用されたかどうかを識別する方法
Oracleがパーティション・プルーニングを使用するかどうかは、EXPLAIN PLAN文の計画表または共有SQL領域にある文の実行計画に示されます。
パーティション・プルーニング情報は、計画列PSTART(PARTITION_START)およびPSTOP(PARTITION_STOP)に反映されます。シリアル文の場合、プルーニング情報はOPERATION列とOPTIONS列にも反映されます。
関連項目:
EXPLAIN PLANとその解釈方法の詳細は、Oracle Database SQLチューニング・ガイドを参照してください
3.1.4 静的パーティション・プルーニング
Oracleでは、主に静的述語に基づいて、静的プルーニングを使用する時期が決定されます。
多くの場合、Oracleはコンパイル時にアクセスするパーティションを決定します。静的パーティション・プルーニングが行われるのは、静的な述語を使用したときですが、次の場合を除きます。
-
副問合せの結果を使用してパーティション・プルーニングが行われる場合。
-
オプティマイザがスター型変換を使用して問合せを再作成し、スター型変換の後でプルーニングが行われる場合。
-
最も有効な実行計画がネステッド・ループである場合。
これら3つの場合には、動的プルーニングが使用されます。
解析時にOracleが、隣接するどのパーティション・セットがアクセスされるかを識別できると、実行計画のPSTART列とPSTOP列に、アクセスされるパーティションの開始値と終了値が示されます。動的プルーニングを含む他のパーティション・プルーニングでは、PSTARTおよびPSTOPのKEY値と、オプションでその他の属性が表示されます。
次に、例を示します。
SQL> explain plan for select * from sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3971874201
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 673 | 19517 | 27 (8)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 673 | 19517 | 27 (8)| 00:00:01 | 17 | 17 |
|* 2 | TABLE ACCESS FULL | SALES | 673 | 19517 | 27 (8)| 00:00:01 | 17 | 17 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TIME_ID"=TO_DATE('2001-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
この計画では、PSTART列とPSTOP列に示されているようにOracleがパーティション番号17にアクセスします。OPERATION列にPARTITION RANGE SINGLEとあり、1つのパーティションのみにアクセスすることを示します。OPERATIONにPARTITION RANGE ALLと表示されている場合は、すべてのパーティションにアクセスし、実質的にはプルーニングは行われません。このとき、PSTARTには表の最初のパーティションが示され、PSTOPには最後のパーティションが示されます。
時間隔パーティション表の全表スキャンを含む実行計画では、作成された時間隔パーティションの数にかかわらず、PSTARTに1、PSTOPに1048575が示されます。
3.1.5 動的パーティション・プルーニング
このトピックでは、Oracleの動的パーティション・プルーニングを紹介します。
動的プルーニングが行われるのは、プルーニングが可能で、静的プルーニングが不可能な場合です。次に動的プルーニングのいくつかの例を示します。
3.1.5.1 バインド変数を含む動的プルーニング
パーティション列に対してバインド変数を使用する文では、動的プルーニングが行われます。
次にSQL文の例を示します。
SQL> explain plan for select * from sales s where time_id in ( :a, :b, :c, :d);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 513834092
---------------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes|Cost (%CPU)| Time | Pstart| Pstop|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |2517|72993| 292 (0)|00:00:04| | |
| 1 | INLIST ITERATOR | | | | | | | |
| 2 | PARTITION RANGE ITERATOR | |2517|72993| 292 (0)|00:00:04|KEY(I) |KEY(I)|
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |2517|72993| 292 (0)|00:00:04|KEY(I) |KEY(I)|
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE |SALES_TIME_BIX| | | | |KEY(I) |KEY(I)|
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TIME_ID"=:A OR "TIME_ID"=:B OR "TIME_ID"=:C OR "TIME_ID"=:D)
パラレル実行計画では、次の例のようにpstart列とpstop列のみにパーティション・プルーニング情報が含まれ、operation列にはパラレル操作の情報が含まれます。
SQL> explain plan for select * from sales where time_id in (:a, :b, :c, :d);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 4058105390
-------------------------------------------------------------------------------------------------
| Id| Operation | Name |Rows|Bytes|Cost(%CP| Time |Pstart| Pstop| TQ |INOUT| PQ Dis|
-------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | |2517|72993| 75(36)|00:00:01| | | | | |
| 1| PX COORDINATOR | | | | | | | | | | |
| 2| PX SEND QC(RANDOM)|:TQ10000|2517|72993| 75(36)|00:00:01| | |Q1,00| P->S|QC(RAND|
| 3| PX BLOCK ITERATOR| |2517|72993| 75(36)|00:00:01|KEY(I)|KEY(I)|Q1,00| PCWC| |
|* 4| TABLE ACCESS FULL| SALES |2517|72993| 75(36)|00:00:01|KEY(I)|KEY(I)|Q1,00| PCWP| |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("TIME_ID"=:A OR "TIME_ID"=:B OR "TIME_ID"=:C OR "TIME_ID"=:D)
関連項目:
EXPLAIN PLANとその解釈方法の詳細は、Oracle Database SQLチューニング・ガイドを参照してください
3.1.5.2 副問合せを含む動的プルーニング
パーティション列に対して明示的に副問合せを使用する文では、動的プルーニングが行われます。
次にSQL文の例を示します。
SQL> explain plan for select sum(amount_sold) from sales where time_id in
(select time_id from times where fiscal_year = 2000);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3827742054
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 523 (5)| 00:00:07 | | |
| 1 | SORT AGGREGATE | | 1 | 25 | | | | |
|* 2 | HASH JOIN | | 191K| 4676K| 523 (5)| 00:00:07 | | |
|* 3 | TABLE ACCESS FULL | TIMES | 304 | 3648 | 18 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE SUBQUERY| | 918K| 11M| 498 (4)| 00:00:06 |KEY(SQ)|KEY(SQ)|
| 5 | TABLE ACCESS FULL | SALES | 918K| 11M| 498 (4)| 00:00:06 |KEY(SQ)|KEY(SQ)|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TIME_ID"="TIME_ID")
3 - filter("FISCAL_YEAR"=2000)
関連項目:
EXPLAIN PLANとその解釈方法の詳細は、Oracle Database SQLチューニング・ガイドを参照してください
3.1.5.3 スター型変換を含む動的プルーニング
データベースによってスター型変換を使用して変換される文では、動的なプルーニングが行われます。
次にSQL文の例を示します。
SQL> explain plan for select p.prod_name, t.time_id, sum(s.amount_sold)
from sales s, times t, products p
where s.time_id = t.time_id and s.prod_id = p.prod_id and t.fiscal_year = 2000
and t.fiscal_week_number = 3 and p.prod_category = 'Hardware'
group by t.time_id, p.prod_name;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 4020965003
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | | |
| 1 | HASH GROUP BY | | 1 | 79 | | |
|* 2 | HASH JOIN | | 1 | 79 | | |
|* 3 | HASH JOIN | | 2 | 64 | | |
|* 4 | TABLE ACCESS FULL | TIMES | 6 | 90 | | |
| 5 | PARTITION RANGE SUBQUERY | | 587 | 9979 |KEY(SQ)|KEY(SQ)|
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 587 | 9979 |KEY(SQ)|KEY(SQ)|
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 8 | BITMAP AND | | | | | |
| 9 | BITMAP MERGE | | | | | |
| 10 | BITMAP KEY ITERATION | | | | | |
| 11 | BUFFER SORT | | | | | |
|* 12 | TABLE ACCESS FULL | TIMES | 6 | 90 | | |
|* 13 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | | |KEY(SQ)|KEY(SQ)|
| 14 | BITMAP MERGE | | | | | |
| 15 | BITMAP KEY ITERATION | | | | | |
| 16 | BUFFER SORT | | | | | |
| 17 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 14 | 658 | | |
|* 18 | INDEX RANGE SCAN | PRODUCTS_PROD_CAT_IX | 14 | | | |
|* 19 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | | |KEY(SQ)|KEY(SQ)|
| 20 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | 14 | 658 | | |
|* 21 | INDEX RANGE SCAN | PRODUCTS_PROD_CAT_IX | 14 | | | |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."PROD_ID"="P"."PROD_ID")
3 - access("S"."TIME_ID"="T"."TIME_ID")
4 - filter("T"."FISCAL_WEEK_NUMBER"=3 AND "T"."FISCAL_YEAR"=2000)
12 - filter("T"."FISCAL_WEEK_NUMBER"=3 AND "T"."FISCAL_YEAR"=2000)
13 - access("S"."TIME_ID"="T"."TIME_ID")
18 - access("P"."PROD_CATEGORY"='Hardware')
19 - access("S"."PROD_ID"="P"."PROD_ID")
21 - access("P"."PROD_CATEGORY"='Hardware')
Note
-----
- star transformation used for this statement
ノート:
Cost (%CPU)列とTime列は、この例の計画表の出力では削除されています。
関連項目:
EXPLAIN PLANとその解釈方法の詳細は、Oracle Database SQLチューニング・ガイドを参照してください
3.1.5.4 ネステッド・ループ結合を含む動的プルーニング
ネステッド・ループ結合を使用すると最も効率的に実行される文では、動的プルーニングが使用されます。
次にSQL文の例を示します。
SQL> explain plan for select t.time_id, sum(s.amount_sold)
from sales s, times t
where s.time_id = t.time_id and t.fiscal_year = 2000 and t.fiscal_week_number = 3
group by t.time_id;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 50737729
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 168 | 126 (4)| 00:00:02 | | |
| 1 | HASH GROUP BY | | 6 | 168 | 126 (4)| 00:00:02 | | |
| 2 | NESTED LOOPS | | 3683 | 100K| 125 (4)| 00:00:02 | | |
|* 3 | TABLE ACCESS FULL | TIMES | 6 | 90 | 18 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE ITERATOR| | 629 | 8177 | 18 (6)| 00:00:01 | KEY | KEY |
|* 5 | TABLE ACCESS FULL | SALES | 629 | 8177 | 18 (6)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."FISCAL_WEEK_NUMBER"=3 AND "T"."FISCAL_YEAR"=2000)
5 - filter("S"."TIME_ID"="T"."TIME_ID")
関連項目:
EXPLAIN PLANとその解釈方法の詳細は、Oracle Database SQLチューニング・ガイドを参照してください
3.1.6 ゾーン・マップを使用するパーティション・プルーニング
パーティション・プルーニングが拡張され、完全なパーティションのプルーニングにゾーン・マップを利用できるようになりました。拡張されたプルーニング機能を提供することで、リソース消費が削減され、情報を得るまでの時間が短縮され、パフォーマンスが向上します。
ゾーン・マップは、表に対して作成できる、独立したアクセス構造です。表スキャン中にゾーン・マップを使用して、表のディスク・ブロックおよびパーティション表のパーティションを、表の列の述語に基づいてプルーニングできます。ゾーン・マップは、パーティション表のパーティション・キー列と相関関係がないため、ゾーン・マップを持つパーティション表の文では、非パーティション・キー列に基づいてパーティションをプルーニングできます。
関連項目:
ゾーン・マップおよび属性クラスタリングの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。
ゾーン・マップを使用するパーティション・プルーニングは、ゾーン・マップ列の値とパーティション・キー列の値に相関関係がある場合に特に有効です。たとえば、同じパーティション表内のパーティション・キー列「受注日」と相関関係のある「出荷日」など、パーティション表自体の列間、またはパーティション表のパーティション・キー列「日」と相関関係のあるディメンション表「時間」の結合ゾーン・マップ列「月の説明」など、結合ゾーン・マップ列およびパーティション表には、相関関係があります。
例3-2に、パーティション表の相関関係のある列での、ゾーン・マップを使用するパーティション・プルーニングを示します。パーティション表sales_rangeの列s_shipdateはパーティション・キー列order_dateと相関関係があります。受注は一般に、受注を受けとった日から数日以内に出荷されるためです。
s_shipdateとパーティション・キー列に相関関係があるため、列がパーティション・キーの一部でなくても、この列の選択的な述語でパーティション表sales_rangeをパーティション・プルーニングできる見込みが高くなります。
次のSELECT文は、1999年の第1四半期に出荷されたすべての受注を検索します。
SELECT * FROM sales_range
WHERE s_shipdate BETWEEN to_date('01/01/1999','dd/mm/yyyy')
AND to_date('03/01/1999','mm/dd/yyyy');
前述のSELECT文の次の実行計画では、ゾーン・マップがパーティション・プルーニングに使用され、アクセスされるパーティションからブロックをプルーニングするためにも使用されます。
ゾーン・マップを使用するパーティション・プルーニングは、実行計画のPSTARTおよびPSTOP列にあるKEY(ZM)によって識別されます。アクセスされるすべてのパーティションのブロック・レベルのプルーニングは、表アクセス時間のフィルタ述語によって識別されます(id 2)。
例3-2 属性クラスタリングのあるパーティション表sales_rangeおよび相関する列のゾーン・マップ
CREATE TABLE sales_range(
s_productid NUMBER,
s_saledate DATE,
s_shipdate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
CLUSTERING BY (s_shipdate)
WITH MATERIALIZED ZONEMAP
PARTITION BY RANGE (s_saledate)
(PARTITION sal99q1 VALUES LESS THAN
(TO_DATE('01-APR-1999', 'DD-MON-YYYY')),
PARTITION sal99q2 VALUES LESS THAN
(TO_DATE('01-JUL-1999', 'DD-MON-YYYY')),
PARTITION sal99q3 VALUES LESS THAN
(TO_DATE('01-OCT-1999', 'DD-MON-YYYY')),
PARTITION sal99q4 VALUES LESS THAN
(TO_DATE('01-JAN-2000', 'DD-MON-YYYY')));
例3-3 ゾーン・マップを使用するパーティション・プルーニングの実行計画
--------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | | | 1 | PARTITION RANGE ITERATOR | | 58 | 3306 | 3 (0)| 00:00:01 |KEY(ZM)|KEY(ZM)| |* 2 | TABLE ACCESS FULL WITH ZONEMAP| SALES_RANGE | 58 | 3306 | 3 (0)| 00:00:01 |KEY(ZM)|KEY(ZM)| ---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((SYS_ZMAP_FILTER('/* ZM_PRUNING */ SELECT "ZONE_ID$", CASE WHEN
BITAND(zm."ZONE_STATE$",1)=1 THEN 1 ELSE CASE WHEN (zm."MAX_1_S_SHIPDATE" < :1 OR
zm."MIN_1_S_SHIPDATE" > :2) THEN 3 ELSE 2 END END FROM "SH"."ZMAP$_SALES_RANGE" zm WHERE
zm."ZONE_LEVEL$"=0 ORDER BY zm."ZONE_ID$"',SYS_OP_ZONE_ID(ROWID),TO_DATE(' 1999-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'),TO_DATE(' 1999-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<3 AND
"S_SHIPDATE">=TO_DATE(' 1999-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss') AND "S_SHIPDATE"<=TO_DATE('
1999-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
3.1.7 パーティション・プルーニングのヒント
このトピックでは、パーティション・プルーニングのヒントを紹介します。
パーティション・プルーニングを使用するときは、次の点を考慮する必要があります。
ノート:
CASTやTRUNCなどのファンクションまたは変換を使用してパーティション列を操作する場合、パーティション・プルーニングは行われません。
3.1.7.1 データ型の変換
パーティション・プルーニングから最大限のパフォーマンスを得るには、データベースによって変換が必要となるデータ型を指定しないようにしてください。
データ型が変換されると、その他の点で静的プルーニングが可能な場合でも、通常は動的プルーニングが行われます。静的プルーニングを利用するSQL文は、動的プルーニングを利用するSQL文よりもパフォーマンスが高くなります。
OracleのDATEデータ型を使用すると、一般的なデータ型変換が行われます。OracleのDATEデータ型は文字列ではありませんが、データベースを問い合せたときに文字列のように表示されます。表示形式はインスタンスまたはセッションのNLS設定で定義されます。したがって、データをDATEフィールドに挿入するとき、またはそのようなフィールドに述語を指定するとき、同じ変換を逆向きに実行する必要があります。
変換は暗黙に実行するか、TO_DATE変換を指定して明示的に実行することができます。TO_DATE関数を適切に適用した場合のみ、データベースが一意に日付値を判別して、その値を静的プルーニングに潜在的に使用できるようになります。これは、1つのパーティションにアクセスするために特に役立ちます。
sales表に対して実行する、次の例を考察します。2000年の合計収益を問い合せる場合があります。この問合せの結果を取得する方法はたくさんありますが、すべての方法の効率が同じではありません。
explain plan for SELECT SUM(amount_sold) total_revenue FROM sales, WHERE time_id between '01-JAN-00' and '31-DEC-00';
計画は次のようになります。
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 525 (8)| 00:00:07 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | 230K| 2932K| 525 (8)| 00:00:07 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | SALES | 230K| 2932K| 525 (8)| 00:00:07 | KEY | KEY |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('01-JAN-00')<=TO_DATE('31-DEC-00'))
4 - filter("TIME_ID">='01-JAN-00' AND "TIME_ID"<='31-DEC-00')
この場合、PSTARTとPSTOP両方のキーワードKEYは、動的パーティション・プルーニングが実行時に行われることを示します。次の例について考えてください。
explain plan for select sum(amount_sold) from sales where time_id between '01-JAN-2000' and '31-DEC-2000' ;
この実行計画は次のようになります。
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 127 (4)| | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | PARTITION RANGE ITERATOR| | 230K| 2932K| 127 (4)| 13 | 16 |
|* 3 | TABLE ACCESS FULL | SALES | 230K| 2932K| 127 (4)| 13 | 16 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', "syyyy-mm-dd hh24:mi:ss'))
ノート:
Time列は、この実行計画からは削除されています。
この実行計画では静的パーティション・プルーニングが示されます。問合せは、隣接するパーティション13から16にアクセスします。この場合は、日付形式の指定方法が、NLS日付形式の設定と一致しています。この例では最も効果的な実行計画が示されていますが、NLS日付形式設定を利用して特定の形式を定義することはできません。
alter session set nls_date_format='fmdd Month yyyy'; explain plan for select sum(amount_sold) from sales where time_id between '01-JAN-2000' and '31-DEC-2000' ;
この実行計画は次のようになります。
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 525 (8)| | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
|* 2 | FILTER | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | 230K| 2932K| 525 (8)| KEY | KEY |
|* 4 | TABLE ACCESS FULL | SALES | 230K| 2932K| 525 (8)| KEY | KEY |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('01-JAN-2000')<=TO_DATE('31-DEC-2000'))
4 - filter("TIME_ID">='01-JAN-2000' AND "TIME_ID"<='31-DEC-2000')
ノート:
Time列は、この実行計画からは削除されています。
動的プルーニングを使用するこの計画は、静的プルーニングの実行計画よりも効率が悪くなります。静的パーティション・プルーニング計画を保証するには、パーティション列のデータ型と一致するようにデータ型を明示的に変換する必要があります。例:
explain plan for select sum(amount_sold)
from sales
where time_id between to_date('01-JAN-2000','dd-MON-yyyy')
and to_date('31-DEC-2000','dd-MON-yyyy') ;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 127 (4)| | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | PARTITION RANGE ITERATOR| | 230K| 2932K| 127 (4)| 13 | 16 |
|* 3 | TABLE ACCESS FULL | SALES | 230K| 2932K| 127 (4)| 13 | 16 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
ノート:
Time列は、この実行計画からは削除されています。
関連項目:
-
DATEデータ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください -
NLSの設定およびグローバリゼーションの問題の詳細は、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください
3.1.7.2 関数のコール
関数により、プルーニングを実行するオプティマイザの機能を制限できます。
オプティマイザがプルーニングを実行できない場合がいくつかあります。一般的な理由の1つは、パーティション列に対する演算子の使用です。これには、明示的な演算子(たとえば関数)も、文を実行するために必要なデータ型変換の一環としてOracleが導入した暗黙の演算子も該当します。たとえば、次の問合せを考えてみます。
EXPLAIN PLAN FOR
SELECT SUM(quantity_sold)
FROM sales
WHERE time_id = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy');
time_idはDATE型ですが、Oracleは同じデータ型を取得するためにTIMESTAMP型に変換する必要があるため、この述語は内部で次のように変更されます。
TO_TIMESTAMP(time_id) = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy')
この文の実行計画は次のとおりです。
--------------------------------------------------------------------------------------------
|Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 6 (17)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 11 | | | | |
| 2 | PARTITION RANGE ALL| | 10 | 110 | 6 (17)| 00:00:01 | 1 | 16 |
|*3 | TABLE ACCESS FULL | SALES | 10 | 110 | 6 (17)| 00:00:01 | 1 | 16 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(INTERNAL_FUNCTION("TIME_ID")=TO_TIMESTAMP('1-jan-2000',:B1))
15 rows selected
このSELECT文は、プルーニングによって1つのパーティションが限定される場合でも、すべてのパーティションにアクセスします。2000年の合計売上高を調べる例を考えてみます。問合せを構成するもう1つの方法は、次のとおりです。
EXPLAIN PLAN FOR SELECT SUM(amount_sold) FROM sales WHERE TO_CHAR(time_id,'yyyy') = '2000';
この問合せは関数コールをパーティション・キー列に適用します。通常はこのためにパーティション・プルーニングが無効になります。実行計画では全表スキャンが示され、パーティション・プルーニングはありません。
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 527 (9)| 00:00:07 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE ALL| | 9188 | 116K| 527 (9)| 00:00:07 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 9188 | 116K| 527 (9)| 00:00:07 | 1 | 28 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_CHAR(INTERNAL_FUNCTION("TIME_ID"),'yyyy')='2000')
パーティション列に対しては暗黙的にも明示的にも関数を使用しないことをお薦めします。問合せがよく関数コールを使用する場合は、このようなケースでパーティション・プルーニングを利用できるように仮想列と仮想列パーティション化の使用を検討してください。
3.1.7.3 コレクション表
コレクション表により、プルーニングを実行するオプティマイザの機能を制限できます。
次の例は、EXPLAIN PLAN文がコレクション表を含むときに、どのように表示されるかを示します(コレクション表は、ここでは便宜上、ソートされたコレクション表またはネスト表とします)。該当するパーティションのみに制約されるため、全表アクセスは実行されません。
EXPLAIN PLAN FOR SELECT p.ad_textdocs_ntab FROM print_media_part p; Explained. PLAN_TABLE_OUTPUT ----------------------------------------------------------------------- Plan hash value: 2207588228 ----------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | PARTITION REFERENCE SINGLE| | KEY | KEY | | 2 | TABLE ACCESS FULL | TEXTDOC_NT | KEY | KEY | | 3 | PARTITION RANGE ALL | | 1 | 2 | | 4 | TABLE ACCESS FULL | PRINT_MEDIA_PART | 1 | 2 | ----------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement
関連項目:
EXPLAIN PLANのベースとなるCREATE TABLE文の例は、「XMLTypeおよびオブジェクトのコレクションのパーティション化」を参照してください