RE: Strategy for partitioning a huge OLTP table

To make a useful analysis we’d need to know at a minimum the relationship (if any) of the time course of insert to deletion. What makes a row a candidate for delete?  

Then there is reporting: Is there any column set that would be useful for partition pruning with respect to reports?  

If there is a relationship between time and becoming a candidate for delete, even a soft correlation such as reaching a “complete” status after a certain amount of time (usually) and that “complete” status allows a row to be considered for deletion, then you might consider adding a date of insertion column implicitly that is your partitioning.  

Then you can used a slightly modified version of scaling to infinity such that when a time based insertion partition should be mostly ready for deletion, you copy out the “can’t be deleted yet” rows from that partition into a table and then do the partition exchange with the “keepers.”  

Good luck.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of kunwar singh Sent: Wednesday, September 29, 2021 4:22 AM To: ORACLE-L
Subject: Strategy for partitioning a huge OLTP table  

Hi Listers,  

Can you please suggest to me a reference for deciding how to partition a online OLTP table which is 300GB currently and going to grow upto 1TB soon .  

Roughly 300k deletes /hour . inserts 1M /hour. Table having 500M records so far.

other than ease of maintenance/purge records in a better way than delete, i dont see any other benefit of partitioning . But would like to hear your opinion.  

DB version :12.2.0.1  

The table doesnt have a date column worthy of partitioning .    

--

Cheers,
Kunwar

--

http://www.freelists.org/webpage/oracle-l Received on Wed Sep 29 2021 - 21:44:58 CEST