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

Last updated on JUNE 08, 2015

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 to 10.2.0.4 [Release 10.2]
Information in this document applies to any platform.

Symptoms

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 SCOTT.EMP table:

 

drop table test;
drop materialized view mview_2;
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 scott.emp;

drop table d_1;
create table d_1 as select * from scott.dept;
drop table d_2;
create table d_2 as select * from scott.dept;
drop table d_3;
create table d_3 as select * from scott.emp where deptno <20;

create materialized view mview_2
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_2', '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_2', '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_2" 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_2"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_2" TRUNCATE PARTITION P1 UPDATE GLOBAL INDEXES
REFRESH STATEMENT
/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO "TEST"."MVIEW_2" 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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms