Partitioning

Partitioning splits a large table into smaller physical pieces called partitions, while logically presenting them as a single table. Each partition can be stored, indexed, backed up, and queried independently — but applications still write SELECT * FROM orders, oblivious to the underlying split.

Partitioning is an Enterprise Edition feature (separately licensed in older versions). It's the standard technique for tables that have grown past a few hundred GB and for fact tables in data warehouses.

Why Partition?

Three categories of benefit:

1. Performance — Oracle can prune partitions that can't match the query and only scan the relevant ones. A query against WHERE order_date >= DATE '2024-01-01' on a date-partitioned table only touches partitions that overlap that range.

2. Manageability — Drop a year-old partition in milliseconds; you can't drop a year of rows from a single table that quickly. Take partitions offline, backup them selectively, rebuild indexes per-partition.

3. Availability — Operations on one partition (rebuild index, move, archive) don't affect access to others. A failed disk holding one partition doesn't take the whole table down.

You don't get these benefits automatically. They depend on the queries matching the partition key, indexes being designed for the access pattern, and partitions being sized appropriately.

The Four Partitioning Strategies

Range Partitioning

The most common strategy. Each partition holds a range of values:

CREATE TABLE orders (
  order_id    NUMBER,
  order_date  DATE,
  customer_id NUMBER,
  total       NUMBER
)
PARTITION BY RANGE (order_date) (
  PARTITION p2022 VALUES LESS THAN (DATE '2023-01-01'),
  PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),
  PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01'),
  PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

Reads as: rows where order_date < 2023-01-01 go into p2022, rows where order_date < 2024-01-01 AND >= 2023-01-01 go into p2023, and so on. The MAXVALUE partition catches anything beyond the defined ranges.

-- This query scans only p2024:
SELECT * FROM orders WHERE order_date >= DATE '2024-01-01';

-- This query scans p2023 and p2024:
SELECT * FROM orders WHERE order_date BETWEEN DATE '2023-06-01' AND DATE '2024-06-01';

Interval Partitioning — Auto-Create Range Partitions

A common pain point with range partitioning: every year (or month) someone has to manually ALTER TABLE ... ADD PARTITION. Interval partitioning automates this:

CREATE TABLE orders (
  order_id   NUMBER,
  order_date DATE,
  total      NUMBER
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION p_start VALUES LESS THAN (DATE '2024-01-01'));

The first partition is explicit. Beyond that, Oracle creates monthly partitions automatically on first insert into each new month — no DBA intervention needed.

The auto-created partitions get system-generated names like SYS_P101. To use friendlier names:

ALTER TABLE orders RENAME PARTITION FOR (DATE '2024-03-01') TO p2024_03;

List Partitioning

For categorical data with discrete values:

CREATE TABLE customers (
  customer_id NUMBER,
  region      VARCHAR2(10),
  name        VARCHAR2(100)
)
PARTITION BY LIST (region) (
  PARTITION p_us   VALUES ('US', 'CA', 'MX'),
  PARTITION p_emea VALUES ('UK', 'DE', 'FR', 'IT'),
  PARTITION p_apac VALUES ('JP', 'IN', 'AU', 'CN'),
  PARTITION p_other VALUES (DEFAULT)
);

The DEFAULT partition catches values not listed elsewhere — recommended unless you want inserts to fail when a new region appears.

-- Query only touches p_us:
SELECT * FROM customers WHERE region IN ('US', 'CA');

Hash Partitioning

When you have no natural range or list, but you want to spread data evenly across N partitions for parallelism:

CREATE TABLE call_logs (
  call_id   NUMBER PRIMARY KEY,
  caller    VARCHAR2(20),
  call_time TIMESTAMP
)
PARTITION BY HASH (call_id) PARTITIONS 16;

Oracle applies an internal hash function to call_id and distributes rows across 16 partitions. The number is usually a power of 2 (4, 8, 16, 32) so the hash distribution is even.

Hash partitioning doesn't help with range queries (you still scan all 16 partitions for WHERE call_id > 1000), but it parallelises better and avoids hotspots when many writers insert sequential IDs.

Composite Partitioning

Combine two strategies: partition by range, sub-partition by hash (or list). Standard pattern in data warehouses:

CREATE TABLE fact_sales (
  sale_id     NUMBER,
  sale_date   DATE,
  product_id  NUMBER,
  amount      NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (product_id) SUBPARTITIONS 8
(
  PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),
  PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01')
);

p2023 and p2024 each contain 8 sub-partitions distributed by product_id. A query filtering on date prunes to one partition; further filtering on product_id can prune to one sub-partition.

Other composite options:

• RANGE-LIST — date range, then region list
• LIST-RANGE — region list, then date range
• LIST-HASH, HASH-HASH, etc.

