PARTITIONING

RANGE | LIST | HASH | INTERVAL | COMPOSITE | REFERENCE


Partitioning — Overview

  • Partitioning splits a large table (or index) into smaller, more manageable pieces called partitions, while it is still treated as a single logical object by applications.
  • Each partition is stored as a separate physical segment and can reside in its own tablespace.
  • The column(s) used to decide which partition a row belongs to is called the Partition Key.
  • Fully transparent to SQL — no application code changes needed; queries run against the table name as usual.
Benefits of Partitioning
  • Improved performance — Partition Pruning lets the optimizer scan only the relevant partitions instead of the whole table.
  • Easier manageability — backup, load, and purge operations can work on a single partition instead of the entire table.
  • Better availability — a problem in one partition (e.g. corruption) doesn't affect the rest of the table.
  • Improved performance of DML — parallel DML and partition-wise joins reduce contention.
  • Simplified data lifecycle management — easily archive/drop old data by dropping a partition (e.g. drop last year's data instantly).

Types of Partitioning

TypePartition Key Based On
RangeA range of values — e.g. dates, ID ranges
ListA discrete list of values — e.g. region, status code
HashAn internal hash function — used to spread data evenly when there's no natural range/list
IntervalAutomatic extension of Range — Oracle creates new partitions on demand
CompositeTwo levels combined — e.g. Range-Hash, Range-List, List-Hash, List-List
ReferenceInherits the partitioning strategy of a parent table via a foreign key
1) Range Partitioning

Rows are distributed based on a range of values in the partition key (commonly dates).

SQL
CREATE TABLE sales (
  sale_id NUMBER, sale_date DATE, amount NUMBER)
PARTITION BY RANGE (sale_date) (
  PARTITION p2023 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY')),
  PARTITION p2024 VALUES LESS THAN (TO_DATE('01-JAN-2025','DD-MON-YYYY')),
  PARTITION p_max VALUES LESS THAN (MAXVALUE)
);
2) List Partitioning

Rows are distributed based on a discrete list of values in the partition key.

SQL
CREATE TABLE customers (
  cust_id NUMBER, region VARCHAR2(10))
PARTITION BY LIST (region) (
  PARTITION p_south VALUES ('TN','KA','KL'),
  PARTITION p_north VALUES ('DL','PB','HR'),
  PARTITION p_other VALUES (DEFAULT)
);
3) Hash Partitioning

Oracle applies an internal hash function to the partition key to evenly spread rows across a fixed number of partitions.

Used when there's no natural range/list, mainly to balance I/O and improve parallelism.

SQL
CREATE TABLE orders (
  order_id NUMBER, cust_id NUMBER)
PARTITION BY HASH (order_id)
PARTITIONS 4;
4) Interval Partitioning

An extension of Range partitioning — Oracle automatically creates new partitions on demand as new data arrives, instead of the DBA pre-creating them.

SQL
CREATE TABLE sales_int (
  sale_id NUMBER, sale_date DATE)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) (
  PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY'))
);
5) Composite Partitioning

Combines two partitioning strategies — data is first partitioned, then each partition is further sub-partitioned.

Common combinations: Range-Hash, Range-List, List-Hash, List-List.

SQL
CREATE TABLE sales_comp (
  sale_id NUMBER, sale_date DATE, region VARCHAR2(10))
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
  SUBPARTITION sp_south VALUES ('TN','KA'),
  SUBPARTITION sp_north VALUES ('DL','PB')
)(
  PARTITION p2023 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY')),
  PARTITION p2024 VALUES LESS THAN (TO_DATE('01-JAN-2025','DD-MON-YYYY'))
);
6) Reference Partitioning

A child table inherits the partitioning strategy of a parent table through an enforced foreign-key relationship — no need to duplicate the partition key column in the child.

