Oracle Databaseのプルーニング機能では、複数のパーティション表を対象とする、より複雑な述語やSQL文も効率よく処理されます。パーティション表が、WHERE
条件により限定された別の表のサブセットに結合される場合などは、その典型例です。たとえば、次の問合せを考えてみます。
SELECT t.day_number_in_month, SUM(s.amount_sold) FROM sales s, times t WHERE s.time_id = t.time_id AND t.calendar_month_desc='2000-12' GROUP BY t.day_number_in_month;
データベースが、右側にあるtimes
表とのネステッド・ループ結合を実行した場合、問合せはtimes
表のこの行に対応するパーティションにのみアクセスすることになります。つまりプルーニングは暗黙に実行されます。ただし、データベースでハッシュ結合またはソート・マージ結合が実行される場合は、そうはなりません。WHERE
述語の対象となる表がパーティション表と比べて小さく、かつパーティション表から多くのレコードやパーティションが除かれると予想される場合は、再帰的な副問合せを使用して動的パーティション・プルーニングが実行されます。副問合せによるプルーニングを実行するかどうかは、オプティマイザによるコストベースの内部的な判断により決定されます。
ハッシュ結合操作を使用した実行計画は、たとえば次のようになります。
-------------------------------------------------------------------------------------------------- | Id| Operation | Name | Rows | Bytes| Cost (%CPU)| Time | Pstart | Pstop | -------------------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | | 761 (100) | | | | | 1| HASH GROUP BY | | 20 | 640 | 761 (41) |00:00:10| | | |* 2| HASH JOIN | | 19153 | 598K | 749 (40) |00:00:09| | | |* 3| TABLE ACCESS FULL | TIMES | 30 | 570 | 17 (6) |00:00:01| | | | 4| PARTITION RANGE SUBQUERY | | 918K | 11M | 655 (33) |00:00:08| KEY(SQ)|KEY(SQ)| | 5| TABLE ACCESS FULL | SALES | 918 | 11M | 655 (33) |00:00:08| KEY(SQ)|KEY(SQ)| -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- 2 - access("S"."TIME_ID"="T"."TIME_ID") 3 - filter("T"."CALENDAR_MONTH_DESC"='2000-12')
この実行計画では、PSTART
列とPSTOP
列のKEY(SQ)
値に示されるように、副問合せを使用してsales
表で動的パーティション・プルーニングが実行されたことがわかります。
次にOR
述語を使用した高度なプルーニングの例を示します。
SELECT p.promo_name promo_name, (s.profit - p.promo_cost) profit FROM promotions p, ( SELECT sales.promo_id, SUM(sales.QUANTITY_SOLD * (costs.UNIT_PRICE - costs.UNIT_COST)) profit FROM sales, costs WHERE ((sales.time_id BETWEEN TO_DATE('01-JAN-1998','DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American') AND TO_DATE('01-JAN-1999','DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American') OR (sales.time_id BETWEEN TO_DATE('01-JAN-2001','DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American') AND TO_DATE('01-JAN-2002','DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'))) AND sales.time_id = costs.time_id AND sales.prod_id = costs.prod_id) GROUP BY sales.promo_id) s WHERE s.promo_id = p.promo_id ORDER BY profit DESC;
この問合せでは、sh
サンプル・スキーマのsales
表とcosts
表が結合されます。sales
表は、time_id
列でレンジ・パーティション化されています。この問合せの条件の1つはtime_id
に関する2つの述語です。それらがOR
演算子で1つにまとめられています。このOR
述語を基にsales
表のパーティションに対してプルーニングが行われ、sales
表とcosts
表の単一結合が実行されます。実行計画は次のようになります。
-------------------------------------------------------------------------------------------------- | Id| Operation | Name |Rows |Bytes |TmpSp|Cost(%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 4 | 200 | | 3556 (14)| 00:00:43| | | | 1| SORT ORDER BY | | 4 | 200 | | 3556 (14)| 00:00:43| | | |* 2| HASH JOIN | | 4 | 200 | | 3555 (14)| 00:00:43| | | | 3| TABLE ACCESS FULL |PROMOTIONS| 503 | 16599| | 16 (0)| 00:00:01| | | | 4| VIEW | | 4 | 68 | | 3538 (14)| 00:00:43| | | | 5| HASH GROUP BY | | 4 | 164 | | 3538 (14)| 00:00:43| | | | 6| PARTITION RANGE OR | | 314K| 12M| | 3321 (9)| 00:00:40|KEY(OR)|KEY(OR)| |* 7| HASH JOIN | | 314K| 12M| 440K| 3321 (9)| 00:00:40| | | |* 8| TABLE ACCESS FULL| SALES | 402K| 7467K| | 400 (39)| 00:00:05|KEY(OR)|KEY(OR)| | 9| TABLE ACCESS FULL | COSTS |82112| 1764K| | 77 (24)| 00:00:01|KEY(OR)|KEY(OR)| -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("S"."PROMO_ID"="P"."PROMO_ID") 7 - access("SALES"."TIME_ID"="COSTS"."TIME_ID" AND "SALES"."PROD_ID"="COSTS"."PROD_ID") 8 - filter("SALES"."TIME_ID"<=TO_DATE('1999-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "SALES"."TIME_ID">=TO_DATE('1998-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR "SALES"."TIME_ID">=TO_DATE('2001-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "SALES"."TIME_ID"<=TO_DATE('2002-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
ある列が複数の列でレンジ・パーティション化されている場合は、さらにプルーニングが実行されます。ある特定の述語に対して、決して合致しないと判断できるパーティションはスキップされます。これにより、複数列に対する範囲述語がある場合またはパーティション列のプリフィックスに対する述語が存在しない場合のパフォーマンスが最適化されます。
パーティション・プルーニングのヒントは、「パーティション・プルーニングのヒント」を参照してください。