Oracle Cloud Infrastructure Documentation

Logical Partition Change Tracking and Materialized Views

Describes information about the Logical Partition Change Tracking (LPCT) metadata framework and Query Rewrite with Logical Partition Change Tracking in Autonomous Database.

Parent topic: Autonomous Database for Experienced Oracle Database Users

About Logical Partition Change Tracking

Logical Partition Change Tracking (LPCT) tracks the staleness of materialized views.

Logical Partition Change Tracking enables you to create logical partitions on base tables. It evaluates the staleness of the base tables for individual logical partitions without using a materialized view log or requiring any of the tables used in the materialized view to be partitioned.

When one or more dependent base tables of a materialized view are updated, a materialized view becomes STALE and cannot be used for query rewrite under the default enforced integrity mode.

Logical Partition Change Tracking (LPCT) provides the capability to leverage the user-supplied logical partitioning information of base tables of a materialized view for a more fine-grained, partition-level tracking of stale data for both refresh and rewrite purposes. While classical Partitioning Change Tracking (PCT) relies on the physical partitioning of tables, LPCT has no dependency on tables being physically partitioned; LPCT can be used with both partitioned and nonpartitioned tables.

Logical Partition Change Tracking mechanism makes use of the FRESH subsets (partitions) of materialized views despite other subsets being STALE. Faster response times can be achieved for user queries because pre-computed results in materialized views are used more often. Other than increasing the usability of materialized views, PCT and LPCT also allows incremental refreshing of the materialized views without the need of materialized view logs; refresh can be both ON DEMAND or ON COMMIT.

Similar to Partitioning Change Tracking (PCT), Logical Partition Change Tracking (LPCT) is associated with a base table and can accurately identify the rows in a Materialized View affected by data changes on the base table, according to the logical partition boundaries defined.

See Advanced Materialized Views Advanced Materialized Views for more information.

Using Logical Partition Change Tracking

Logical Partition Change Tracking (LPCT) logically partitions a table using a specified key column and method.

Logical Partition Change Tracking creation syntax is analogous to physical partitions. Unlike physical partitions, which must be created as part of table creation, LPCT can be freely specified independent of the table creation and its shape, allowing more flexibility to address your requirements. LPCT creation is metadata only.

Parent topic: Logical Partition Change Tracking and Materialized Views

Creating Logical Partitions - BNF

Describes the syntax to create BNF logical partitions.

Following is the syntax to create BNF logical partitions:

CREATE LOGICAL PARTITION TRACKING ON table_name
    PARTITION BY RANGE (partition_key)
    INTERVAL (interval_clause)
    (partition_specification);
  • Only RANGE and INTERVAL logical partitioning methods are supported.
  • Only a single logical partition key column is supported.
  • The partition key column can be of these data types:
    • NUMBER
    • DATE
    • CHAR
    • VARCHAR
    • VARCHAR2
    • TIMESTAMP
    • TIMESTAMP WITH TIME ZONE

Choosing the Logical Partition Key Column

The logical partitioning key is specified to define the boundaries of each logical partition.

The logical partition key is not physical, this means that table rows belonging to a key range are not segregated into a separate physical partition. The table can be non-partitioned or partitioned on a key that is different from the logical partition key. The logical partition key can be chosen freely, and partition boundaries can be made flexible.

To choose a Logical Partition Change Tracking (LPCT) key column, you can consider a clustered column, that is, a column where data is close to sorted by column value, that are frequently referenced in the query filter predicates. For a clustered column, less logical partitions are likely to be affected during data loads, this means that less STALE logical partitions need to be refreshed and more FRESH logical partitions are ready to be used for rewrites. If a table is already partitioned, it is recommended to create a LPCT using a different column other than the partition key column. LPCT offers similar benefits as Partitioning Change Tracking (PCT), and the combined benefits are not maximized if data tracking is done on the same column.

Freshness of Materialized Views Using Logical Partition Change Tracking

The Logical Partition Change Tracking (LPCT) staleness tracking mechanism automatically records and consolidates the change statistics internally based on the specified logical partition key and partitioning method during each data change.

Adjacent change data is grouped into a “logical” partition. Unlike with Partitioning Change Tracking (PCT), which is tied to physical partition bounds the LPCT scheme offers flexibility in managing and grouping the data changes resulting from DMLs applied to the base table.

During conventional DMLs and Direct-loads, LPCT adopts the same algorithm that PCT uses to track staleness. During Query rewrites, LPCT adopts the same algorithm that PCT uses to calculate rewrite containment.

When a table is logically partitioned using key ranges, a materialized view defined on the table is eligible to use LPCT for staleness tracking, refresh and query rewrite, provided that the materialized view contains the logical partition key.

Note

All types of Materialized Views are supported for LPCT.

Rewrite with Materialized Views Using Logical Partition Change Tracking

Using Logical Partition Change Tracking (LPCT), Oracle knows that a materialized view is STALE with respect to some logical partitions of the base table, but FRESH with respect to other portions.

Having the finer-grained data staleness information of the base tables, the associated materialized view would be used more frequently due to LPCT rewrite.

Oracle transparently identifies and makes use of the FRESH subset of materialized views for query rewrite to answer complicated queries of base tables when QUERY_REWRITE_INTEGRITY = ENFORCED |TRUSTED.

