Im looking for a help here. We are trying to implement archive strategy to off load unused data from a base table into another archive table. we are trying to leverage partition exchange feature with oracle to help with this task. Pls see below details table structure and constraint.
Approach :
Base Table ( Partition by Range) => Partition Exchange to Non Partition Temp Table ==> Partition Exchange from Temp table to Archive partition table.
The base table has Global Index ( PK) and couple of Local indexes. also we replicated same index structure across temp and archive tables.
Also, the base table is child table and has RI enabled.
Now, when we execute Partition exchange process, its taking longer on each partition till it reaches to Archive Table.
our understanding is, the partition exchange is quicker but trying to understand why its taking longer. not sure the indexes and RI is causing for slowness.
Looking for some advise on tunning the processs.
Base to Temp Exchange:
ALTER TABLE <Base Table>
EXCHANGE PARTITION <Partition Name> WITH TABLE <Temp Table>
Temp to Archive Exchange:
ALTER TABLE <Archive Table>
EXCHANGE PARTITION <Parttiopn name> WITH TABLE <Temp Table>