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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
