Licensing first
Partitioning and Advanced Compression (OLTP/advanced row compression) are extra-cost options for Enterprise Edition on-premises and on cloud VMs. Some Oracle cloud services include them. Confirm with your licensing team before using them.
When partitioning helps
| Symptom | Partitioning benefit |
|---|---|
| Queries filter by date range on a huge table | Partition pruning reads only relevant partitions |
Monthly purge with DELETE takes hours and generates huge redo | ALTER TABLE ... DROP PARTITION is near instant |
| Backups of static history every day | Read-only tablespaces for old partitions back up once |
| Index maintenance on giant indexes | Local indexes maintained per partition |
Interval partitioning example
CREATE TABLE sales.orders_p (
order_id NUMBER PRIMARY KEY,
order_date DATE NOT NULL,
customer_id NUMBER,
amount NUMBER(12,2)
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION p_before_2024 VALUES LESS THAN (DATE '2024-01-01'));
Choose the partition key from how queries filter and how data ages. A primary key not including the partition key needs a global index; plan index strategy (local vs global) up front.
Convert an existing table online
ALTER TABLE sales.orders MODIFY
PARTITION BY RANGE (order_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION p_before_2024 VALUES LESS THAN (DATE '2024-01-01'))
ONLINE
UPDATE INDEXES;
Test on a copy: time, undo/redo and space needs.
Compression by data age
| Data age | Option |
|---|---|
| Hot (current months, updated) | No compression or advanced row compression |
| Warm (read-mostly) | Advanced row compression |
| Cold (history, read-only) | Basic/hybrid columnar compression where available (Exadata/cloud), read-only tablespace |
ALTER TABLE sales.orders_p MOVE PARTITION SYS_P1234 ROW STORE COMPRESS ADVANCED ONLINE UPDATE INDEXES;
Automatic Data Optimization (Heat Map + ILM policies) can compress or move partitions by age automatically (licensing applies).
Purging
ALTER TABLE sales.orders_p DROP PARTITION FOR (DATE '2019-01-15') UPDATE GLOBAL INDEXES;
Measure
- Query elapsed time and buffer gets for key reports (AWR/SQL Monitor).
- Segment sizes before/after compression.
- Purge duration and redo generated.
Tip
Start with the single largest table that has a clear date access pattern and a purge requirement. The drop-partition purge alone usually justifies the work.
Checklist
- Licensing confirmed
- Partition key from query and ageing patterns
- Local/global index strategy decided
- Online conversion tested on a copy
- Compression tiers by data age
- Purge by partition drop; results measured