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.
- About Logical Partition Change Tracking
Logical Partition Change Tracking (LPCT) tracks the staleness of materialized views. - Using Logical Partition Change Tracking
Logical Partition Change Tracking (LPCT) logically partitions a table using a specified key column and method. - Example: Logical Partition Change Tracking
Shows the steps to use Logical Partition Change Tracking (LPCT) using a Materialized View that contains joins and aggregates.
About Logical Partition Change Tracking
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.
Parent topic: Logical Partition Change Tracking and Materialized Views
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.
- Creating Logical Partitions - BNF
Describes the syntax to create BNF logical partitions. - Choosing the Logical Partition Key Column
The logical partitioning key is specified to define the boundaries of each logical partition. - 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. - Rewrite with Materialized Views Using Logical Partition Change Tracking
Using Logical Partition Change Tracking (LPCT), Oracle knows that a materialized view isSTALE
with respect to some logical partitions of the base table, butFRESH
with respect to other portions. - 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 refreshSTALE
subsets of a materialized view, eliminating costly complete refresh or log-based fast refresh. - Logical Partition Change Tracking – Data Dictionary Views
Describes the data dictionary views to find information about logical partitions.
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
andINTERVAL
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
Parent topic: Using Logical Partition Change Tracking
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.
Parent topic: Using Logical Partition Change Tracking
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.
All types of Materialized Views are supported for LPCT.
Parent topic: Using Logical Partition Change Tracking
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.
Parent topic: Using Logical Partition Change Tracking
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.
Parent topic: Using Logical Partition Change Tracking
Logical Partition Change Tracking – Data Dictionary Views
Describes the data dictionary views to find 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.
Parent topic: Using Logical Partition Change Tracking
Example: Logical Partition Change Tracking
Shows the steps to use Logical Partition Change Tracking (LPCT) using a Materialized View that contains joins and aggregates.
- Create base tables with logical change partitions.
- Create a materialized view on top of tables with logical partition change tracking.
- Observe the impact of DMLs on your materialized view.
- Leverage LPCT for incremental refresh.
Parent topic: Logical Partition Change Tracking and Materialized Views