How to stop swapping in EXCHANGE PARTITION [message #684644] |
Fri, 16 July 2021 12:36 |
|
gopalMisra
Messages: 7 Registered: July 2021
|
Junior Member |
|
|
Hello Seniors, I am facing one issue. I wrote one partition archival process. My partition are range interval.
Simple logic - I am following simple logic.
Source table(partitioned) => intermediate table(non partitioned) => archive table(partitioned).
Issue: when i exchanged partition for "01-jan-2021", Data moved to Archive table. After few months source table again filled with records for "01-jan-2021". Now when exchange will run- it will swap data between archive table and intermediate table.
for example: if source table/partition has 5 records. Archive table already has 30 record. when i execute my code -
Alter table <source> exchange partition <Src5> with table <intermediate>;
Alter table <archive> exchange partition <arc5> with table <intermediate>;
Now it is showing 30 records in intermediate table and 5 records in archive table. It means they exchanged records in between.
expectation: is that whole set of data will move to archival table. 30 (existing) + 5 (new) = 35 records.
Please suggest how we can handle this situation.
|
|
|
|
|
|
Re: How to stop swapping in EXCHANGE PARTITION [message #684659 is a reply to message #684658] |
Sun, 18 July 2021 02:07 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I think OP already have the archive table partition and his problem is that it is not empty:
active intermediate archive
t0 5 0 30 nb rows
t1 <===> operation
t2 0 5 30
t3 <===>
t4 0 30 5
What OP wants is that at t4 there is:
active intermediate archive
t4 0 0 35 nb rows
But, as Solomon implies, it is strange to archive an active partition.
Best solution(s) depends on the actual use(s) of the table and its archive.
What are the requirements on availability of the active table (see Solomon remark about global indexes)?
What are the number of interested rows?
And what about the updates in the active table? Do they exist or not?
...
[Updated on: Sun, 18 July 2021 02:09] Report message to a moderator
|
|
|
|
|
|