8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23c » Here

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.

Related articles.

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).

For more information see:

Hope this helps. Regards Tim...

Back to the Top.