Partitioning

53.1 Benefits

Performance: partition pruning, partition-wise joins.

Manageability: partition-level maintenance, fast load and archive (exchange/drop), per-partition compression and tablespaces.

Availability: partition independence.

An Enterprise Edition option (licensed separately).

53.2 Partitioning Strategies

-- RANGE with INTERVAL (auto-creates partitions)
CREATE TABLE sales (
    sale_id NUMBER, sale_date DATE NOT NULL, region VARCHAR2(10), amount NUMBER(12,2)
)
PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION p_before_2024 VALUES LESS THAN (DATE '2024-01-01'));

-- LIST (AUTOMATIC list partitioning in 12.2)
CREATE TABLE customers_part (id NUMBER, country VARCHAR2(30))
PARTITION BY LIST (country) AUTOMATIC
(PARTITION p_india VALUES ('India'), PARTITION p_usa VALUES ('USA'), PARTITION p_other VALUES (DEFAULT));

-- HASH
CREATE TABLE sessions_part (session_id NUMBER, data VARCHAR2(100))
PARTITION BY HASH (session_id) PARTITIONS 16;   -- use powers of 2

-- Composite: RANGE-HASH, RANGE-LIST, LIST-LIST, ... (all combinations)
CREATE TABLE orders_part (order_id NUMBER, order_date DATE, region VARCHAR2(10))
PARTITION BY RANGE (order_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY LIST (region) SUBPARTITION TEMPLATE
  (SUBPARTITION sp_n VALUES ('NORTH'), SUBPARTITION sp_s VALUES ('SOUTH'), SUBPARTITION sp_o VALUES (DEFAULT))
(PARTITION p0 VALUES LESS THAN (DATE '2024-01-01'));

-- REFERENCE (child inherits the parent's partitioning via FK)
CREATE T
…

53.3 Partition Maintenance

Asynchronous global index maintenance (12c), filtered partition maintenance, read-only partitions (12.2).

ALTER TABLE sales ADD PARTITION p_2026_01 VALUES LESS THAN (DATE '2026-02-01');   -- range (non-interval)
ALTER TABLE sales DROP PARTITION p_before_2024 UPDATE GLOBAL INDEXES;
ALTER TABLE sales TRUNCATE PARTITION FOR (DATE '2024-03-15');
ALTER TABLE sales SPLIT PARTITION p_before_2024 AT (DATE '2023-01-01') INTO (PARTITION p_2022, PARTITION p_2023);
ALTER TABLE sales MERGE PARTITIONS p_2022, p_2023 INTO PARTITION p_old;
ALTER TABLE sales MOVE PARTITION p_old TABLESPACE archive_ts COMPRESS FOR OLTP ONLINE;
ALTER TABLE sales EXCHANGE PARTITION p_old WITH TABLE sales_old_stage INCLUDING INDEXES WITHOUT VALIDATION;
CREATE TABLE sales_stage FOR EXCHANGE WITH TABLE sales;   -- 12.2
ALTER TABLE orders MODIFY PARTITION BY RANGE (order_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) (...) ONLINE;  -- 12.2 online conversion
SELECT * FROM sales PARTITION (p_old);
SELECT * FROM sales PARTITION FOR (DATE '2025-06-01');
SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions;

53.4 Partitioned Indexes

Local indexes are equi-partitioned with the table, which makes maintenance easy (prefixed or non-prefixed).

Global partitioned and global non-partitioned indexes need UPDATE INDEXES during partition maintenance.

Partial indexes: INDEXING ON/OFF per partition.

Partition-wise joins (full and partial).