Partition Pruning

Partition pruning is the cost-based optimiser's ability to skip partitions that can't satisfy the query. It's the single biggest reason to partition.

-- This query prunes to only p2024:
SELECT * FROM orders WHERE order_date >= DATE '2024-01-01';

In the execution plan you'll see:

| Id | Operation                          | Pstart | Pstop |
|  0 | SELECT STATEMENT                   |        |       |
|  1 |  PARTITION RANGE ITERATOR          | KEY    | KEY   |   ← pruning happened
|  2 |   TABLE ACCESS FULL | ORDERS       |   3    |   4   |   ← scans partitions 3-4 only

Pstart and Pstop show which partitions are visited.

What Defeats Pruning

Pruning requires Oracle to map the WHERE clause to specific partitions at parse time (static pruning) or execution time (dynamic pruning).

-- Good: literal value → static pruning at parse time
WHERE order_date = DATE '2024-03-15'

-- Good: bind variable → dynamic pruning at execution
WHERE order_date = :order_date

-- BAD: function on the partition key defeats pruning
WHERE TRUNC(order_date) = DATE '2024-03-15'

-- BAD: type conversion defeats pruning
WHERE order_date = '2024-03-15'   -- implicit TO_DATE means a function call

The rule: keep the partition key un-wrapped on the left side of the predicate. Use the inverse on the right side instead.

-- Good
WHERE order_date >= TRUNC(SYSDATE) AND order_date < TRUNC(SYSDATE) + 1

-- Bad (no pruning)
WHERE TRUNC(order_date) = TRUNC(SYSDATE)

Partition-Wise Joins

When two tables are partitioned the same way on the join key, Oracle can join them partition-by-partition in parallel — no single big sort or hash. This is dramatically faster than a normal join on large datasets.

-- Both tables partitioned by customer_id with the same hash key
CREATE TABLE customers (customer_id NUMBER PRIMARY KEY, ...)
  PARTITION BY HASH (customer_id) PARTITIONS 16;

CREATE TABLE orders (order_id NUMBER, customer_id NUMBER, ...)
  PARTITION BY HASH (customer_id) PARTITIONS 16;

-- This join is partition-wise:
SELECT *
FROM   customers c
JOIN   orders    o ON c.customer_id = o.customer_id;

Oracle joins customers_p1 with orders_p1, customers_p2 with orders_p2, and so on — 16 small joins instead of one giant one. With parallelism, this scales nearly linearly with CPU count.

For this to work:

• Both tables partitioned on the join column
• Same partitioning strategy and number of partitions
• The optimiser must choose to use it (driven by table statistics and parallel hint)

Local vs Global Indexes

A partitioned table has two index families:

Local index — partitioned the same way as the table. Each table partition has its own index partition.

CREATE INDEX orders_date_idx ON orders (order_date) LOCAL;

Pros:

• Drop/move a table partition → just drop/move that index partition (fast)
• Smaller indexes per partition → faster maintenance
• Better partition pruning during query

Cons:

• A query that doesn't filter on the partition key may need to access every local index partition

Global index — one B-tree over all table partitions. Optionally partitioned by its own key (different from the table).

-- Global, unpartitioned:
CREATE INDEX orders_total_idx ON orders (total);

-- Global, partitioned (by hash on its own key):
CREATE INDEX orders_cust_idx ON orders (customer_id)
GLOBAL PARTITION BY HASH (customer_id) PARTITIONS 16;

Pros:

• One index for all data — efficient for queries that don't filter on the table's partition key

Cons:

• Drop/move a table partition requires global index rebuild (or UPDATE INDEXES clause)
• Maintenance is more disruptive

Rule of thumb: local indexes for the partition key and for columns frequently combined with it. Global indexes for columns queried independently of the partition key.

Partition Exchange — The Lightning-Fast Load

A standard ETL pattern uses partition exchange to load new data with minimal downtime:

-- 1. Load new data into a temporary, un-partitioned table that matches the structure:
CREATE TABLE orders_new_month AS SELECT * FROM orders WHERE 1=0;
INSERT INTO orders_new_month SELECT * FROM external_csv_data;

