Hi,
We have a purging process which does :
1) create temp table
2) create indexes on temp table matching local indexes on main table
3) exchange partition to be dropped
4) drop partition
5) drop temp table
Monitoring process from gv$session_longops I see 3 "stages" at least :
Table scan
Sort/Merge
Sort output
Since it takes too long, specially for the last "stage" even hours for just one partition of 100m rows, I did some research and added :
a) disable PK keep index
between 2 and 3 above, according to doc :
http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm
but that didn't work, am I missing anything ?
The simplest table has no "incoming" FKs and just a global PK ( not local index ) which is NOVALIDATE and disabled
exchange command is :
alter table ANALYZER_ATTRIBUTE_INSTANCE exchange partition SYS_P2339 with table ZZPARTDROP including indexes without validation update indexes;
db version is 11.2.0.3.0, 6 nodes rac.
Thanks,
Bertran Saragusti.