Partitioning: Range, Hash and List

Why Partition

Partitioning divides a table into smaller segments with the same logical definition. Queries touch only matching partitions, and maintenance runs per partition.

Range Partition

CREATE TABLE sales (
  sale_id NUMBER, sale_date DATE, amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_q1 VALUES LESS THAN (DATE '2026-04-01'),
  PARTITION p_q2 VALUES LESS THAN (DATE '2026-07-01'),
  PARTITION p_q3 VALUES LESS THAN (DATE '2026-10-01')
);

Hash and List

CREATE TABLE events (id NUMBER, payload CLOB)
PARTITION BY HASH (id) PARTITIONS 8;

CREATE TABLE regions (city VARCHAR2(30))
PARTITION BY LIST (city) (
  PARTITION p_north VALUES ('Pune', 'Mumbai'),
  PARTITION p_south VALUES ('Chennai', 'Bangalore')
);

Partition Maintenance

ALTER TABLE sales ADD PARTITION p_q4 VALUES LESS THAN (DATE '2027-01-01');
ALTER TABLE sales DROP PARTITION p_q1;

Key Points

• Use range for dates, hash for distribution, list for categories.
• Add and drop partitions without touching every row.
• Partition elimination skips irrelevant segments.
• Local indexes align with partitions on large tables.