Oracle Database / Advanced: secure and recoverable evolution / Lesson 14

Partitioning, pruning and data lifecycle

Evaluate time-based layout as a lifecycle design with explicit boundaries, index consequences and entitlement checks.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Reason about range boundaries and partition pruning.
Connect retention and unique-key rules to index design.

Start from a lifecycle problem

A scan history table may retain recent months for operational investigation and older months for slower archival access. Partitioning divides one logical object into manageable pieces; it does not turn the table into separate application databases. The useful questions are whether queries select a limited time range, whether retention can operate on complete periods and whether maintenance can isolate older data. A large row count alone does not prove a benefit. Partitioning capabilities and licensing depend on the exact Oracle offering, so confirm entitlement before an implementation experiment instead of assuming Enterprise Edition includes every option.

Upper bounds are exclusive

For range partitioning, VALUES LESS THAN names an exclusive upper boundary. The p_before_oct partition below contains dates before October 1, p_oct contains October 1 through just before November 1, and p_future catches later values. Naming a partition after a month does not establish its contents; only the bounds do. A catch-all MAXVALUE partition can prevent an out-of-range insert failure, but it also requires an explicit process for future layout changes. Decide how missing event_time values are handled with NOT NULL rather than treating their placement as an undocumented exception.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

CREATE TABLE scan_history (
 scan_id NUMBER NOT NULL, event_time DATE NOT NULL, shipment_id NUMBER NOT NULL
) PARTITION BY RANGE (event_time) (
 PARTITION p_before_oct VALUES LESS THAN (DATE '2026-10-01'),
 PARTITION p_oct VALUES LESS THAN (DATE '2026-11-01'),
 PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

Illustrative definition only, conditional on version, privileges and partitioning entitlement. Boundary midnight belongs to the partition starting at that boundary.

A useful predicate exposes the partition key

A half-open event_time range can allow Oracle to avoid irrelevant partitions. This is pruning, which is distinct from using an index inside the remaining partitions. Wrapping the partition key in a function or introducing incompatible implicit conversion can complicate the access strategy. Check the actual authorized plan’s partition information rather than assuming that a calendar-looking filter prunes. Bind variables can lead to runtime pruning, so the absence of literal partition numbers in an estimate is not by itself failure. Keep correctness and typed boundaries first; pruning is the performance consequence to verify.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT COUNT(*)
FROM scan_history
WHERE event_time >= DATE '2026-10-01'
  AND event_time < DATE '2026-11-01';

The intended population lies in p_oct for the definition above. A plan review, not this text alone, establishes the chosen pruning behavior.

Local and global indexes answer different needs

A local index follows the table’s partition layout and can make period-oriented maintenance more self-contained. A global index can enforce or serve access patterns spanning that layout but has different maintenance consequences. For a local unique index, the table partition key must be included in the index key; adding it changes the uniqueness contract to that composite key. If scan_id must be globally unique, do not accidentally weaken the rule to uniqueness only within event_time combinations. Evaluate the exact constraint and supporting index together. A convenient lifecycle layout cannot silently redefine the business identity.

Retention is a verified business operation

Removing a period can invalidate a legal hold, destroy a reconciliation window or affect related tables even when the database command is fast. Before any partition maintenance, inventory foreign keys, global and local indexes, replication dependencies, restore requirements and downstream extracts. Exchange-partition workflows also require structural compatibility and deliberate validation decisions. This course provides no destructive maintenance command. A reviewable plan should identify the exact bounds, expected row population, preserved backup evidence and post-change checks. Performance gains are secondary to proving that the intended records, and only those records, follow the retention policy.

Common errors and fixes

ORA-14400

Why it happens: An inserted key has no matching range partition.
How to fix it: Review the actual boundaries and approved future-partition policy.
Prevent it next time: Test boundary midnights and future dates before each ingest period.

ORA-14039

Why it happens: A proposed local unique index omits required partitioning columns.
How to fix it: Reconcile the global business uniqueness requirement with the index design.
Prevent it next time: Review constraints and partition keys together rather than independently.

A partitioned report still reads excessive data

Why it happens: The predicate or access strategy does not prune the intended pieces.
How to fix it: Inspect typed predicates and plan partition evidence.
Prevent it next time: Measure representative periods and preserve a correctness reconciliation.

Check your understanding

1. Where does event_time = DATE '2026-11-01' belong in the example?

Show worked solution

It is excluded from p_oct because its upper bound is exclusive, so it belongs to p_future. The partition name cannot override this rule. Test records exactly at both boundaries and just before them using the column’s time precision, rather than relying on a monthly label in a dashboard.

2. Can adding event_time to a local unique key preserve a rule that scan_id alone is globally unique?