DBA Hub

📋Steps in this guide1/6

Logical Partition Change Tracking (LPCT) for Materialized Views in Oracle Database 23ai/26ai

In Oracle database 23ai/26ai Logical Partition Change Tracking (LPCT) allows materialized view staleness to be tracked at the level of a logical partition.

oracle 23configurationintermediate
by OracleDba
14 views
1

Setup

Create a test user and connect to it. Create and populate and 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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

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;
2

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
3

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. The view displays information about the logical partitions, including the freshness of the data in it. The procedure allow us to check the refresh status of the materialized view. Amongst other capabilities, it displays the logical partition tracking (LPT) capabilities. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
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);

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>

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>
4

Tracking Staleness with Logical Partition Change Tracking (LPCT)

We insert some new data into the and tables. The materialized view is marked as "NEEDS_COMPILE". We check the view to see the impact of the data change. The table has a new logical partition created, which is marked as stale. The logical partition of the 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
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;

select staleness
from   user_mviews
where  mview_name = 'SALES_MV';

STALENESS
-------------------
NEEDS_COMPILE

SQL>

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>

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>
5

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 procedure in the package. When we do this we see the logical partitions are marked as fresh.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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>
6

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: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!