If the materialized view rows are partially FRESH with respect to those logical partitions, a partial rewrite might take place to answer the query partially using materialized view, that is, FRESH logical partitions, and partially using the base table, that is, the STALE logical partitions.

Refresh of Materialized Views Using Logical Partition Change Tracking

Logical Partition Change Tracking (LPCT) refresh can be implemented using the finer-grained data staleness to incrementally refresh STALE subsets of a materialized view, eliminating costly complete refresh or log-based fast refresh.

If LPCT refresh is specified, the STALE logical partitions are identified and targeted refresh operations will be performed to those logical partitions only.

To invoke refresh using logical partition change tracking you specify ‘L’ or ‘l’ (“logical”) as refresh method.

For example: execute DBMS_MVIEW.REFRESH(<materialized_view_name>,’L’);

If REFRESH FORCE is specified, a FAST refresh is chosen and performed if possible, or else it performs a COMPLETE refresh. During materialized view FORCE refresh, LPCT refresh has the same priority as Partitioning Change Tracking (PCT) refresh.

Logical Partition Change Tracking – Data Dictionary Views

Describes the data dictionary views to find information about logical partitions.

Query the following data dictionary views to retrieve information about logical partitions.

  • ALL_MVIEW_DETAIL_LOGICAL_PARTITION: This view displays the freshness information of the materialized views, with respect to an LPCT detail logical partition, accessible to the current user. See ALL_MVIEW_DETAIL_PARTITION for more information.

  • DBA_MVIEW_DETAIL_ LOGICAL_PARTITION: displays freshness information for all materialized views in the database, with respect to a LPCT detail logical partition. See DBA_MVIEW_DETAIL_PARTITION for more information.

  • USER_MVIEW_DETAIL_ LOGICAL_PARTITION: displays freshness information for all materialized views, with respect to a LPCT detail logical partition, owned by the current user. See USER_MVIEW_DETAIL_PARTITION for more information.

Example: Logical Partition Change Tracking

Shows the steps to use Logical Partition Change Tracking (LPCT) using a Materialized View that contains joins and aggregates.

  1. Create base tables with logical change partitions.
    1. Create MYSALES table.
      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));

      This creates the MYSALES table.

    2. Insert records into the MYSALES table.
      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;
      This populates the MYSALES table.
    3. Create logical partition tracking for the MYSALES table.
      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'))
  );
      This creates a logical partition tracking for the MYSALES table using the TIME_ID as key.
    4. Create MYCUSTOMERS table.
      CREATE TABLE mycustomers (cust_id NUMBER, age NUMBER, gender CHAR(1), address VARCHAR(100));
      This creates the MYCUSTOMERS table.
    5. Insert records into the MYCUSTOMERS table.
      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;
      This populates the MYCUSTOMERS table.
    6. Create logical partition tracking for the MYCUSTOMERS table.
      CREATE LOGICAL PARTITION TRACKING ON mycustomers
 PARTITION BY RANGE (age) INTERVAL (20.5)
 (PARTITION m0 values less than (20));
      This creates a logical partition tracking for the MYSALES table using the AGE as key.
  2. Create a materialized view on top of tables with logical partition change tracking.
    1. Create materialized view on MYSALES and MYCUSTOMERS tables.
      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;
      This creates the SALES_AGE_TIME materialized view.
    2. Query the DBA_MVIEW_DETAIL_LOGICAL_PARTITION data dictionary view.
      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;

      It shows the following output.

      
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. Use EXPLAIN_MVIEW to assess the logical partition related refresh and rewrite capabilities.
      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;

      It shows the following output.

      
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. Observe the impact of DMLs on your materialized view.
    1. Introduce a new logical partition on the MYSALES table.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES
      (TO_DATE('2019-02-05','yyyy-mm-dd'), 99, 2108, 33);
      This introduces a new partition (partition #6) on the MYSALES table.
    2. Introduce a new logical partition on the MYSALES table.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2019-02-05','yyyy-mm-dd'), 99, 2108, 33);
      This introduces a new partition (partition #6) on the MYSALES table.
    3. Introduce a new logical partition on the MYCUSTOMERS table.
      INSERT INTO mycustomers(cust_id, age, gender) VALUES (1399, 80, 'F');
      This introduces a new partition (partition #3) on the MYCUSTOMERS table.
    4. Introduce a new logical partition on the MYSALES table.
      INSERT INTO mysales (time_id, prod_id, cust_id, amount_sold) VALUES (TO_DATE('2019-02-09','yyyy-mm-dd'), 99, 1997, 79.9);
      This introduces a new partition (partition #7) on the MYSALES table.
    5. Introduce a new logical partition on the MYSALES table.
      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;
      This introduces a new partition (partition #2) on the MYSALES table.
    6. Query the DBA_MVIEW_DETAIL_LOGICAL_PARTITION data dictionary view.
      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;

      Now, it shows the following output.

      
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. Perform LPCT rewrite on subset of lpart #1 on the MYSALES and lpart #0 on the MYCUSTOMERS tables.
      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. Query REWRITE_TABLE to verify the rewrites.
      SELECT mv_name, sequence, pass, message FROM rewrite_table;
    9. Execute the following query.
      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. View the explain plan for the above query to verify the rewrites.
      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. Leverage LPCT for incremental refresh.
    1. Execute the following code to perform LPCT refresh.
      EXECUTE DBMS_MVIEW.REFRESH('SALES_AGE_TIME', 'L');
    2. Verify the refresh using the following query.
      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;