Mastering Oracle Partitioning: Concepts, History, Best Practices & New Features

Partition Basics

Oracle partitioning splits a table into independent data segments according to user‑defined rules. The partitioned table is accessed exactly like a regular table, so applications require no code changes. Benefits include:

Fine‑grained DDL (DELETE, TRUNCATE, MOVE, index rebuild) can be applied to individual partitions, reducing maintenance windows.

Improved availability: a failure in the datafile of one partition does not affect the other partitions.

Better manageability: many small partitions are easier to monitor and purge than one massive table.

Evolution History

Partitioning was introduced in Oracle 8. Early releases supported RANGE, LIST and HISTORIC partitions. Subsequent releases added composite, reference, and hash partitions, and introduced a richer set of management features in Oracle 12c, 12.2 and 18c.

Best Practices

Range Partitioning

Ideal for tables with a natural time column (e.g., order_date, created_at). Use DDL to drop old partitions quickly.

CREATE TABLE sales (
  order_id   NUMBER,
  order_date DATE,
  amount     NUMBER
) PARTITION BY RANGE (order_date) (
  PARTITION p_2023 VALUES LESS THAN (TO_DATE('01‑JAN‑2024','DD‑MON‑YYYY')),
  PARTITION p_2024 VALUES LESS THAN (TO_DATE('01‑JAN‑2025','DD‑MON‑YYYY')),
  PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

Insert‑plus‑Exchange for selective cleanup

When a small subset of rows must be retained while the rest are purged, follow these steps:

Lock the source table to prevent concurrent DML.

Insert the rows to keep into a temporary table.

Exchange the source partition with an empty staging partition.

Insert the retained rows back if needed.

LOCK TABLE sales IN EXCLUSIVE MODE;
INSERT INTO sales_tmp SELECT * FROM sales WHERE order_id IN (/*ids to keep*/);
ALTER TABLE sales EXCHANGE PARTITION p_future WITH TABLE sales_tmp WITHOUT VALIDATION;
-- optional: re‑insert retained rows

Reference Partitioning

Eliminates the need for redundant partition keys in child tables. From Oracle 11g onward, a child table can inherit the partitioning scheme of its parent via a foreign‑key relationship.

CREATE TABLE orders (
  order_id   NUMBER PRIMARY KEY,
  order_date DATE
) PARTITION BY RANGE (order_date) (
  PARTITION p_2023 VALUES LESS THAN (TO_DATE('01‑JAN‑2024','DD‑MON‑YYYY')),
  PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

CREATE TABLE order_items (
  item_id   NUMBER,
  order_id  NUMBER,
  product   VARCHAR2(30),
  CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id)
) PARTITION BY REFERENCE (fk_order);

Reference partitions support cascading TRUNCATE: truncating a parent partition automatically truncates the matching child partitions.

Hash Partitioning

Use when no obvious range or list key exists. Choose a low‑cardinality column with uniform distribution, set the number of partitions to a power of two, and consider global indexes for point‑lookup queries.

CREATE TABLE sessions (
  session_id   NUMBER,
  user_id      NUMBER,
  start_time   DATE
) PARTITION BY HASH (user_id) PARTITIONS 8;

List Partitioning

Best for categorical data such as region codes. Always define a DEFAULT partition to capture unexpected values and avoid insert errors.

CREATE TABLE customers (
  cust_id   NUMBER,
  region    VARCHAR2(2)
) PARTITION BY LIST (region) (
  PARTITION p_us VALUES ('US'),
  PARTITION p_eu VALUES ('EU'),
  PARTITION p_asia VALUES ('AS'),
  PARTITION p_default VALUES (DEFAULT)
);

Index Strategies

Global indexes are single‑tree indexes that span all partitions. They remain usable after partition DDL, but DDL can invalidate them and require rebuild.

Local indexes are aligned with partitions; they are automatically maintained with DML and support parallel scans. Primary‑key local indexes must include the partition key columns.

Recent Features (Oracle 12c, 12.2, 18c)

Partial Partition Indexes

Index only a subset of partitions (typically recent ones) to reduce maintenance overhead.

CREATE INDEX idx_sales_amount ON sales(amount)
  GLOBAL PARTITION BY RANGE (order_date)
  (PARTITION p_recent VALUES LESS THAN (TO_DATE('01‑JAN‑2025','DD‑MON‑YYYY')));

Index Asynchronous Maintenance (12c)

When a partition DDL operation would normally rebuild a global index synchronously, the UPDATE INDEXES clause triggers background maintenance, allowing the DDL to finish faster.

ALTER TABLE sales ADD PARTITION p_2025 VALUES LESS THAN (TO_DATE('01‑JAN‑2026','DD‑MON‑YYYY'))
  UPDATE INDEXES;

Automatic List Partitioning (12.2)

Oracle can create list partitions on‑the‑fly, automatically adding a DEFAULT bucket for unknown values.

ALTER TABLE customers SET SUBPARTITION TEMPLATE (
  LIST (region) (
    PARTITION p_us VALUES ('US'),
    PARTITION p_eu VALUES ('EU'),
    PARTITION p_default VALUES (DEFAULT)
  )
);

Online Partition Merge (18c)

Multiple adjacent partitions can be merged without taking the table offline.

ALTER TABLE sales MERGE PARTITIONS p_2023, p_2024 INTO PARTITION p_2023_2024;

Online Convert to Partitioned Table

Convert a regular table to a partitioned one without downtime.

ALTER TABLE sales SET PARTITION BY RANGE (order_date)
  INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));

These features collectively improve performance, simplify lifecycle management, and increase availability for large‑scale Oracle environments.