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論理パーティション化メソッドのみがサポートされています。
  • 単一の論理パーティション・キー列のみがサポートされています。
  • パーティション・キー列のデータ型は、次のとおりです。
    • NUMBER
    • DATE
    • CHAR
    • VARCHAR
    • VARCHAR2
    • TIMESTAMP
    • TIMESTAMP WITH TIME ZONE

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

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

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

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

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

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

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

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

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

ノート

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

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

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

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

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

マテリアライズド・ビュー行がそれらの論理パーティションに関して部分的に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のサブセットおよびMYCUSTOMERS表のlpart #0に対してLPCTリライトを実行します。
      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;