-- 2. Build indexes on the standalone table (parallel, doesn't affect production):
CREATE INDEX orders_new_month_idx ON orders_new_month (order_date);

-- 3. Atomically swap the table contents into a partition:
ALTER TABLE orders
EXCHANGE PARTITION p2024_03
WITH TABLE orders_new_month
INCLUDING INDEXES;

Step 3 is essentially a metadata change: Oracle just renames pointers. Production users see the new data appear instantly. The previous partition contents are now in orders_new_month (which you can then truncate or archive).

Same trick in reverse for archival:

-- Move a partition out to a standalone table for archival:
ALTER TABLE orders
EXCHANGE PARTITION p2022
WITH TABLE orders_archive_2022;

-- Now drop the partition entirely:
ALTER TABLE orders DROP PARTITION p2022;

Both directions are millisecond operations, regardless of how many million rows are involved.

Adding, Dropping, and Splitting Partitions

-- Add a new partition:
ALTER TABLE orders ADD PARTITION p2025
  VALUES LESS THAN (DATE '2026-01-01');

-- Drop a partition (and its data):
ALTER TABLE orders DROP PARTITION p2022;

-- Truncate a partition (remove data, keep structure):
ALTER TABLE orders TRUNCATE PARTITION p2022;

-- Split one partition into two:
ALTER TABLE orders SPLIT PARTITION p_max AT (DATE '2026-01-01')
  INTO (PARTITION p2025, PARTITION p_max);

-- Merge two partitions:
ALTER TABLE orders MERGE PARTITIONS p2022, p2023 INTO PARTITION p_old;

-- Rename a partition:
ALTER TABLE orders RENAME PARTITION p_old TO p_archive;

When NOT to Partition

Partitioning is not free. Consider before partitioning:

• Small tables (under 50 GB) usually don't benefit; the maintenance overhead exceeds any speedup
• Queries that don't filter on the partition key can be slower (more partition scans, less effective indexes)
• Heavy OLTP with random ID inserts — Hash partitioning can help, but row-by-row reads now traverse 1-of-N partition lookups
• Frequent cross-partition transactions — distributed transactions across partitions add overhead
• Wrong partition key — partitioning on a column the queries rarely filter on gives no speedup, only complexity

The classic failure mode: partition a table on INSERT_DATE because it grows over time, then run reports filtered by CUSTOMER_ID — no pruning happens, ever.

Inspecting Partitioning

-- All partitions of a table:
SELECT partition_name, partition_position, high_value, num_rows
FROM   user_tab_partitions
WHERE  table_name = 'ORDERS'
ORDER BY partition_position;

-- Sub-partitions:
SELECT subpartition_name, partition_name, num_rows
FROM   user_tab_subpartitions
WHERE  table_name = 'FACT_SALES';

-- Local vs global indexes:
SELECT index_name, partitioned, locality
FROM   user_part_indexes
WHERE  table_name = 'ORDERS';

-- Partition key columns:
SELECT name, column_name, column_position
FROM   user_part_key_columns
WHERE  name = 'ORDERS';

Worked Example — Designing for a Time-Series Workload

Requirements:

• Table: iot_events
• 500 million rows/year, retain 3 years, drop year 4
• Common queries: filter by event_time (last 24h, last 30d), occasionally by device_id
• Loaded hourly from an external system

Design:

CREATE TABLE iot_events (
  event_id    NUMBER,
  event_time  TIMESTAMP NOT NULL,
  device_id   VARCHAR2(20) NOT NULL,
  metric      VARCHAR2(50),
  value       NUMBER
)
PARTITION BY RANGE (event_time)
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
(PARTITION p_start VALUES LESS THAN (TIMESTAMP '2024-01-01 00:00:00'));

-- Local index on the most common filter column besides event_time:
CREATE INDEX iot_events_device_idx ON iot_events (device_id) LOCAL;

-- Global index on event_id for primary key lookups:
CREATE UNIQUE INDEX iot_events_pk ON iot_events (event_id);

-- Three years from now, drop the oldest day automatically:
-- (use DBMS_SCHEDULER to run this nightly)
ALTER TABLE iot_events DROP PARTITION FOR (SYSTIMESTAMP - INTERVAL '3' YEAR);

What this gives:

• Each day is its own partition (auto-created)
• "Last 24 hours" queries prune to 1-2 partitions out of 1095
• Old data drops in milliseconds via DROP PARTITION
• Per-device queries within a day use the local index efficiently

Common Errors

Interview Corner

ALTER TABLE orders EXCHANGE PARTITION p2024_03 WITH TABLE staging_data;

-- Hourly ETL job:
TRUNCATE TABLE stg_hour;
INSERT /*+ APPEND */ INTO stg_hour SELECT ... FROM raw_data;
-- (build indexes on stg_hour)
ALTER TABLE fact_table EXCHANGE PARTITION p_current_hour
  WITH TABLE stg_hour INCLUDING INDEXES;
COMMIT;

Related Topics

• Indexes — local vs global indexes are central to partitioned table performance
• Performance — partition pruning is the most impactful tuning technique on large tables
• DDL Commands — CREATE TABLE ... PARTITION BY ... and partition-specific ALTER syntax
• DML Commands — EXCHANGE PARTITION is the canonical bulk-load pattern
• Flashback — partition-level operations interact with flashback differently than table-level