Oracle Cloud Infrastructureドキュメント

論理パーティション・チェンジ・トラッキングおよびマテリアライズド・ビュー

Autonomous Databaseの論理パーティション・チェンジ・トラッキング(LPCT)メタデータ・フレームワークおよび論理パーティション・チェンジ・トラッキングを使用したクエリー・リライトに関する情報について説明します。

親トピック: 経験豊富なOracle Databaseユーザー用のAutonomous Database

論理パーティション・チェンジ・トラッキングについて

論理パーティション変更トラッキング(LPCT)は、マテリアライズド・ビューの失効を追跡します。

論理パーティション・チェンジ・トラッキングを使用すると、実表に論理パーティションを作成できます。マテリアライズド・ビュー・ログを使用しないで、またはマテリアライズド・ビューで使用されている表のパーティション化を必要としないで、個々の論理パーティションに対する実表の失効を評価します。

マテリアライズド・ビューの1つ以上の依存実表が更新されると、マテリアライズド・ビューはSTALEになり、デフォルトの強制整合性モードでクエリー・リライトに使用できなくなります。

論理パーティション・チェンジ・トラッキング(LPCT)は、マテリアライズド・ビューの実表のユーザー指定の論理パーティション化情報を活用して、リフレッシュとリライトの両方の目的で失効したデータをより詳細なパーティション・レベルで追跡する機能を提供します。クラシック・パーティショニング・チェンジ・トラッキング(PCT)は表の物理パーティション化に依存しますが、LPCTは物理的にパーティション化される表に依存しません。LPCTはパーティション表と非パーティション表の両方で使用できます。

論理パーティション・チェンジ・トラッキング・メカニズムでは、他のサブセットがSTALEであっても、マテリアライズド・ビューのFRESHサブセット(パーティション)が使用されます。マテリアライズド・ビューで事前に計算された結果を使用する頻度が高くなるため、ユーザー問合せのレスポンス時間が短縮されます。マテリアライズド・ビューの使用性を高める以外に、PCTおよびLPCTでは、マテリアライズド・ビュー・ログを必要とせずにマテリアライズド・ビューを増分リフレッシュできます。リフレッシュはON DEMANDまたはON COMMITの両方にできます。

パーティション化チェンジ・トラッキング(PCT)と同様に、論理パーティション・チェンジ・トラッキング(LPCT)は実表に関連付けられ、定義された論理パーティション境界に従って、実表のデータ変更の影響を受けるマテリアライズド・ビューの行を正確に識別できます。

詳細は、「拡張マテリアライズド・ビュー」の「拡張マテリアライズド・ビュー」を参照してください。

論理パーティション・チェンジ・トラッキングの使用

論理パーティション・チェンジ・トラッキング(LPCT)は、指定されたキー列およびメソッドを使用して表を論理的にパーティション化します。

論理パーティション・チェンジ・トラッキング作成構文は、物理パーティションに似ています。表の作成の一部として作成する必要がある物理パーティションとは異なり、LPCTは表の作成とそのシェイプに関係なく自由に指定できるため、要件への対応が柔軟になります。LPCTの作成はメタデータのみです。

親トピック: 論理パーティション・チェンジ・トラッキングおよびマテリアライズド・ビュー

論理パーティションの作成- BNF

BNF論理パーティションを作成するための構文について説明します。

BNF論理パーティションを作成する構文は次のとおりです。

CREATE LOGICAL PARTITION TRACKING ON table_name
    PARTITION BY RANGE (partition_key)
    INTERVAL (interval_clause)
    (partition_specification);
  • RANGEおよびINTERVAL論理パーティション化メソッドのみがサポートされています。
  • 1つの論理パーティション・キー列のみがサポートされます。
  • パーティション・キー列は、次のデータ型にできます。
    • NUMBER
    • DATE
    • CHAR
    • VARCHAR
    • VARCHAR2
    • TIMESTAMP
    • TIMESTAMP WITH TIME ZONE

論理パーティション・キー列の選択

論理パーティション・キーは、各論理パーティションの境界を定義するために指定されます。

論理パーティション・キーは物理的ではありません。つまり、キー範囲に属する表の行は、個別の物理パーティションに分離されません。表は、非パーティションにすることも、論理パーティション・キーとは異なるキーでパーティション化することもできます。論理パーティション・キーは自由に選択でき、パーティション境界を柔軟に選択できます。

