Re: Strategy for partitioning a huge OLTP table

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 29 Sep 2021 16:30:05 -0400
Message-ID: <6870f581-8187-e5c0-86cb-d82d064d030d_at_gmail.com>

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

Well, you don't have to use range partitioning. You can also use some kind of status for list partitioning. There is a problem with using status for list partitioning: changing status entails physical copy from one partition to another and, of course, both local and global index maintenance. That can be pretty intense, so you have to make sure that this doesn't help too frequently. Other than that, the question you are asking is a business question, not an Oracle one. Basically, you're asking the question of partitioning, business and everything which is a part of the broader question of life, universe and everything. And we all know answer to that: "What do you mean? European or African?" It's good to know such things when you're a DBA.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

-- http://www.freelists.org/webpage/oracle-l Received on Wed Sep 29 2021 - 22:30:05 CEST