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
| Type | Partition Key Based On |
|---|---|
| Range | A range of values — e.g. dates, ID ranges |
| List | A discrete list of values — e.g. region, status code |
| Hash | An internal hash function — used to spread data evenly when there's no natural range/list |
| Interval | Automatic extension of Range — Oracle creates new partitions on demand |
| Composite | Two levels combined — e.g. Range-Hash, Range-List, List-Hash, List-List |
| Reference | Inherits 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).
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.
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.
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.
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.
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.
-- 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
-- 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 Index | Global 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/dropped | Must be rebuilt/maintained manually after partition maintenance operations |
| Simple to manage — recommended for most partitioned tables | Useful for global uniqueness (e.g. primary key) across all partitions |
-- 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
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)