strategy: partitioned table, delete millions of rows



IBM's flagship relational database management system

strategy: partitioned table, delete millions of rows

Postby BobHarris » Wed Aug 17, 2022 2:47 pm

Hello,
I've have a question about deleting millions of rows of partitioned table.
There are 12 table partitions for storing specific customer data.
Each partition holds up data for 3 months, so that these 12 partitions
store data for the last 3 years. I want to delete all records of the
4 oldest partitions and use them then as the paritions for thew newest
customer data coming in. It's like a ring buffer, that holds data for the
past 3 years.

Locking the 4 partitions and deleting all corresponding rows takes too much time
and would prevend new customer data from beeing stored.

I have two ideas how to delete the corresponding records.
1) delete/remove old partitions, create new partitions
2) delete a creatin amount of records of the partitions block wise on a daily basis.

Is there a better way how to delete the old partitions very fast ? What about idea 1. Could that work ?

kind regards,
bob harris
BobHarris
 
Posts: 1
Joined: Thu Aug 04, 2022 5:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: strategy: partitioned table, delete millions of rows

Postby engh » Tue Sep 20, 2022 1:56 pm

Hello

A LOAD DUMMY with "LOG NO" option takes less than one second, even if there are millions or billions of rows !

That's the best way to remove all records from a partition or a table
engh
 
Posts: 6
Joined: Fri Jul 08, 2022 7:29 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post