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.
Example: Logical Partition Change Tracking Shows the steps to use Logical Partition Change Tracking (LPCT) using a Materialized View that contains joins and aggregates.
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.
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.
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.
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.
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.
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.
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.
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.
Create a materialized view on top of tables with logical partition change tracking.
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.
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;
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
Observe the impact of DMLs on your materialized view.
Introduce a new logical partition on the MYSALES table.
This introduces a new partition (partition #2) on the MYSALES table.
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;
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;
Query REWRITE_TABLE to verify the rewrites.
SELECT mv_name, sequence, pass, message FROM rewrite_table;
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;
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.
Leverage LPCT for incremental refresh.
Execute the following code to perform LPCT refresh.
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;