My Oracle Support Banner

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

Last updated on NOVEMBER 07, 2023

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:

 


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.