6 データ・ウェアハウス環境でのパーティション化の使用
パーティション化機能により、データ・ウェアハウス環境でのパフォーマンスを改善できます。
この章では、データ・アクセスを大幅に強化し、アプリケーション・パフォーマンス全体を向上させるパーティション化機能について説明します。パーティション化による改善は、データ・ウェアハウス環境で見られるような、何百万もの行や何GBものデータを含む表や索引にアクセスするアプリケーションでは特に効果があります。データ・ウェアハウスには大規模な表が含まれることが多く、これらの大規模な表を管理し、これらの表で最適な問合せパフォーマンスを得るためのテクニックが必要です。
この章の構成は、次のとおりです。
6.1 データ・ウェアハウスについて
データ・ウェアハウスとは、トランザクション処理ではなく、問合せおよび分析用に設計されたリレーショナル・データベースです。
データ・ウェアハウスには、通常、トランザクション・データから導出された履歴データが含まれますが、別のソースからのデータを含めることもできます。データ・ウェアハウスは分析処理をトランザクション処理の負荷と分離し、組織で、様々なソースからのデータを整理統合できるようにします。
リレーショナル・データベースの他に、データ・ウェアハウス環境には、ETL(抽出、変換、ロード)ソリューション、分析処理やデータ・マイニングの機能、クライアント分析ツール、およびその他のアプリケーション(データ収集およびビジネス・ユーザーへのデータ配布のプロセスを管理する)が含まれます。
6.2 スケーラビリティ
パーティション化により、データベース・オブジェクトが小さく分割され、管理しやすい小さなオブジェクトへのアクセスが可能になり、データ・ウェアハウスの拡張が推進されます。小さなオブジェクトに直接アクセスすることにより、次のようなデータ・ウェアハウスのスケーラビリティ要件に対処できます。
この項では、次の項目について説明します。
6.2.1 データベース・サイズの拡大
大きいデータベース・オブジェクトを小さく分割する機能によって、大規模データベースの効率的な管理が透過的に簡略化されます。
個々のパーティションやサブパーティションを指定して操作することで、大きいデータベース・オブジェクトを管理できます。パーティション化されたオブジェクトの次の利点について考慮してください。
-
大きなサイズのデータベースを管理する際にも、バックアップおよびリカバリを小さな単位で実行できます。
-
データベース・オブジェクトの一部を圧縮ストレージに配置することができます(それ以外の部分は圧縮しません)。
-
パーティション化により、データを様々なストレージ層で透過的に保持することができ、大容量データを格納するコストを節減できます。詳細は、「時間ベース情報の管理およびメンテナンス」を参照してください。
6.2.2 個々の表の拡大(表の行数の増大)
大きな表のスキャンは小さな表のスキャンに比べて時間がかかります。パーティション表に対する問合せでは、表全体のサイズに比べて小さい、1つ以上のパーティションがアクセスされます。
また、問合せは、索引についてのパーティション絞込みを利用できます。索引の一部をディスクから読み取るには、索引全体を読み取るよりも時間がかかりません。ローカル・パーティション索引のようにパーティション化戦略が表と同じ構造の索引は、パーティション単位でアクセスおよびメンテナンスを行うことができます。
問合せ、DMLおよびDDL文の処理速度を上げるためにパラレル実行を使用すると、個別のパーティションに個々のデータセットが存在するメリットが活用されます。個々のパラレル実行サーバーは、パーティション境界ごとに、独自のデータセットを処理します。
6.2.3 システムに問合せを行うユーザーの増加
パーティション化されている状態では、ユーザーは孤立した小さなデータセットを問い合せる可能性が高くなります。
その結果、すべてのユーザーが1つの大きなデータセットを問い合せる場合よりも、データベースは結果を速く返せるようになります。データ競合が発生する可能性が低くなります。
6.3 パフォーマンス
適切なパフォーマンスは、データ・ウェアハウスが成功するために重要です。
データベースに対して実行する分析は、問合せがアクセスするデータが大容量で表のサイズが数TBに及ぶ場合でも、適度な時間で返されることが必要です。パーティション化によってデータ・アクセスやアプリケーション処理の速度が向上します。これによって、ハードウェア・コストもかかりすぎない優れたデータ・ウェアハウスが実現します。
この項では、次の項目について説明します。
6.3.1 パーティション・プルーニング
パーティション・プルーニングは、データ・ウェアハウスの重要なパフォーマンス機能です。
パーティション・プルーニングでは、オプティマイザによりSQL文のFROM
句とWHERE
句が分析され、パーティション・アクセス・リストを構築するときに不要なパーティションが削除されます。結果として、Oracle Databaseは、SQL文に関連するパーティションに限定して処理を実行できるようになります。
パーティション・プルーニングを行うと、ディスクから取得するデータ容量が大幅に削減され、処理時間が短縮します。このため、問合せのパフォーマンスが向上し、リソース使用率が最適化されます。
この項では、次の項目について説明します。
パーティション・プルーニングの詳細および静的パーティション・プルーニングと動的パーティション・プルーニングの違いは、「可用性、管理性およびパフォーマンスのためのパーティション化」を参照してください。
6.3.1.1 基本的なパーティション・プルーニング技法
オプティマイザでプルーニングに使用される述語のタイプには様々なものがあります。
パーティション・プルーニングに最もよく使用される述語のタイプは、等価、範囲およびIN
リストの3つです。例として次の問合せを考えてみます。
SELECT SUM(amount_sold) day_sales FROM sales WHERE time_id = TO_DATE('02-JAN-1998', 'DD-MON-YYYY');
sales
のパーティション列に対する等価述語が含まれるので、この問合せでは1つの述語にプルーニングされ、次の実行計画に反映されます。
----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows| Bytes | Cost (%CPU)| Time |Pstart| Pstop | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 21 (100) | | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | | 2 | PARTITION RANGE SINGLE | | 485 | 6305 | 21 (10) | 00:00:01 | 5 | 5 | | * 3 | TABLE ACCESS FULL | SALES | 485 | 6305 | 21 (10) | 00:00:01 | 5 | 5 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("TIME_ID"=TO_DATE('1998-01-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
同様に、time_id
列に対して範囲述語またはIN
リスト述語があれば、オプティマイザを使用して一連のパーティションにプルーニングされます。使用できる述語のタイプは、パーティション化のタイプにより決まります。範囲述語は、ハッシュ・パーティション表でのプルーニングには使用できませんが、その他のすべてのパーティション化のプルーニングには使用できます。ただし、リスト・パーティション表では、範囲述語は、連続したパーティションのセットへはマッピングされません。等価述語およびIN
リスト述語は、すべてのパーティション化方法でプルーニングできます。
6.3.1.2 高度なパーティション・プルーニング技法
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;
この問合せでは、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'))
ある列が複数の列でレンジ・パーティション化されている場合は、さらにプルーニングが実行されます。ある特定の述語に対して、決して合致しないと判断できるパーティションはスキップされます。これにより、複数列に対する範囲述語がある場合またはパーティション列のプリフィックスに対する述語が存在しない場合のパフォーマンスが最適化されます。
パーティション・プルーニングのヒントは、「パーティション・プルーニングのヒント」を参照してください。
6.3.2 パーティション・ワイズ結合
パーティション・ワイズ結合では、結合がパラレルで実行されるときにパラレル実行サーバー間で交換されるデータ量が最小限に抑えられ、問合せのレスポンス時間が短縮されます。この方法により、レスポンス時間は大幅に短縮され、CPUとメモリー・リソースの使用率が改善されます。
パーティション・ワイズ結合では、フル(完全)またはパーシャル(部分)を選択できます。どちらの種類の結合を使用するかはOracle Databaseによって判別されます。
この項では、次の項目について説明します。
6.3.2.1 フル・パーティション・ワイズ結合
フル・パーティション・ワイズ結合が行われるのは、同じキーでともにパーティション化された2つの表が問合せで結合される場合です。
同等のパーティション化は、表のパーティション・レベル、サブパーティション・レベル、あるいはパーティションとサブパーティションを組み合せたレベルで可能です。参照パーティション化は、同等のパーティション化を保証する簡単な方法です。フル・パーティション・ワイズ結合は、シリアルでもパラレルでも実行できます。
パーティション・ワイズ結合の詳細は、「可用性、管理性およびパフォーマンスのためのパーティション化」を参照してください。
次の例は、orders
表とorder_items
表のフル・パーティション・ワイズ結合を示します。order_items
表が参照パーティション化されています。
CREATE TABLE orders ( order_id NUMBER(12) NOT NULL , order_date DATE NOT NULL , order_mode VARCHAR2(8) , order_status VARCHAR2(1) , CONSTRAINT orders_pk PRIMARY KEY (order_id) ) PARTITION BY RANGE (order_date) ( PARTITION p_before_jan_2006 VALUES LESS THAN (TO_DATE('01-JAN-2006','dd-MON-yyyy')) , PARTITION p_2006_jan VALUES LESS THAN (TO_DATE('01-FEB-2006','dd-MON-yyyy')) , PARTITION p_2006_feb VALUES LESS THAN (TO_DATE('01-MAR-2006','dd-MON-yyyy')) , PARTITION p_2006_mar VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')) , PARTITION p_2006_apr VALUES LESS THAN (TO_DATE('01-MAY-2006','dd-MON-yyyy')) , PARTITION p_2006_may VALUES LESS THAN (TO_DATE('01-JUN-2006','dd-MON-yyyy')) , PARTITION p_2006_jun VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) , PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('01-AUG-2006','dd-MON-yyyy')) , PARTITION p_2006_aug VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy')) , PARTITION p_2006_sep VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')) , PARTITION p_2006_oct VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy')) , PARTITION p_2006_nov VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy')) , PARTITION p_2006_dec VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')) ) PARALLEL; CREATE TABLE order_items ( order_id NUMBER(12) NOT NULL , product_id NUMBER NOT NULL , quantity NUMBER NOT NULL , sales_amount NUMBER NOT NULL , CONSTRAINT order_items_orders_fk FOREIGN KEY (order_id) REFERENCES orders(order_id) ) PARTITION BY REFERENCE (order_items_orders_fk) PARALLEL;
典型的なデータ・ウェアハウス問合せでは、大容量のデータがスキャンされます。基礎となっている実行計画では、列Rows
、Bytes
、Cost (%CPU)
、Time
およびTQ
が削除されます。
EXPLAIN PLAN FOR SELECT o.order_date , sum(oi.sales_amount) sum_sales FROM orders o , order_items oi WHERE o.order_id = oi.order_id AND o.order_date BETWEEN TO_DATE('01-FEB-2006','DD-MON-YYYY') AND TO_DATE('31-MAY-2006','DD-MON-YYYY') GROUP BY o.order_id , o.order_date ORDER BY o.order_date; --------------------------------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | | | P->S | QC (ORDER) | | 3 | SORT GROUP BY | | | | PCWP | | | 4 | PX RECEIVE | | | | PCWP | | | 5 | PX SEND RANGE | :TQ10000 | | | P->P | RANGE | | 6 | SORT GROUP BY | | | | PCWP | | | 7 | PX PARTITION RANGE ITERATOR| | 3 | 6 | PCWC | | |* 8 | HASH JOIN | | | | PCWP | | |* 9 | TABLE ACCESS FULL | ORDERS | 3 | 6 | PCWP | | | 10 | TABLE ACCESS FULL | ORDER_ITEMS | 3 | 6 | PCWP | | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 8 - access("O"."ORDER_ID"="OI"."ORDER_ID") 9 - filter("O"."ORDER_DATE"<=TO_DATE(' 2006-05-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
6.3.2.2 パーシャル・パーティション・ワイズ結合
Oracle Databaseでは、パラレル時のみパーシャル・パーティション・ワイズ結合が実行できます。
フル・パーティション・ワイズ結合と異なり、パーシャル・パーティション・ワイズ結合では、両方の表ではなく、一方の表のみを結合キーでパーティション化する必要があります。パーティション化された表は、参照表と呼ばれます。もう一方の表は、パーティション化してもしなくてもかまいません。パーシャル・パーティション・ワイズ結合は、フル・パーティション・ワイズ結合よりも一般的です。
パーシャル・パーティション・ワイズ結合を実行するために、データベースによって、参照表のパーティション化に基づいて、もう一方の表が動的にパーティション化または再パーティション化されます。もう一方の表が再パーティション化された後は、フル・パーティション・ワイズ結合と同様に実行されます。
次の例は、データ・ウェアハウスの一般的なシナリオでの、通話明細レコードの表cdrs
を示します。この表は、時間隔ハッシュ・パーティション化されています。
CREATE TABLE cdrs ( id NUMBER , cust_id NUMBER , from_number VARCHAR2(20) , to_number VARCHAR2(20) , date_of_call DATE , distance VARCHAR2(1) , call_duration_in_s NUMBER(4) ) PARTITION BY RANGE(date_of_call) INTERVAL (NUMTODSINTERVAL(1,'DAY')) SUBPARTITION BY HASH(cust_id) SUBPARTITIONS 16 (PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2005','dd-MON-yyyy'))) PARALLEL;
cdrs
表は、非パーティション表callers
とcust_id
列によって結合され、通話時間の長い顧客順に並べられます。
EXPLAIN PLAN FOR SELECT c.cust_id , c.cust_last_name , c.cust_first_name , AVG(call_duration_in_s) , COUNT(1) , DENSE_RANK() OVER (ORDER BY (AVG(call_duration_in_s) * COUNT(1)) DESC) ranking FROM callers c , cdrs cdr WHERE cdr.cust_id = c.cust_id AND cdr.date_of_call BETWEEN TO_DATE('01-JAN-2006','dd-MON-yyyy') AND TO_DATE('31-DEC-2006','dd-MON-yyyy') GROUP BY c.cust_id , c.cust_last_name , c.cust_first_name ORDER BY ranking;
この実行計画はパーシャル・パーティション・ワイズ結合を示しています。計画では、列Rows
、Bytes
、Cost (%CPU)
、Time
およびTQ
が削除されます。
-------------------------------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | | 1 | WINDOW NOSORT | | | | | | | 2 | PX COORDINATOR | | | | | | | 3 | PX SEND QC (ORDER) | :TQ10002 | | | P->S | QC (ORDER) | | 4 | SORT ORDER BY | | | | PCWP | | | 5 | PX RECEIVE | | | | PCWP | | | 6 | PX SEND RANGE | :TQ10001 | | | P->P | RANGE | | 7 | HASH GROUP BY | | | | PCWP | | |* 8 | HASH JOIN | | | | PCWP | | | 9 | PART JOIN FILTER CREATE | :BF0000 | | | PCWP | | | 10 | BUFFER SORT | | | | PCWC | | | 11 | PX RECEIVE | | | | PCWP | | | 12 | PX SEND PARTITION (KEY) | :TQ10000 | | | S->P | PART (KEY) | | 13 | TABLE ACCESS FULL | CALLERS | | | | | | 14 | PX PARTITION RANGE ITERATOR| | 367 | 731 | PCWC | | | 15 | PX PARTITION HASH ALL | | 1 | 16 | PCWC | | |* 16 | TABLE ACCESS FULL | CDRS | 5857 | 11696 | PCWP | | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 8 - access("CDR"."CUST_ID"="C"."CUST_ID") 16 - filter("CDR"."DATE_OF_CALL">=TO_DATE(' 2006-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CDR"."DATE_OF_CALL"<=TO_DATE(' 2006-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
6.3.2.3 パーティション・ワイズ結合のメリット
6.3.2.3.1 通信オーバーヘッドの削減
パーティション・ワイズ結合では、パラレルで実行する場合の通信オーバーヘッドが削減されます。
この削減は、デフォルトでは、パラレル実行サーバー・セットが結合操作をパラレル実行するときに、各表を結合列に関して非結合行のサブセットとして再分配する必要があるため発生します。これらの非結合行のサブセットは、1つのパラレル実行サーバーによって、組単位で結合されます。
パーティション・ワイズ結合では、2つの表が結合列でパーティション化されているため、パーティションの再分配を回避できます。この機能により、各パラレル実行サーバーが、一致するパーティションの組を結合できます。パラレル実行によるパフォーマンス向上は、ノード間パラレル実行を行うOracle Real Application Clusters構成ではさらに顕著になります。
パーティション・ワイズ結合により、インターコネクト・トラフィックが大幅に削減されます。この機能の使用は、Oracle Real Application Clustersを使用する大規模な意思決定支援(DSS)構成に欠かせません。現在、超並列処理(MPP)や対称型マルチプロセッシング(SMP)クラスタなど、ほとんどのOracle Real Application Clustersプラットフォームでは、その処理能力に比べてインターコネクト帯域幅が制限されています。インターコネクト帯域幅は、ディスク帯域幅と同等であることが理想的ですが、このような例はまれです。そのため、Oracle Real Application Clustersでのほとんどの結合操作では、パーティション・ワイズ結合がパラレル実行されない場合にインターコネクトの待ち時間が長くなります。
6.3.2.3.2 メモリー要件の削減
パーティション・ワイズ結合は、同じ表のデータセット全体で同様の結合操作を行う場合に比べて、メモリー所要量が少なくなります。
シリアル結合では、一致するパーティション1組ずつに対して結合が実行されます。データがパーティション間で均等に分散されている場合、メモリー要件はパーティションの数で分割され、パラレル・サーバー間のデータの分散に偏りはありません。
パラレル結合の場合、メモリー要件は、パラレルで結合されるパーティションの組数によって異なります。たとえば、並列度が20でパーティションの数が100の場合、2つのパーティションの結合が20ずつ同時に実行されるため、必要なメモリー量は5分の1になります。パーティション・ワイズ結合でメモリーの必要量が減ることは、パフォーマンスに直接的な好影響があります。たとえば、結合では、ハッシュ結合の作成フェーズ中にブロックがディスクに書き込まれる必要がなくなります。
6.3.2.4 パラレル・パーティション・ワイズ結合のパフォーマンスに関する考慮点
オプティマイザは、パーティション・ワイズ結合を使用するかどうかを決定する場合に、メリットとデメリットを比較します。
オプティマイザは、次に基づいてパーティション・ワイズ結合を使用するかどうかを選択します。
-
レンジ・パーティション化の場合、パーティション・サイズが異なると、データの偏りによって応答時間が長くなることがあります。つまり、パラレル実行サーバーによっては、他のサーバーより結合完了までの時間が長くなります。ハッシュ・(サブ)パーティション化を使用して、パーティション・ワイズ結合を使用可能にすることをお薦めします。パーティションの数が2の累乗であれば、ハッシュ・パーティション化によって偏りが発生する可能性が低く抑えられるためです。偏りの発生を最小限にするために、ハッシュ・パーティション化キーが一意であることが理想的です。
-
パーティション・ワイズ結合に使用されるパーティションの数は、可能であれば問合せサーバーの数の倍数にします。たとえば、並列度が16の場合は、パーティションの数を16、32または64にします。パーティションの数が半端な場合、一部のパラレル実行サーバーの使用率が低くなります。たとえば、17組の均等に分散されたパーティションがある場合、1組のみが最後の結合で処理され、そのときに他の組は待機する必要があります。これは、実行開始時には、各パラレル実行サーバーが別のパーティションの組に対して動作するためです。この最初のフェーズ後には、1組のみが残ります。そのため、1つのパラレル実行サーバーがこの残った組を結合し、その間、他のすべてのパラレル実行サーバーはアイドル状態になります。
パラレル結合によって、リモートI/O処理が発生する場合があります。たとえば、MPP構成で実行されるOracle Real Application Clusters環境では、一致するパーティションの組が同じノードに存在しない場合、パーティション・ワイズ結合を行うために、リモートI/Oのための追加のノード間通信が必要になります。これは、結合が実行されるノードに、少なくとも1つのパーティションを転送する必要があるためです。この場合は、パーティション・ワイズ結合を使用するより、データを明示的に再配置する方が適しています。
6.3.3 索引とパーティション索引
索引は表と関連付けられているオプション構造で、これにより表に対するSQL文をより迅速に実行することができます。
表スキャンは多くのデータ・ウェアハウスでごく一般的ですが、多くの場合、索引を使用することで問合せの速度がさらに速くなることがあります。
Bツリー索引とビットマップ索引はどちらも、パーティション表のローカル索引として作成できます。この場合、索引は表のパーティション化戦略を継承します。Bツリー索引は、パーティション表または非パーティション表のグローバル・パーティション索引として作成することもできます。
この項では、次の項目について説明します。
パーティション索引の詳細は、「可用性、管理性およびパフォーマンスのためのパーティション化」を参照してください。
6.3.3.1 ローカル・パーティション索引
ローカル索引の場合、特定の索引パーティションのキーはすべて、基礎となる表の1つのパーティションに格納されている行のみを参照します。
ローカル索引は、基礎となる表と同一レベルでパーティション化されます。基礎となる表と同じ列で索引をパーティション化し、同じ数のパーティションまたはサブパーティションを作成して、基礎となる表の対応するパーティションと同じパーティション・バウンドを設定します。
また、Oracle Databaseは、基礎となる表のパーティションが追加、削除、マージまたは分割されたり、ハッシュ・パーティションやサブパーティションが追加または結合されたりした場合は、索引のパーティション化を自動的にメンテナンスします。これにより、索引のパーティションが表と同一レベルに保たれます。
データ・ウェアハウス・アプリケーションでは、ローカル非同一キー索引によってパフォーマンスを向上させることができます。これは、索引キーに基づくレンジ問合せによって、多数の索引パーティションをパラレルにスキャンできるためです。次の例では、ローカルBツリー索引がパーティション表customers_dw
に作成されます。
CREATE INDEX cust_last_name_ix ON customers_dw(last_name) LOCAL PARALLEL NOLOGGING ;
ビットマップ索引では、カーディナリティが低い列を非常に効率よく格納できる方法が使用されます。ビットマップ索引はデータ・ウェアハウス環境で使用され、特にスター・スキーマを実装しているデータ・ウェアハウスで広く使用されています。1つのスター・スキーマは、中心の大きなファクト表と、ファクト表のデータについて説明する複数の小さなディメンション表で構成されます。
たとえば、ディメンション表customers
、products
、promotions
、times
およびchannels
で説明されている、ファクト表であるsales
表を考察します。ビットマップ索引では、スター・スキーマ(またはスターに似ているスキーマ)に対する高速の問合せ取得を最適化する、スター型変換を使用できます。
ファクト表の外部キー列は、ビットマップ索引の理想的な候補です。一般的に、全行数に比べて固有の値が少ないためです。ファクト表は、レンジ・パーティション化、またはレンジと他の方法を組み合せてパーティション化されていることが多いですが、その場合は、ローカル・ビットマップ索引を作成する必要があります。パーティション表のグローバル・ビットマップ索引はサポートされません。
次の例では、ローカル・パーティション・ビットマップ索引がsales
表に作成されます。
CREATE BITMAP INDEX prod_id_ix ON sales(prod_id) LOCAL PARALLEL NOLOGGING;
関連項目:
スター型変換の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください
6.3.3.2 非パーティション索引
非パーティション索引は、非パーティション表にもパーティション表にも作成できます。
非パーティション索引はデータ・ウェアハウス環境で非パーティション表で主に使用され、一般的に一意の制約のステータスをデータ・ウェアハウス環境で施行する必要がある場合に一意性を施行します。パーティション表で非パーティション・グローバル索引を使用すると、主キーまたは一意キーを施行できます。非パーティション(グローバル)索引は、パーティション化キーに含まれない1つの列または複数の列に対する等価述語またはIN
リストに基づいて、通常ごく少数の行を取得する問合せで役立ちます。このような場合、一致するすべての行を検出するために、1つの索引をスキャンする方が多数の索引パーティションをスキャンするよりも速く行えます。
パーティション列以外の列の一意索引はグローバルにする必要があります。これは、キーにパーティション化キーが含まれていない一意ローカル非同一キー索引はサポートされていないためです。制御されたデータ・ロード処理や一意制約の施行によるパフォーマンス・コストのために、データ・ウェアハウスでは必ずしも一意キーが施行されるとはかぎりません。グローバル索引は、数十億の行を含む表では非常に大きくなることがあります。
次の例では、グローバル一意索引がsales
表に作成されます。
CREATE UNIQUE INDEX sales_unique_ix ON sales(cust_id, prod_id, promo_id, channel_id, time_id) PARALLEL NOLOGGING;
この索引からメリットを得る問合せはほとんどありません。データ・ロード・ウィンドウが非常に限られているシステムでは、この索引の作成や管理を行わないことを検討する必要があります。
6.3.3.3 グローバル・パーティション索引
グローバル・パーティション索引は、非パーティション表にもパーティション表にも作成できます。
グローバル・パーティション索引では、特定の索引パーティションのキーが、複数の基礎となる表のパーティションまたはサブパーティションに格納されている行を参照する場合があります。グローバル索引ではレンジ・パーティション化またはハッシュ・パーティション化が可能ですが、定義するパーティション表はどのタイプでもかまいません。
グローバル索引は、GLOBAL
属性を指定することで作成されます。データベース管理者は、グローバル索引の作成時に最初のパーティション化を定義して、それ以降はパーティション化のメンテナンスを行う必要があります。索引パーティションは、必要に応じてマージまたは分割できます。
グローバル索引が役立つのは、少数の行を取得するために索引を介した表へのアクセス・パスを使用する問合せがある場合です。索引をパーティション化することで、問合せの大半に対して索引の大部分を排除できます。パーティション表では、パーティション・プルーニングの実行のために含めるべき列または複数の列に、表のパーティション化キーが含まれない場合に、グローバル・パーティション索引を検討してください。
次の例では、グローバル・ハッシュ・パーティション索引がsales
表に作成されます。
CREATE INDEX cust_id_prod_id_global_ix ON sales(cust_id,prod_id) GLOBAL PARTITION BY HASH (cust_id) ( PARTITION p1 TABLESPACE tbs1 , PARTITION p2 TABLESPACE tbs2 , PARTITION p3 TABLESPACE tbs3 , PARTITION p4 TABLESPACE tbs4 ) PARALLEL NOLOGGING;
6.3.4 マテリアライズド・ビューとパーティション化
パフォーマンス向上のためにデータ・ウェアハウスで使用されている1つのテクニックに、サマリーの作成があります。これは、特殊なタイプの集計ビューであり、問合せを実行する前に、効率が悪い結合および集計操作を事前に計算し、その結果をデータベース内の表に格納することで、問合せ実行時間を短縮します。
たとえば、サマリー表が、地域別および製品別の売上合計を含むように作成できます。
サマリー、すなわちこのマニュアルやデータ・ウェアハウスに関するマニュアルで説明されている集計は、マテリアライズド・ビューと呼ばれるスキーマ・オブジェクトを使用してOracle Databaseに作成されます。データ・ウェアハウスではマテリアライズド・ビューによって問合せパフォーマンスが改善されます。
データベースでは、マテリアライズド・ビューに対する透過的なリライトがサポートされます。したがって、マテリアライズド・ビューの計算済結果を利用するために元の問合せを変更する必要はありません。問合せを実行するかわりに、データベースは計算済結果を1つ以上のマテリアライズド・ビューから取得し、必要であればデータに対してその他の操作を実行し、問合せ結果を返します。
関連項目:
データ・ウェアハウスおよびマテリアライズド・ビューの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください
6.3.4.1 パーティション・マテリアライズド・ビュー
マテリアライズド・ビューの基礎となる記憶域は表構造です。表をパーティション化するのと同様にマテリアライズド・ビューをパーティション化できます。
マテリアライズド・ビューに対して実行するようにデータベースが問合せをリライトしたとき、その問合せは、表に対して直接実行される問合せと同じパフォーマンス特性を得られます。リライトされた問合せでは、マテリアライズド・ビューのパーティションを排除できます。問合せ結果を取得するために、表または他のマテリアライズド・ビューとの結合が必要な場合は、リライトされた問合せで、パーティション・ワイズ結合を利用できます。
例6-1は、売上結果を国レベルで集計する圧縮パーティション・マテリアライズド・ビューを作成する方法を示します。このマテリアライズド・ビューは、売上数を国レベル(またはさらに大きな地区や地域のレベル)で要約する問合せを利用します。
例6-1 圧縮パーティション・マテリアライズド・ビュー
CREATE MATERIALIZED VIEW country_sales PARTITION BY HASH (country_id) PARTITIONS 16 COMPRESS FOR OLTP PARALLEL NOLOGGING ENABLE QUERY REWRITE AS SELECT co.country_id , co.country_name , co.country_subregion , co.country_region , sum(sa.quantity_sold) country_quantity_sold , sum(sa.amount_sold) country_amount_sold FROM sales sa , customers cu , countries co WHERE sa.cust_id = cu.cust_id AND cu.country_id = co.country_id GROUP BY co.country_id , co.country_name , co.country_subregion , co.country_region;
関連項目:
データ・ウェアハウスおよびマテリアライズド・ビューの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください
6.4 管理性
データ・ウェアハウスには履歴データが格納されます。データ・ウェアハウスの重要な部分はデータのロードとパージです。パーティション化は、データ・ウェアハウスでのデータ管理に役立つ高機能テクノロジです。
この項では、次の項目について説明します。
関連項目:
パーティション索引、交換および表の統計の収集および管理の詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください
6.4.1 パーティション交換ロード
パーティション交換ロード(PEL)を使用してパーティションを追加できます。
PELを使用するときは、1つのパーティションとまったく同じに見える別の表を作成し、索引や制約(存在する場合)も同じものを含めます。コンポジット・パーティション表を使用する場合、この表は、コンポジット・パーティション表のサブパーティション化戦略と一致するパーティション化戦略を使用する必要があります。次に、既存の表パーティションをこの表と交換します。データ・ロード・シナリオでは、この表にデータをロードしておきます。表のユーザーの問合せに影響しないように、表に索引を構築して制約を実装します。次にPELを実行します。これはデータのロードに比べると非常に影響の少ないトランザクションです。日単位のレンジ・パーティション戦略と毎日のロードの組合せは、データ・ウェアハウス環境では一般的です。
次の例は、sales
表のパーティション交換ロードを示します。
ALTER TABLE sales ADD PARTITION p_sales_jun_2007 VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy')); CREATE TABLE sales_jun_2007 COMPRESS FOR OLTP AS SELECT * FROM sales WHERE 1=0;
次に、表sales_jun_2007
に2007年6月の売上数を移入して、sales
表に実装されていたのと同じビットマップ索引と制約を作成します。
CREATE BITMAP INDEX time_id_jun_2007_bix ON sales_jun_2007(time_id) NOLOGGING; CREATE BITMAP INDEX cust_id_jun_2007_bix ON sales_jun_2007(cust_id) NOLOGGING; CREATE BITMAP INDEX prod_id_jun_2007_bix ON sales_jun_2007(prod_id) NOLOGGING; CREATE BITMAP INDEX promo_id_jun_2007_bix ON sales_jun_2007(promo_id) NOLOGGING; CREATE BITMAP INDEX channel_id_jun_2007_bix ON sales_jun_2007(channel_id) NOLOGGING; ALTER TABLE sales_jun_2007 ADD CONSTRAINT prod_id_fk FOREIGN KEY (prod_id) REFERENCES products(prod_id); ALTER TABLE sales_jun_2007 ADD CONSTRAINT cust_id_fk FOREIGN KEY (cust_id) REFERENCES customers(cust_id); ALTER TABLE sales_jun_2007 ADD CONSTRAINT promo_id_fk FOREIGN KEY (promo_id) REFERENCES promotions(promo_id); ALTER TABLE sales_jun_2007 ADD CONSTRAINT time_id_fk FOREIGN KEY (time_id) REFERENCES times(time_id); ALTER TABLE sales_jun_2007 ADD CONSTRAINT channel_id_fk FOREIGN KEY (channel_id) REFERENCES channels(channel_id);
次に、パーティションを交換します。
ALTER TABLE sales EXCHANGE PARTITION p_sales_jun_2007 WITH TABLE sales_jun_2007 INCLUDING INDEXES;
パーティション交換ロードの詳細は、「パーティションの管理」を参照してください。
6.4.2 パーティション化と索引
パーティション・メンテナンス操作の実行が最も簡単にできるのはローカル索引です。
ローカル索引は、パーティション管理が行われるときにグローバル索引を無効にしません。ローカル索引を別の表の同じ索引と交換するとき、索引パーティションが無効にならないように、PEL文でINCLUDING INDEXES
を使用します。PELの場合は、ロードの際にグローバル索引を更新できます。PEL文でUPDATE GLOBAL INDEXES
拡張機能を使用します。索引の更新が必要な場合、PELの実行時間が長くなります。
6.4.3 表からのデータの削除
通常、データ・ウェアハウスでは一定の時間枠のデータが保存されます。たとえば、3年分の履歴データが格納されます。
パーティション化を行うと、非常に簡単に表からデータをパージすることができます。データをパージするにはDROP PARTITION
文または TRUNCATE PARTITION
文を使用します。一般的な戦略として、表のデータをアンロードするためにパーティション交換ロードを使用する方法、パーティションを空の表と置き換えてからパーティションを削除する方法もあります。交換した別の表は、空にしたり削除したりする前にアーカイブします。
削除または切捨て操作によって、グローバル索引またはグローバル・パーティション索引が無効になる場合があります。ローカル索引は有効なまま変化しません。ローカル索引パーティションは、表パーティションを削除するときに削除されます。
次の例は、sales
表のsales_1995
パーティションを削除する方法を示します。
ALTER TABLE sales DROP PARTITION sales_1995 UPDATE GLOBAL INDEXES PARALLEL;
6.4.4 パーティション化とデータの圧縮
パーティション表のデータは、パーティション単位で圧縮することができます。
圧縮データの使用は、データが頻繁に変更されない場合に最も効果的です。一般的なデータ・ウェアハウスのシナリオでは、データが古くなるにつれてデータの変更は減ります。あるいは、シナリオによってはデータの挿入のみが行われます。パーティション管理機能を使用して、パーティション単位でデータを圧縮できます。Oracle Databaseでは、圧縮データに対してすべてのDML操作を行えますが、データの変更は圧縮されていない表で実行する方が効率的です。
圧縮を有効化するパーティションの変更は、パーティションにこれから挿入されるデータのみに適用されます。パーティションの既存のデータを圧縮するには、パーティションを移動する必要があります。圧縮の有効化とパーティションの移動は、1つの操作で実行できます。
ビットマップ索引のあるパーティション表に対して表の圧縮を使用するには、最初に圧縮属性を導入する前に、次の処理を行う必要があります。
-
ビットマップ索引を
UNUSABLE
としてマークします。 -
圧縮属性を設定します。
-
索引を再作成します。
圧縮パーティションを、まったく圧縮されていない既存のパーティション表に含める場合は、圧縮パーティションを追加する前に、既存のビットマップ索引をすべて削除するか、UNUSABLE
としてマークする必要があります。これは、パーティションにデータが含まれるかどうかにかかわらず実行する必要があります。また、表の1つ以上のパーティションを圧縮する操作とも無関係です。これは、Bツリー索引のみを含むパーティション表には適用されません。
次の例は、sales
表のSALES_1995
パーティションを圧縮する方法を示します。
ALTER TABLE sales MOVE PARTITION sales_1995 COMPRESS FOR OLTP PARALLEL NOLOGGING;
表またはパーティションがディスクに占める領域が減ると、I/Oに制約がある環境では、大きな表スキャンのパフォーマンスが改善されることがあります。