論理パーティション・チェンジ・トラッキング(LPCT)キー列を選択するには、クラスタ化された列、つまり、問合せフィルタ述語で頻繁に参照される列値でソートされるデータに近い列を検討します。クラスタ列の場合、データ・ロード時に影響を受ける論理パーティションが少なくなる可能性があります。つまり、STALE論理パーティションをリフレッシュする必要が少なくなり、リライトに使用する準備が整ったFRESH論理パーティションが多くなります。表がすでにパーティション化されている場合は、パーティション・キー列以外の別の列を使用してLPCTを作成することをお薦めします。LPCTは、パーティショニング・チェンジ・トラッキング(PCT)と同様の利点を提供し、同じ列でデータ・トラッキングを行う場合、結合された利点は最大化されません。

論理パーティション・チェンジ・トラッキングを使用したマテリアライズド・ビューの鮮度

論理パーティション・チェンジ・トラッキング(LPCT)の失効トラッキング・メカニズムは、データ変更のたびに、指定された論理パーティション・キーおよびパーティション化方法に基づいて、変更統計を自動的に記録および統合します。

隣接する変更データは、論理パーティションにグループ化されます。物理パーティション境界に関連付けられているパーティショニング・チェンジ・トラッキング(PCT)とは異なり、LPCTスキームでは、実表に適用されたDMLによって生じるデータ変更を柔軟に管理およびグループ化できます。

従来のDMLおよびダイレクト・ロードでは、LPCTは失効の追跡にPCTが使用するアルゴリズムと同じアルゴリズムを採用しています。クエリー・リライト時に、LPCTはリライト包含の計算にPCTが使用するのと同じアルゴリズムを採用します。

キー範囲を使用して表を論理的にパーティション化する場合、マテリアライズド・ビューに論理パーティション・キーが含まれていると、その表に定義されているマテリアライズド・ビューは、失効トラッキング、リフレッシュおよびクエリー・リライトにLPCTを使用する資格があります。

ノート

LPCTでは、すべてのタイプのマテリアライズド・ビューがサポートされています。

論理パーティション・チェンジ・トラッキングを使用したマテリアライズド・ビューでのリライト

Oracleでは、論理パーティション・チェンジ・トラッキング(LPCT)を使用して、実表の一部の論理パーティションに関してマテリアライズド・ビューがSTALEであることを認識していますが、他の部分についてはFRESHです。

実表のよりきめ細かいデータ失効情報があると、関連付けられたマテリアライズド・ビューはLPCTリライトにより頻繁に使用されます。

Oracleは、マテリアライズド・ビューのFRESHサブセットを透過的に識別し、クエリー・リライトに使用して、QUERY_REWRITE_INTEGRITY = ENFORCED |TRUSTEDの場合に実表の複雑な問合せに応答します。

マテリアライズド・ビュー行がこれらの論理パーティションに関して部分的にFRESHである場合、マテリアライズド・ビュー(FRESH論理パーティション)を使用して部分的に問合せに応答し、実表(STALE論理パーティション)を部分的に使用するために、部分リライトが行われることがあります。

論理パーティション・チェンジ・トラッキングを使用したマテリアライズド・ビューのリフレッシュ

論理パーティション・チェンジ・トラッキング(LPCT)リフレッシュは、よりきめ細かいデータ失効を使用して実装し、マテリアライズド・ビューのSTALEサブセットを増分リフレッシュすることで、コストのかかる完全リフレッシュまたはログベースの高速リフレッシュを排除できます。

LPCTリフレッシュが指定されている場合、STALE論理パーティションが識別され、ターゲット・リフレッシュ操作はそれらの論理パーティションに対してのみ実行されます。

論理パーティション・チェンジ・トラッキングを使用してリフレッシュを起動するには、リフレッシュ・メソッドとして‘L’または‘l’(論理)を指定します。

たとえば: execute DBMS_MVIEW.REFRESH(<materialized_view_name>,’L’);

REFRESH FORCEが指定されている場合、FASTリフレッシュが選択されて実行され、それ以外の場合はCOMPLETEリフレッシュが実行されます。マテリアライズド・ビューFORCEのリフレッシュ中、LPCTリフレッシュの優先度は、パーティション化チェンジ・トラッキング(PCT)リフレッシュと同じです。

