Home » SQL & PL/SQL » SQL & PL/SQL » How to stop swapping in EXCHANGE PARTITION (PLSQL 12.1.0.2.0)
How to stop swapping in EXCHANGE PARTITION [message #684644] Fri, 16 July 2021 12:36 Go to next message
gopalMisra
Messages: 2
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 #684645 is a reply to message #684644] Fri, 16 July 2021 13:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67988
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If we exchange our cars I then have yours and you have mine, it won't be an exchange if in the end you have both cars and me nothing. Smile

Exchange between intermediate and archive is not the right operation, I think the best option is to use an INSERT SELECT then TRUNCATE the intermediate table.

Re: How to stop swapping in EXCHANGE PARTITION [message #684653 is a reply to message #684645] Sat, 17 July 2021 06:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3087
Registered: January 2010
Location: Connecticut, USA
Senior Member
Partition exchange based archival process must not archive current partition. In addition you'd need to take care of global indexes (if you have any) during or after exchange. Both "during" and "after" have pros and cons.

SY.
Re: How to stop swapping in EXCHANGE PARTITION [message #684658 is a reply to message #684644] Sun, 18 July 2021 01:07 Go to previous messageGo to next message
John Watson
Messages: 8618
Registered: January 2010
Location: Global Village
Senior Member
You need to add an empty partition to the archive table, and exchange that with the intermediate table.

Incidentally, I wish you would not say "record" when you mean "row".
Re: How to stop swapping in EXCHANGE PARTITION [message #684659 is a reply to message #684658] Sun, 18 July 2021 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67988
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

Re: How to stop swapping in EXCHANGE PARTITION [message #684665 is a reply to message #684659] Mon, 19 July 2021 01:20 Go to previous messageGo to next message
gopalMisra
Messages: 2
Registered: July 2021
Junior Member
Thank you folks for replies.

Quote:
If we exchange our cars I then have yours and you have mine, it won't be an exchange if in the end you have both cars and me nothing. Smile
Exchange between intermediate and archive is not the right operation, I think the best option is to use an INSERT SELECT then TRUNCATE the intermediate table.

@Michel Cadot: Partition which is getting '01-jan-2021' rows, are not ACTIVE partition. This scenario is rare but happened in past. Due to delay in some reconciliations, they might appear late. Table is day wise partitioned. I am writing process to keep only 100 partitions at any point of time. after archival, code is dropping partitions from active table. so if any backdated entry comes, it will create new partition. Which will eventually moved in next day or 2.

So I changed code. if this scenario appears, I simply append (insert in arc table from intermediate) rows in archival table (instead of exchanging it). This solves my problem.

global indexes on active tables: Exists
What are the number of interested rows: for any given day in between 15000-20000
And what about the updates in the active table? No updates only inserts

Quote:
Partition exchange based archival process must not archive current partition. In addition you'd need to take care of global indexes (if you have any) during or after exchange. Both "during" and "after" have pros and cons.

SY.
@Solomon Yakobson: Yes i get it. this is another piece which I need to work on. Thank you for advice.
Re: How to stop swapping in EXCHANGE PARTITION [message #684668 is a reply to message #684665] Mon, 19 July 2021 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 67988
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
for any given day in between 15000-20000
For such tiny number of rows, especially in the presence of global indexes, I'd get rid of intermediate table and partition exchange and use INSERT SELECT followed by a DELETE on the active table (unless you have big LOBs on the table).

Re: How to stop swapping in EXCHANGE PARTITION [message #684669 is a reply to message #684668] Mon, 19 July 2021 02:24 Go to previous message
Michel Cadot
Messages: 67988
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Another option is to make the archival table a materialized view of the active one and then just drop the original partition each day.

Previous Topic: get the record where the amount columns are same in 2 tables
Next Topic: Statement Level trigger - After Insert/Update
Goto Forum:
  


Current Time: Sat Oct 16 00:36:21 CDT 2021