SQL
-- Parent (range partitioned)
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, order_date DATE)
PARTITION BY RANGE (order_date) (
  PARTITION p2024 VALUES LESS THAN (TO_DATE('01-JAN-2025','DD-MON-YYYY'))
);

-- Child (reference partitioned)
CREATE TABLE order_items (
  item_id NUMBER, order_id NUMBER,
  CONSTRAINT fk_ord FOREIGN KEY (order_id) REFERENCES orders(order_id))
PARTITION BY REFERENCE (fk_ord);

Partition Maintenance Operations

SQL
-- Add a new partition
ALTER TABLE sales ADD PARTITION p2025
VALUES LESS THAN (TO_DATE('01-JAN-2026','DD-MON-YYYY'));

-- Drop a partition (data lost, unless UPDATE GLOBAL INDEXES is used with care)
ALTER TABLE sales DROP PARTITION p2023;

-- Truncate a partition (removes rows, keeps the partition)
ALTER TABLE sales TRUNCATE PARTITION p2023;

-- Split a partition into two
ALTER TABLE sales SPLIT PARTITION p_max AT
(TO_DATE('01-JAN-2026','DD-MON-YYYY'))
INTO (PARTITION p2025, PARTITION p_max);

-- Merge two partitions into one
ALTER TABLE sales MERGE PARTITIONS p2023, p2024 INTO PARTITION p_old;

-- Rename a partition
ALTER TABLE sales RENAME PARTITION p2024 TO p_2024_final;

-- Move a partition to a different tablespace
ALTER TABLE sales MOVE PARTITION p2024 TABLESPACE tbs_archive;

-- Exchange a partition with a standalone table (fast load/archive, no data copy)
ALTER TABLE sales EXCHANGE PARTITION p2023
WITH TABLE sales_2023_stage;

-- Query a specific partition directly
SELECT * FROM sales PARTITION (p2024);
SELECT * FROM sales PARTITION FOR (TO_DATE('15-JUN-2024','DD-MON-YYYY'));

Indexes on Partitioned Tables

A partitioned table can have Local indexes or Global indexes.

Local IndexGlobal Index
One index partition per table partition (auto-aligned)Index partitioning is independent of the table's partitioning
Automatically maintained when a table partition is added/droppedMust be rebuilt/maintained manually after partition maintenance operations
Simple to manage — recommended for most partitioned tablesUseful for global uniqueness (e.g. primary key) across all partitions
SQL
-- Local index (one-to-one with table partitions)
CREATE INDEX idx_sales_date ON sales (sale_date) LOCAL;

-- Global index (independent partitioning, e.g. by range)
CREATE INDEX idx_sales_id ON sales (sale_id)
GLOBAL PARTITION BY RANGE (sale_id) (
  PARTITION ip1 VALUES LESS THAN (100000),
  PARTITION ip2 VALUES LESS THAN (MAXVALUE)
);

-- Rebuild an index partition (often needed after table partition maintenance)
ALTER INDEX idx_sales_id REBUILD PARTITION ip1;

Useful Dictionary Views

SQL
SELECT table_name, partitioning_type, subpartitioning_type
FROM user_part_tables;

SELECT table_name, partition_name, high_value, tablespace_name
FROM user_tab_partitions;

SELECT table_name, partition_name, subpartition_name
FROM user_tab_subpartitions;

SELECT name, object_type, column_name, column_position
FROM user_part_key_columns;

Easy Memory

PARTITIONING
|
+-- RANGE     --> ranges of values (dates, IDs)
+-- LIST      --> discrete values (region, status)
+-- HASH      --> even spread, no natural key
+-- INTERVAL  --> auto-extending RANGE
+-- COMPOSITE --> two levels (Range-Hash, Range-List...)
+-- REFERENCE --> inherited from parent via FK
|
+-- Maintenance: ADD / DROP / SPLIT / MERGE / TRUNCATE / EXCHANGE / MOVE / RENAME
|
+-- Index: LOCAL (auto-aligned) / GLOBAL (independent, manual maintenance)