論理パーティション・チェンジ・トラッキング- データ・ディクショナリ・ビュー

論理パーティションに関する情報を検索するためのデータ・ディクショナリ・ビューについて説明します。

次のデータ・ディクショナリ・ビューを問い合せて、論理パーティションに関する情報を取得します。

  • ALL_MVIEW_DETAIL_LOGICAL_PARTITION: このビューには、現行のユーザーがアクセスできる、LPCTディテール論理パーティションに関するマテリアライズド・ビューのフレッシュネス情報が表示されます。詳細は、ALL_MVIEW_DETAIL_PARTITIONを参照してください。

  • DBA_MVIEW_DETAIL_ LOGICAL_PARTITION: LPCTディテール論理パーティションに関する、データベース内のすべてのマテリアライズド・ビューのフレッシュネス情報を表示します。詳細は、DBA_MVIEW_DETAIL_PARTITIONを参照してください。

  • USER_MVIEW_DETAIL_ LOGICAL_PARTITION: 現在のユーザーが所有するすべてのマテリアライズド・ビューについて、LPCTディテール論理パーティションに関するフレッシュネス情報を表示します。詳細は、USER_MVIEW_DETAIL_PARTITIONを参照してください。

例: 論理パーティション・チェンジ・トラッキング

結合および集計を含むマテリアライズド・ビューを使用して論理パーティション・チェンジ・トラッキング(LPCT)を使用するステップを示します。

  1. 論理変更パーティションを含む実表を作成します。
    1. MYSALES表を作成します。
      CREATE TABLE mysales ( time_id DATE, prod_id NUMBER, cust_id NUMBER, channel_id CHAR(1), promo_id NUMBER(6), quantity_sold
      NUMBER(3), amount_sold NUMBER(10,2))    
      PARTITION BY LIST (prod_id) 
      (PARTITION p1 VALUES (1,2,3),
      PARTITION p2 VALUES (4,5,6),
      PARTITION p3 VALUES (7,8,9),
      PARTITION p4 VALUES(DEFAULT));

      これにより、MYSALES表が作成されます。

    2. MYSALES表にレコードを挿入します。
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2007-06-05','yyyy-mm-dd'), 1, 2088, 189.98);
INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2007-07-05','yyyy-mm-dd'), 2, 1354, 12.99);
INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2007-09-05','yyyy-mm-dd'), 5, 2088, 189.98);
INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2007-10-05','yyyy-mm-dd'), 18, 2088, 42);
COMMIT;
      これにより、MYSALES表に移入されます。
    3. MYSALES表の論理パーティション・トラッキングを作成します。
      CREATE LOGICAL PARTITION TRACKING ON mysales
  PARTITION BY RANGE (time_id)
  INTERVAL(NUMTOYMINTERVAL(2, 'YEAR'))
  (
   PARTITION p0 VALUES LESS THAN (TO_DATE('7-15-2005', 'MM-DD-YYYY')),
   PARTITION p1 VALUES LESS THAN (TO_DATE('7-15-2007', 'MM-DD-YYYY'))
  );
      これにより、TIME_IDをキーとして使用して、MYSALES表の論理パーティション・トラッキングが作成されます。
    4. MYCUSTOMERS表を作成します。
      CREATE TABLE mycustomers (cust_id NUMBER, age NUMBER, gender CHAR(1), address VARCHAR(100));
      これにより、MYCUSTOMERS表が作成されます。
    5. MYCUSTOMERS表にレコードを挿入します。
      INSERT INTO mycustomers(cust_id, age, gender) VALUES (2088, 35, 'F');
