{Abhilash Kumar Bhattaram : Follow on LinkedIn}
This article focuses on partitioning and large data challenges in managed databases and how I help you turn a DBA technique into a data retention policy your CTO, your auditor, and your cloud bill can all live with.
Partitioning & Large Data
This may look like a DBA-centric topic. It isn't. Partitioning is the mechanism that makes a data retention policy enforceable and a retention policy that can't be enforced is just a document.
This is probably applicable for any RDBMS and not just Oracle.
Cloud Storage you add is storage you pay for. Cloud data you purge first is storage you never have to buy as you can target to reuse the space that is reclaimed.
Mission-critical core databases are rarely built with data management in mind. They were built to go live. Years later, they carry a decade of history in the same tables that serve today's transactions, and the conversation turns to "we need more storage" long before it turns to "why are we keeping all of this?"
1. Ground Zero: Where Challenges Start
Let's start with a plain canvas and understand our ground zero , most large-data problems are not technical failures, they are decisions nobody was ever asked to make.
Weekend-storage-panic causes & corresponding solutions:
1. Ground Zero: Where Challenges Start
- Tables grow forever because nobody owns the question "how long do we keep this?" → Solution: Get a retention period agreed per data class, in writing, before touching any design
- Storage alerts trigger a purchase instead of a conversation → Solution: Make "what can we purge?" the first question, "what can we add?" the second
-
Purge jobs run as huge
DELETEstatements that crawl, bloat undo, and fail midway → Solution: Remove data by dropping or truncating partitions, not by deleting rows - Month-end and reporting queries scan years of history to find last week's data → Solution: Let partition pruning confine the work to the recent data that actually matters
- Every performance complaint ends with "let's partition everything" → Solution: Partition where size and access pattern justify it — not as a reflex
- Compliance asks "can you prove data older than X is gone?" and the answer is a long pause → Solution: Make purge a scheduled, logged, auditable operation
- Capacity is added in the cloud first, and purging is promised for later → Solution: Purge first — the added storage keeps billing whether or not the data is eventually deleted
As you can see, before any partitioning design begins, there's a business question to settle first.
Data management is the crux of retention. Partitioning is simply the tool that lets you act on the answer.
2. Underneath Ground Zero:
Once you've understood your Ground Zero problems, you need to move to why established core systems end up here — and why "just partition it" isn't the answer by itself.
Why large tables become a nightmare in mature systems:
- The application was designed for go-live, not for year ten → Solution: Treat retention and archival as design requirements, even when retrofitting
- OLTP works almost entirely on the most recent data, but nothing in the design reflects that → Solution: Align storage layout with access pattern — recent data hot, older data cold or gone
- Not every large table has a partitioning problem → Solution: Check first whether the issue is size, a bad plan, missing indexes, or a stale statistic — partitioning fixes size and lifecycle, not everything
- Partitioning is available in the database but not in the edition or license you're running → Solution: Confirm the partitioning entitlement for your edition and shape before designing around it
- The partition key doesn't match how the application queries → Solution: Choose the key from real query and purge patterns — usually a date — not from convenience
- Global indexes and unique constraints get in the way of partition maintenance → Solution: Plan index strategy up front so dropping a partition doesn't mean rebuilding the world
- Backups, Flashback, and standby copies still hold purged data → Solution: Understand that a purge shortens live data, while older copies age out on their own retention schedule
This gap is why "we purged it" and "it is gone" so often mean different things to the business and the auditor.
As you can see, the real problem underneath is a missing lifecycle for data — and partitioning is how you give it one.
3. Working Upwards:
Before the technique, a point that has to stand on its own.
Purging data is a business decision, not a DBA decision. The DBA can drop a partition in seconds, but only the business can say that the data is no longer needed — for operations, for a regulator, for a dispute, for an audit. Retention periods come from the business and compliance owners, and the DBA's job is to carry that decision out reliably, not to make it. This is exactly why long term backups exist: when the live database has to stay lean but the business still has to be able to produce old data, the answer is a retained backup, not a bloated production table. I covered what OCI long term backups can and cannot satisfy in my earlier blog below.
Purge the live data on the business's schedule, retain what must be retained on the backup side, and the two controls work together.
Here's what partitioning actually buys you, in two things a CTO can take to the board.
What partitioning gives you:
- Predictable OLTP performance. Your transactional application works on the most recent data. When that data lives in its own partitions, you can benchmark it, tune it, and defend its response times independently of how large the history has become.
- Compliant, cheap purging. When data passes its retention period, you remove the partition instead of deleting millions of rows. It is fast, it generates little undo and redo, and it is a clean, loggable event for audit.
Why purge timing decides your cloud bill:
- Storage added to an OCI database system is storage you keep paying for, and it is generally not something you hand back once the data is gone.
- Purge first, then size. If the purge happens after the capacity is added, the saving never reaches your invoice — you bought the storage anyway.
- This is the quiet link between a retention policy and a cost-optimization programme: retention is a cost control, not just a compliance control.
When not to partition:
- The table is small or growing slowly — the overhead outweighs the benefit.
- The slowness comes from a bad plan, a missing index, or stale statistics — fix those first.
- There is no clean key that matches both how the application queries and how data is retired.
- The edition or license in use does not include partitioning.
A quick look under the hood (for the technical conversation with your DBA — not something a CTO needs to write, just recognize):
A table that creates a new monthly partition automatically as data arrives:
CREATE TABLE app.transactions (
txn_id NUMBER,
txn_date DATE,
amount NUMBER
)
PARTITION BY RANGE (txn_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION p_initial VALUES LESS THAN (DATE '2024-01-01'));
Retiring a month of data that has passed its retention period:
ALTER TABLE app.transactions
DROP PARTITION FOR (DATE '2024-01-15')
UPDATE GLOBAL INDEXES;
Existing large tables can be converted to partitioned online in many cases, so this is a retrofit option for established systems rather than only a new-build one. That one statement is why purging by partition is quick and auditable: it removes a whole slice of data in one step, where a row-by-row delete would run for hours.
The point for a CTO isn't the syntax — it's that data retirement becomes a scheduled, repeatable operation instead of a project.
Technical Issues:
- Purge done with large
DELETEjobs that bloat undo and run for hours — Solution: Move to partition drop or truncate for time-based data - Partition key chosen without looking at real query patterns — Solution: Analyse access and purge patterns before choosing the key
- Global indexes invalidated or rebuilt on every partition drop — Solution: Design index strategy for partition maintenance from the start
- Partitioning assumed available without checking the edition — Solution: Verify entitlement before building the design
Non-Technical Issues:
- No agreed retention period per data class — Solution: Get business and compliance sign-off on retention before any technical work
- Purge treated as optional housekeeping, not a scheduled control — Solution: Put purge on the operational calendar with a named owner
- Storage approved before purge is considered — Solution: Make "what can be purged?" a mandatory step in every capacity request
- Backups and standby copies overlooked when answering "is it deleted?" — Solution: Document how purged data ages out of every other copy, so the audit answer is consistent
Partitioning isn't a performance trick. It's what turns "we should have a retention policy" into something you can actually
run, audit, and see on your cloud bill.
Stay tuned for my next post.
How Nabhaas helps you
If you’ve made it this far, you already sense there’s a better way — in fact, you have a way ahead.
If you’d like Nabhaas to assist in your journey, remember — TAB is just one piece. Our Managed Delivery Service ensures your Oracle operations run smoothly between patch cycles, maintaining predictability and control across your environments.
NABTAB - Whitepaper ,
Reference & White Paper
Managed Delivery Services - Whitepaper ,
Reference & White Paper
Top comments (0)