8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Logical Partition Change Tracking (LPCT) for Materialized Views in Oracle Database 23c
In Oracle database 23c Logical Partition Change Tracking (LPCT) allows materialized view staleness to be tracked at the level of a logical partition.
- Setup
- Create Logical Partitions
- Create Materialized View
- Tracking Staleness with Logical Partition Change Tracking (LPCT)
- Refresh Using Logical Partition Change Tracking (LPCT)
- Additional Information
Related articles.
- Materialized View Enhancements in Oracle Database 23c
- Materialized Views in Oracle
- Real-Time Materialized Views in Oracle Database 12c Release 2 (12.2)
Setup
Create a test user and connect to it.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba drop user if exists testuser1 cascade; create user testuser1 identified by testuser1 quota unlimited on users; grant db_developer_role to testuser1; grant select_catalog_role to testuser1; conn testuser1/testuser1@//localhost:1521/freepdb1
Create and populate CUSTOMERS
and SALES
tables to act as the base tables for our materialized view. All sales are currently for year 2022. Notice none of the tables are partitioned.
drop table if exists sales purge; drop table if exists customers purge; create table customers ( customer_id number primary key, age number, gender varchar2(1) ); insert into customers (customer_id, age, gender) values (1, 35, 'F'), (2, 54, 'M'), (3, 17, 'F'), (4, 15, 'M'); commit; create table sales ( id number generated always as identity primary key, sale_date date, product_id number, customer_id number, sale_value number(10,2), constraint sales_cust_fk foreign key (customer_id) references customers(customer_id) ); create index sales_cust_fk_i on sales(customer_id); insert into sales (sale_date, product_id, customer_id, sale_value) select to_date('2022','yyyy'), trunc(dbms_random.value(1,10)), trunc(dbms_random.value(1,5)), round(dbms_random.value(1,200),2) from dual connect by level < 10001; commit;
Create Logical Partitions
Logical partitions are metadata, not physical partitions. They can be used with partitioned or non-partitioned tables. The partition definitions are similar to those of partitioned tables.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba create logical partition tracking on testuser1.customers partition by range (age) interval (20.5) ( partition m0 values less than (20) ); create logical partition tracking on testuser1.sales partition by range (sale_date) interval(numtoyminterval(1, 'year')) ( partition p0 values less than (to_date('01-01-2023', 'dd-mm-yyyy')), partition p1 values less than (to_date('01-01-2024', 'dd-mm-yyyy')) ); conn testuser1/testuser1@//localhost:1521/freepdb1
Create Materialized View
We create a materialized view to sum the sales by age of the customer and date of the sale. Notice we have no materialized view refresh logs, but we are still able to create the materialized view with the fast refresh option.
drop materialized view if exists sales_mv; create materialized view sales_mv refresh fast enable query rewrite as select sum(s.sale_value) value_total, c.age, s.sale_date from sales s, customers c where s.customer_id = c.customer_id group by c.age, s.sale_date; exec dbms_stats.gather_schema_stats(null);
The USER_MVIEW_DETAIL_LOGICAL_PARTITION
view displays information about the logical partitions, including the freshness of the data in it.
set linesize 100 column mview_name format a10 column detailobj_owner format a15 column detailobj_name format a15 column dlp_name format a12 column lpartname format a15 column freshness format a10 select mview_name, detailobj_owner, detailobj_name, detail_logical_partition_name as dlp_name, detail_logical_partition_number as dlp_no, freshness, last_refresh_time from user_mview_detail_logical_partition order by dlp_no; MVIEW_NAME DETAILOBJ_OWNER DETAILOBJ_NAME DLP_NAME DLP_NO FRESHNESS LAST_REFR ---------- --------------- --------------- ------------ ---------- ---------- --------- SALES_MV TESTUSER1 SALES P0 0 FRESH 05-SEP-23 SALES_MV TESTUSER1 CUSTOMERS M0 0 FRESH 05-SEP-23 SALES_MV TESTUSER1 SALES P1 1 FRESH 05-SEP-23 SQL>
The DBMS_MVIEW.EXPLAIN_MVIEW
procedure allow us to check the refresh status of the materialized view. Amongst other capabilities, it displays the logical partition tracking (LPT) capabilities.
execute dbms_mview.explain_mview ('sales_mv'); select capability_name, related_text, possible from mv_capabilities_table where mvname = 'SALES_MV' and capability_name like '%LPT%' order by 1, 2; CAPABILITY_NAME RELATED_TEXT POSSIBLE -------------------- --------------- ---------- LPT Y LPT_TABLE CUSTOMERS Y LPT_TABLE SALES Y LPT_TABLE_REWRITE CUSTOMERS Y LPT_TABLE_REWRITE SALES Y REFRESH_FAST_LPT Y REWRITE_LPT Y 7 rows selected. SQL>
We can see LPT is enabled, the materialized view supports fast refreshes using LPT, and rewrite is enabled using LPT to track staleness of logical partitions.
Tracking Staleness with Logical Partition Change Tracking (LPCT)
We insert some new data into the SALES
and CUSTOMERS
tables.
insert into customers (customer_id, age, gender) values (5, 25, 'M'); insert into sales (sale_date, product_id, customer_id, sale_value) values (to_date('2023-10-01','yyyy-mm-dd'), 5, 5, 42); commit;
The materialized view is marked as "NEEDS_COMPILE".
select staleness from user_mviews where mview_name = 'SALES_MV'; STALENESS ------------------- NEEDS_COMPILE SQL>
We check the USER_MVIEW_DETAIL_LOGICAL_PARTITION
view to see the impact of the data change.
set linesize 100 column mview_name format a10 column detailobj_owner format a15 column detailobj_name format a15 column dlp_name format a12 column lpartname format a15 column freshness format a10 select mview_name, detailobj_owner, detailobj_name, detail_logical_partition_name as dlp_name, detail_logical_partition_number as dlp_no, freshness, last_refresh_time from user_mview_detail_logical_partition order by dlp_no; MVIEW_NAME DETAILOBJ_OWNER DETAILOBJ_NAME DLP_NAME DLP_NO FRESHNESS LAST_REFR ---------- --------------- --------------- ------------ ---------- ---------- --------- SALES_MV TESTUSER1 SALES P0 0 FRESH 05-SEP-23 SALES_MV TESTUSER1 CUSTOMERS M0 0 FRESH 05-SEP-23 SALES_MV TESTUSER1 CUSTOMERS SYS_92702P1 1 STALE 05-SEP-23 SALES_MV TESTUSER1 SALES P1 1 STALE 05-SEP-23 SQL>
The CUSTOMERS
table has a new logical partition created, which is marked as stale. The P1
logical partition of the SALES
table, which references the "2023" data, is marked as stale.
Despite the apparent staleness of the materialized view, we can still use it for query rewrites provided we only touch the fresh logical partitions.
select sum(s.sale_value) value_total, c.age, s.sale_date from sales s, customers c where s.customer_id = c.customer_id and age = 35 group by c.age, s.sale_date; select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 48jg6r42js79d, child number 0 ------------------------------------- select sum(s.sale_value) value_total, c.age, s.sale_date from sales s, customers c where s.customer_id = c.customer_id and age = 35 group by c.age, s.sale_date Plan hash value: 1420257564 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | MAT_VIEW REWRITE ACCESS FULL| SALES_MV | 1 | 17 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("SALES_MV"."AGE"=35) 20 rows selected. SQL>
Refresh Using Logical Partition Change Tracking (LPCT)
We can perform a fast refresh of the materialized view using the "l" or "L" option when calling the REFRESH
procedure in the DBMS_MVIEW
package. When we do this we see the logical partitions are marked as fresh.
execute dbms_mview.refresh('SALES_MV', 'L'); select mview_name, detailobj_owner, detailobj_name, detail_logical_partition_name as dlp_name, detail_logical_partition_number as dlp_no, freshness, last_refresh_time from user_mview_detail_logical_partition order by dlp_no; MVIEW_NAME DETAILOBJ_OWNER DETAILOBJ_NAME DLP_NAME DLP_NO FRESHNESS LAST_REFR ---------- --------------- --------------- ------------ ---------- ---------- --------- SALES_MV TESTUSER1 SALES P0 0 FRESH 05-SEP-23 SALES_MV TESTUSER1 CUSTOMERS M0 0 FRESH 05-SEP-23 SALES_MV TESTUSER1 CUSTOMERS SYS_92744P1 1 FRESH 05-SEP-23 SALES_MV TESTUSER1 SALES P1 1 FRESH 05-SEP-23 SQL>
Additional Information
Here is some additional information about Logical Partition Change Tracking (LPCT).
- LPCT can be used with partitioned or non-partitioned tables.
- LPCT can be used in addition to materialized view logs.
- Since staleness is tracked at the logical partition level, it's possible to continue to use query rewrites provided the query only references data in fresh logical partitions. Without LPCT materialized views on non-partitioned tables are either completely stale or completely fresh. So LPCT gives a finer granularity.
- LPCT is more lightweight than materialized view logs, as it doesn't log each changed row. Instead it tracks change statistics at the logical partition level.
For more information see:
- About Logical Partition Change Tracking (LPCT) Refresh for Materialized Views
- Logical Partition Change Tracking and Materialized Views
- USER_MVIEW_DETAIL_LOGICAL_PARTITION
- DBMS_MVIEW
- Materialized View Enhancements in Oracle Database 23c
- Materialized Views in Oracle
- Real-Time Materialized Views in Oracle Database 12c Release 2 (12.2)
Hope this helps. Regards Tim...