INSERT INTO mycustomers(cust_id, age, gender) VALUES (1234, 54, 'M');
INSERT INTO mycustomers(cust_id, age, gender) VALUES (1354, 17, 'F');
INSERT INTO mycustomers(cust_id, age, gender) VALUES (6666, 15, 'F');
COMMIT;
      これにより、MYCUSTOMERS表に移入されます。
    6. MYCUSTOMERS表の論理パーティション・トラッキングを作成します。
      CREATE LOGICAL PARTITION TRACKING ON mycustomers
 PARTITION BY RANGE (age) INTERVAL (20.5)
 (PARTITION m0 values less than (20));
      これにより、AGEをキーとして使用して、MYSALES表の論理パーティション・トラッキングが作成されます。
  2. 論理パーティション・チェンジ・トラッキングを使用して、表の上にマテリアライズド・ビューを作成します。
    1. MYSALES表およびMYCUSTOMERS表にマテリアライズド・ビューを作成します。
      CREATE MATERIALIZED VIEW sales_age_time
 REFRESH FAST
 ENABLE QUERY REWRITE
 AS SELECT SUM(s.amount_sold) amount_total, c.age, s.time_id
 FROM mysales s, mycustomers c
 WHERE s.cust_id = c.cust_id
 GROUP BY c.age, s.time_id;
      これにより、SALES_AGE_TIMEマテリアライズド・ビューが作成されます。
    2. DBA_MVIEW_DETAIL_LOGICAL_PARTITIONデータ・ディクショナリ・ビューを問い合せます。
      SELECT mview_name, DETAILOBJ_NAME, DETAIL_LOGICAL_PARTITION_NAME LPARTNAME,
 DETAIL_LOGICAL_PARTITION_NUMBER LPART#, FRESHNESS
 FROM DBA_MVIEW_DETAIL_LOGICAL_PARTITION 
 WHERE mview_name = 'SALES_AGE_TIME' 
 ORDER BY 1,2,3;

      これは、次の出力を示しています。

      
MVIEW_NAME        DETAILOBJ_NAME     LPARTNAME      LPART#      FRESHNESS
---------------   ---------------    ----------     ----------  ---------
SALES_AGE_TIME    MYCUSTOMERS        M0             0 	      FRESH
SALES_AGE_TIME    MYSALES            P0             0 	      FRESH
SALES_AGE_TIME    MYSALES            P1             1 	      FRESH
    3. EXPLAIN_MVIEWを使用して、論理パーティション関連のリフレッシュおよびリライト機能を評価します。
      EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('sales_age_time');

SELECT CAPABILITY_NAME, RELATED_TEXT, POSSIBLE
FROM MV_CAPABILITIES_TABLE
WHERE MVNAME = 'SALES_AGE_TIME' AND CAPABILITY_NAME LIKE '%LPT%'
ORDER BY 1, 2;

      これは、次の出力を示しています。

      
