Oracle Table Partitioning: Strategies for Large Datasets

How partitioning works

graph TD
    T[SALES Table - 500M rows] --> P1[Partition p_2024<br/>Jan-Dec 2024<br/>~120M rows]
    T --> P2[Partition p_2025<br/>Jan-Dec 2025<br/>~180M rows]
    T --> P3[Partition p_2026<br/>Jan-Dec 2026<br/>~200M rows]
    
    Q[Query: WHERE sale_date >= 2026-01-01] -->|Partition Pruning| P3
    Q -.->|Skipped entirely| P1
    Q -.->|Skipped entirely| P2

    style T fill:#1e293b,stroke:#3B82F6,color:#e2e8f0
    style P3 fill:#0f2d1f,stroke:#22C55E,color:#e2e8f0
    style P1 fill:#1e293b,stroke:#475569,color:#94A3B8
    style P2 fill:#1e293b,stroke:#475569,color:#94A3B8
    style Q fill:#1e3a5f,stroke:#60A5FA,color:#e2e8f0

Oracle scans only p_2026 — skipping 320M rows entirely. That’s partition pruning.

Range partitioning — most common

CREATE TABLE sales (
  sale_id     NUMBER,
  sale_date   DATE,
  customer_id NUMBER,
  amount      NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'),
  PARTITION p_2025 VALUES LESS THAN (DATE '2026-01-01'),
  PARTITION p_2026 VALUES LESS THAN (MAXVALUE)
);

Verify partition pruning with EXPLAIN PLAN:

EXPLAIN PLAN FOR
SELECT SUM(amount)
FROM sales
WHERE sale_date BETWEEN DATE '2026-01-01' AND DATE '2026-03-31';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Look for: PARTITION RANGE SINGLE or PARTITION RANGE ITERATOR
-- NOT: PARTITION RANGE ALL (means no pruning!)

Interval partitioning — auto-creates partitions

Eliminates manual partition management:

CREATE TABLE events (
  event_id    NUMBER,
  event_date  DATE,
  payload     CLOB
)
PARTITION BY RANGE (event_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_initial VALUES LESS THAN (DATE '2024-01-01')
);

-- Oracle auto-creates SYS_P1234 partitions as new months arrive
-- No DBA intervention needed

Choosing the right strategy

flowchart TD
    Q1{Is data date-based?} -->|Yes| Q2{New data arrives regularly?}
    Q1 -->|No| Q3{Discrete categories?}
    Q2 -->|Yes| IV[Interval Partitioning
Auto monthly/yearly]
    Q2 -->|No, historical only| RNG[Range Partitioning
Manual boundaries]
    Q3 -->|Yes, <20 values| LST[List Partitioning
By country, region, status]
    Q3 -->|No, uniform distribution| HSH[Hash Partitioning
By primary key]

    style IV fill:#0f2d1f,stroke:#22C55E,color:#e2e8f0
    style RNG fill:#1e3a5f,stroke:#3B82F6,color:#e2e8f0
    style LST fill:#1e293b,stroke:#F59E0B,color:#e2e8f0
    style HSH fill:#2d1f0f,stroke:#F97316,color:#e2e8f0

Partition maintenance operations

-- Drop old partition instantly (vs DELETE which logs every row):
ALTER TABLE sales DROP PARTITION p_2024;

-- Compress & move old partition to cheaper tablespace:
ALTER TABLE sales MOVE PARTITION p_2024
  TABLESPACE ts_archive COMPRESS;

-- Split a large partition into two:
ALTER TABLE sales SPLIT PARTITION p_2026
  AT (DATE '2026-07-01')
  INTO (PARTITION p_2026_h1, PARTITION p_2026_h2);

-- Check partition sizes:
SELECT partition_name, num_rows, blocks,
       ROUND(blocks * 8192 / 1024 / 1024, 2) AS size_mb
FROM user_tab_partitions
WHERE table_name = 'SALES'
ORDER BY partition_position;

When NOT to partition

• Tables under 1M rows — overhead exceeds benefit
• Tables accessed exclusively via primary key — no pruning possible
• High-concurrency OLTP with short-running DML — partition overhead adds latency