PCT Refresh Does Delete Instead of Truncate After Split of Empty Partition on Base Tables
(Doc ID 1322100.1)
Last updated on NOVEMBER 07, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.2 [Release 10.2 to 11.2]Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
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:
Changes
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 |