CAPABILITY_NAME               RELATED_TEXT                 POSSIBLE
-------------------------     -------------------------    –--------------
LPT                                                         Y
LPT_TABLE                     MYCUSTOMERS                   Y
LPT_TABLE                     MYSALES                       Y
LPT_TABLE_REWRITE             MYCUSTOMERS                   Y
LPT_TABLE_REWRITE             MYSALES                       Y
REWRITE_LPT                                                 Y
REFRESH_FAST_LPT                                            Y
  3. DMLがマテリアライズド・ビューに与える影響を確認します。
    1. MYSALES表に新しい論理パーティションを導入します。
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES
      (TO_DATE('2019-02-05','yyyy-mm-dd'), 99, 2108, 33);
      これにより、MYSALES表に新しいパーティション(パーティション#6)が導入されます。
    2. MYSALES表に新しい論理パーティションを導入します。
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2019-02-05','yyyy-mm-dd'), 99, 2108, 33);
      これにより、MYSALES表に新しいパーティション(パーティション#6)が導入されます。
    3. MYCUSTOMERS表に新しい論理パーティションを導入します。
      INSERT INTO mycustomers(cust_id, age, gender) VALUES (1399, 80, 'F');
      これにより、MYCUSTOMERS表に新しいパーティション(パーティション#3)が導入されます。
    4. MYSALES表に新しい論理パーティションを導入します。
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2019-02-09','yyyy-mm-dd'), 99, 1997, 79.9);
      これにより、MYSALES表に新しいパーティション(パーティション#7)が導入されます。
    5. MYSALES表に新しい論理パーティションを導入します。
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2010-02-09','yyyy-mm-dd'), 110, 1997, 108.98);
COMMIT;
      これにより、MYSALES表に新しいパーティション(パーティション#2)が導入されます。
    6. DBA_MVIEW_DETAIL_LOGICAL_PARTITIONデータ・ディクショナリ・ビューを問い合せます。
      SELECT mview_name, DETAILOBJ_NAME, DETAIL_LOGICAL_PARTITION_NAME LPARTNAME,
 DETAIL_LOGICAL_PARTITION_NUMBER LPART#, FRESHNESS
 FROM DBA_MVIEW_DETAIL_LOGICAL_PARTITION 
 WHERE mview_name = 'SALES_AGE_TIME' 
 ORDER BY 1,2,3;

      次に、次の出力を示します。

      
MVIEW_NAME        DETAILOBJ_NAME     LPARTNAME      LPART#      FRESHNESS
---------------   ---------------    ----------     ----------  ---------
SALES_AGE_TIME    MYCUSTOMERS        M0             0             FRESH
SALES_AGE_TIME    MYCUSTOMERS        SYS_88904P3    3             STALE
SALES_AGE_TIME    MYSALES            P1             0             FRESH
SALES_AGE_TIME    MYSALES            P1             1             FRESH
SALES_AGE_TIME    MYSALES            SYS_88899P3    2             STALE
SALES_AGE_TIME    MYSALES            SYS_88899P7    6             STALE
    7. MYSALESのlpart #1のサブセットに対してLPCTリライトを実行し、MYCUSTOMERSテーブルに対してlpart #0を実行します。
      DELETE FROM rewrite_table;
DECLARE    
 stmt varchar2(2000) := q'#select sum(s.amount_sold) amount_total,
 c.age, s.time_id
 FROM mysales s, mycustomers c
 WHERE s.cust_id = c.cust_id
 AND s.time_id < TO_DATE ('07-07-2007', 'MM-DD-YYYY')
 AND c.age < 18
 GROUP BY c.age, s.time_id#';
BEGIN 
 dbms_mview.explain_rewrite (stmt,'sales_age_time');
END;
/
SELECT mv_name, sequence, pass, message FROM rewrite_table;
    8. REWRITE_TABLEを問い合せて、リライトを確認します。
      SELECT mv_name, sequence, pass, message FROM rewrite_table;
    9. 次の問合せを実行します。
      SELECT SUM(s.amount_sold) amount_total,
 c.age, s.time_id
 FROM mysales s, mycustomers c
 WHERE s.cust_id = c.cust_id
 AND s.time_id < TO_DATE ('07-07-2007', 'MM-DD-YYYY')
 AND c.age < 18
 GROUP BY c.age, s.time_id;
    10. 上記の問合せの実行計画を表示して、リライトを確認します。
      SELECT * FROM TABLE(dbms_xplan.display_cursor);
      PLAN_TABLE_OUTPUT
__________________________________________________
SQL_ID  ampuzk8tbp6df, child number 0
-------------------------------------
SELECT SUM(s.amount_sold) amount_total,
c.age, s.time_id
FROM mysales s, mycustomers c
WHERE s.cust_id = c.cust_id
AND s.time_id < TO_DATE ('07-07-2007', 'MM-DD-YYYY')
AND c.age < 18
GROUP BY c.age, s.time_id;

Plan hash
        value: 3902795718 
        -----------------------------------------------------------------------------------------------
        | Id  | Operation                    | Name           | Rows  | Bytes | Cost
        (%CPU)| Time     
        |-----------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT             |                |      
          |       |     2 (100)|         
        ||*  1 |  MAT_VIEW
        REWRITE ACCESS FULL| SALES_AGE_TIME |     1 |    35 |    
          2   (0)| 00:00:01 
        |----------------------------------------------------------------------------------------------- 
        Predicate Information (identified by operation id):
        ---------------------------------------------------    
        1 - filter(("SALES_AGE_TIME"."TIME_ID"<TO_DATE('2007-07-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SALES_AGE_TIME"."AGE"<18)) 
Note
-----   
   - dynamic statistics used: dynamic sampling (level=2)  
26 rows selected.
  4. LPCTを使用して増分リフレッシュを行います。
    1. 次のコードを実行して、LPCTリフレッシュを実行します。
      EXECUTE DBMS_MVIEW.REFRESH('SALES_AGE_TIME', 'L');
    2. 次の問合せを使用してリフレッシュを確認します。
      SELECT mview_name, DETAILOBJ_NAME, DETAIL_LOGICAL_PARTITION_NAME LPARTNAME, DETAIL_LOGICAL_PARTITION_NUMBER LPART#, FRESHNESS 
 FROM DBA_MVIEW_DETAIL_LOGICAL_PARTITION
 WHERE mview_name = 'SALES_AGE_TIME' 
 ORDER BY 1,2,3;