PCT Refresh Does Delete Instead of Truncate After Split of Empty Partition on Base Tables (Doc ID 1322100.1)

Last updated on OCTOBER 18, 2012

Applies to:

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

Symptoms

A Partition Change Tracking (PCT) refresh never completed after the top, empty partitions on the partitioned base tables were SPLIT.

Before the split of the empty partition on the base tables, the fast refresh completed in seconds.   After the split, the fast refresh ran for days.  A complete refresh with atomic_refresh => false completed in 4.5 hours (about  48 million rows). The longops on the PCT refresh showed that it was doing a delete instead of a truncate of the partitions on the mview. A count from the delete statement shows that about 22 million rows out of the 48 million were being deleted.

The following settings should have given priority to the choosing of a PCT truncate, but they had no effect:
alter session set "_mv_refresh_costing"=COST ;
alter session set "_mv_refresh_costing" = RULE_FA_PT_PD_CO;

The SQL taking the longest time was this one.  Note the "DELETE" and call to DBMS_MVIEW.PMARKER:

/* MV_REFRESH (DEL) */ DELETE FROM "OWNER"."MVIEW_NAME_MV" WHERE ( (
DBMS_MVIEW.PMARKER ("CP_ROWID") IN (563052, 563052) ) OR (
DBMS_MVIEW.PMARKER ("CL_ROWID") IN (407237, 563944, 563944) ) OR (
DBMS_MVIEW.PMARKER ("TPMI_ROWID") IN (336742, 563420, 563420) ) OR (
DBMS_MVIEW.PMARKER ("IF_ROWID") IN (245219, 335988, 563224, 563224) ) OR (
DBMS_MVIEW.PMARKER ("DF_ROWID") IN (335780, 563712, 563712) ) OR (
DBMS_MVIEW.PMARKER ("P_ROWID") IN (336224, 564328, 564328) ) OR (
DBMS_MVIEW.PMARKER ("SLI_ROWID") IN (336468, 563686, 563686) ) OR ( (568 <=
"CUSTOMER_SID" AND "CUSTOMER_SID" < 569) OR (865 <= "CUSTOMER_SID" OR
"CUSTOMER_SID" IS NULL ) ) )


The output of dbms_mview.explain_mview showed that the materialized view was PCT-refreshable:

CAPABILITY_NAME P REL_TEXT MSGTXT
------------------------------ - ------------------------------ ------------------------------
PCT Y
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE N
PCT_TABLE Y Base_table1
PCT_TABLE Y Base_table2
PCT_TABLE Y Base_table3
PCT_TABLE Y Base_table4
PCT_TABLE Y Base_table5
PCT_TABLE Y Base_table6
PCT_TABLE N Base_table7 relation is not a partitioned
PCT_TABLE Y Base_table8
PCT_TABLE Y Base_table9
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT Y



Note that for a PCT refresh to do a truncate, atomic_refresh => false must be specified in the dbms_mview.refresh command.

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