Interval partitioning extends range partitioning with automatic partition creation — the database creates new partitions as data arrives outside the defined range. Reference partitioning allows a child table to inherit the partitioning of its parent via a foreign key, ensuring rows in related tables are always co-located in the same partition.

Interval Partitioning

How Automatic Partition Creation Works

An interval-partitioned table defines a seed partition (at minimum one VALUES LESS THAN partition) and an interval clause. When an INSERT or MERGE places a row in a value range that has no existing partition, Oracle automatically creates the partition.

CREATE TABLE sensor_readings (
  reading_id   NUMBER       GENERATED ALWAYS AS IDENTITY,
  sensor_id    NUMBER       NOT NULL,
  reading_ts   TIMESTAMP    NOT NULL,
  temperature  NUMBER(6,2),
  humidity     NUMBER(5,2)
)
PARTITION BY RANGE (reading_ts)
INTERVAL (INTERVAL '1' DAY)   -- one partition per day
(
  PARTITION p_bootstrap VALUES LESS THAN (TIMESTAMP '2026-01-01 00:00:00')
);

The first INSERT with reading_ts = '2026-03-01 14:32:00' automatically creates a partition covering that day. No DBA action required.

Interval Granularities

-- Monthly partitions
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))

-- Weekly partitions
INTERVAL (INTERVAL '7' DAY)

-- Quarterly partitions
INTERVAL (NUMTOYMINTERVAL(3, 'MONTH'))

-- Hourly partitions (high-frequency IoT)
INTERVAL (INTERVAL '1' HOUR)

Inspecting Auto-Created Partitions

SELECT partition_name,
       partition_position,
       high_value,
       num_rows,
       blocks
FROM dba_tab_partitions
WHERE table_name = 'SENSOR_READINGS'
  AND table_owner = 'SCHEMA1'
ORDER BY partition_position;

Auto-created partitions have system-generated names (SYS_P123). For clarity in operations, you can rename them:

ALTER TABLE sensor_readings RENAME PARTITION SYS_P456 TO p_2026_03_01;

Partition Lifecycle Management with Interval Tables

The key operational challenge with interval partitioning is lifecycle management — old partitions accumulate. Automate archival and purging:

-- Archive old data before dropping
INSERT /*+ APPEND */ INTO sensor_readings_archive
SELECT * FROM sensor_readings
PARTITION (p_2024_01_01);
COMMIT;

-- Drop old partition (instantaneous — metadata only)
ALTER TABLE sensor_readings DROP PARTITION p_2024_01_01 UPDATE GLOBAL INDEXES;

-- Or drop multiple partitions at once (Oracle 12.2+)
ALTER TABLE sensor_readings DROP PARTITION FOR (TIMESTAMP '2024-01-01 00:00:00'),
                                  PARTITION FOR (TIMESTAMP '2024-01-02 00:00:00')
UPDATE GLOBAL INDEXES;

Reference Partitioning

The Problem It Solves

In a classic order/order_lines model, if ORDERS is partitioned by order_date, queries joining ORDER_LINES to ORDERS still need to access all of ORDER_LINES — the child table is unpartitioned. Reference partitioning solves this by partitioning the child table using the parent’s partition key via the foreign key, without physically storing the partition key in the child table.

-- Parent: orders partitioned by order_date
CREATE TABLE orders (
  order_id    NUMBER PRIMARY KEY,
  order_date  DATE,
  customer_id NUMBER,
  status      VARCHAR2(20)
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_bootstrap VALUES LESS THAN (DATE '2024-01-01')
);

-- Child: order_lines partitioned by reference to orders
CREATE TABLE order_lines (
  line_id     NUMBER,
  order_id    NUMBER NOT NULL,
  product_id  NUMBER,
  quantity    NUMBER,
  unit_price  NUMBER,
  CONSTRAINT fk_order_lines_orders
    FOREIGN KEY (order_id) REFERENCES orders (order_id)
)
PARTITION BY REFERENCE (fk_order_lines_orders);

ORDER_LINES is now automatically partitioned using the same partition key as ORDERS.ORDER_DATE, via the foreign key. When a new month partition is created in ORDERS, the corresponding partition is created in ORDER_LINES automatically.

Query Benefits: Partition-Wise Joins

When queries join ORDERS and ORDER_LINES with a predicate on ORDER_DATE, both tables are pruned to the same partition(s). Oracle can then use a partial partition-wise join or full partition-wise join, dramatically reducing join overhead for large tables:

SELECT o.order_id, o.customer_id, SUM(l.quantity * l.unit_price) AS total
FROM orders o
JOIN order_lines l ON o.order_id = l.order_id
WHERE o.order_date >= DATE '2026-01-01'
  AND o.order_date <  DATE '2026-04-01'
GROUP BY o.order_id, o.customer_id;

-------------------------------------------------------------------------------------
| Id | Operation                    | Name        | Pstart | Pstop |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |             |        |       |
|  1 |  HASH GROUP BY               |             |        |       |
|  2 |   PARTITION RANGE ITERATOR   |             |     37 |    39 |
|  3 |    HASH JOIN                 |             |        |       |
|  4 |     TABLE ACCESS FULL        | ORDERS      |     37 |    39 |
|  5 |     TABLE ACCESS FULL        | ORDER_LINES |     37 |    39 |
-------------------------------------------------------------------------------------

Both tables are pruned to partitions 37–39 (January–March 2026). The join happens within co-located partition pairs — no cross-partition data movement.

Reference Partitioning: Key Constraints

  • The foreign key must be enabled and validated (NOT DEFERRABLE).
  • The parent table must be partitioned.
  • The child table’s partitioning is entirely derived from the parent — you cannot independently partition the child.
  • Cascades: when a parent partition is dropped, the corresponding child partition is dropped automatically (if CASCADE is specified).
-- Drop parent and child partition together
ALTER TABLE orders DROP PARTITION p_2024_01 CASCADE;
-- Also drops the corresponding ORDER_LINES partition automatically

Multi-Level Reference Partitioning

Reference partitioning chains across multiple levels:

-- Grandchild table: line_items_audit partitioned via order_lines → orders
CREATE TABLE line_audit (
  audit_id    NUMBER,
  line_id     NUMBER NOT NULL,
  change_ts   TIMESTAMP,
  changed_by  VARCHAR2(30),
  CONSTRAINT fk_audit_lines
    FOREIGN KEY (line_id) REFERENCES order_lines (line_id)
)
PARTITION BY REFERENCE (fk_audit_lines);

LINE_AUDIT now inherits the same monthly partitioning as ORDERS, two levels up the foreign key chain. All three tables are co-partitioned — any join across all three benefits from partition-wise join and full pruning with a single ORDER_DATE predicate.

Operational Considerations

Statistics on reference-partitioned tables: Gather statistics at the partition level using DBMS_STATS.GATHER_TABLE_STATS with granularity => 'ALL'. Reference partitioning does not change statistics gathering — each partition is analysed independently.

Global indexes on reference-partitioned tables: Global indexes work normally on reference-partitioned tables. Local indexes (one segment per partition) are generally preferred for the same reasons as with range-partitioned tables.

Data Guard compatibility: Reference partitioning is fully compatible with Data Guard. Partition DDL (drop, exchange) is replicated via redo.