My Oracle Support Banner

PCT Refresh Issues Delete Where it Should Issue Truncate (Doc ID 733673.1)

Last updated on NOVEMBER 26, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.4 [Release 10.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Partition Change Tracking based refresh is using delete when it can use truncate partition to efficiently maintain the materialized view, although the materialized view satisfies the requirements for supporting PCT.

The following is an example based on <user_name>.<table_name1> table:

 

drop table test;
drop materialized view <mview_name>;
create table test
partition by range (deptno)
(
partition P1 values less than (20),
partition P2 values less than (30),
partition P3 values less than (40),
partition P4 values less than (MAXVALUE)
)
as
select * from <user_name>.<table_name1>;

drop table d_1;
create table d_1 as select * from <user_name>.<table_name2>;
drop table d_2;
create table d_2 as select * from <user_name>.<table_name2>;
drop table d_3;
create table d_3 as select * from <user_name>.<table_name1> where deptno <20;

create materialized view <mview_name>
partition by range (deptno)
(
partition P1 values less than (20),
partition P2 values less than (30),
partition P3 values less than (40),
partition P4 values less than (MAXVALUE)
)
refresh on demand
as
select empno , d_2.deptno, count(*) cnt
from test e, d_1, d_2
where e.deptno = d_1.deptno and d_1.dname = d_2.dname
GROUP BY empno, d_2.deptno;

Alter table test truncate partition p1;
Alter table test exchange partition p1 with table d_3;

execute dbms_mview.refresh('<mview_name>', 'P', atomic_refresh=> FALSE);

 

Changes

Enable the following events for debugging the refresh process:

alter session set events '10046 trace name context forever, level 12';
alter session set events '10979 trace name context forever';

execute dbms_mview.refresh('<mview_name>', 'P', atomic_refresh=> FALSE);


In the trace we can find the following:

Value of _mv_refresh_costing : rule
Refresh method PCT - DEL/TRUNC :
REFRESH STATEMENT
/* MV_REFRESH (DEL) */ DELETE FROM "TEST"."<mview_name>" WHERE ( ( ( (SYS_OP_MAP_NONNULL("DEPTNO")) IN ( SELECT SYS_OP_MAP_NONNULL("D_2"."DEPTNO") FROM "D_1" "D_1", "D_2" "D_2" WHERE ("D_1"."DNAME"="D_2"."DNAME") AND ("D_1"."DEPTNO" < 20) )) ) )
REFRESH STATEMENT
/* MV_REFRESH (INS) */ INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "TEST"."<mview_name>"SELECT /*+ X_DYN_PRUNE */ "E"."EMPNO" , "D_2"."DEPTNO" , COUNT(*) FROM "D_2" "D_2","D_1" "D_1","TEST" "E" WHERE ("E"."DEPTNO"="D_1"."DEPTNO" AND "D_1"."DNAME"="D_2"."DNAME") AND ( ( ( (SYS_OP_MAP_NONNULL("D_2"."DEPTNO")) IN ( SELECT SYS_OP_MAP_NONNULL("D_2"."DEPTNO") FROM "D_1" "D_1", "D_2" "D_2" WHERE ("D_1"."DNAME"="D_2"."DNAME") AND ("D_1"."DEPTNO" < 20) )) ) )GROUP BY "E"."EMPNO","D_2"."DEPTNO"
Refresh method PCT - TRUNC :
REFRESH STATEMENT
ALTER TABLE "TEST"."<mview_name>" TRUNCATE PARTITION P1 UPDATE GLOBAL INDEXES
REFRESH STATEMENT
/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO "TEST"."<mview_name>" PARTITION ( P1 ) SELECT /*+ X_DYN_PRUNE */ "E"."EMPNO" , "D_2"."DEPTNO" P0, COUNT(*) FROM "D_2" "D_2","D_1" "D_1","TEST" "E" WHERE ("E"."DEPTNO"="D_1"."DEPTNO" AND "D_1"."DNAME"="D_2"."DNAME") AND ( ( ( ( ( ( "D_2"."DEPTNO" < 20 ) ) ) ) ) )GROUP BY "E"."EMPNO","D_2"."DEPTNO"
Refresh method picked PCT - DEL/TRUNC

The PCT- based refresh picks up DELETE based refresh instead of TRUNCATE based refresh.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.