Table Partitioning: Range, List, Hash and Interval

Oracle DBA Certification 15 min min read Updated: Nov 16, 2026 Advanced
Table Partitioning: Range, List, Hash and Interval
Advanced Topic 2 of 3

What is Partitioning?

Partitioning splits one large table into smaller pieces called partitions. To the application it is still one table, but Oracle can work on just the pieces it needs.

BenefitHow
Faster queriesPartition pruning skips partitions that cannot contain the answer
Easy cleanupDrop or truncate a whole old partition instantly
Smaller maintenanceBack up, move or rebuild one partition at a time
Licence: Partitioning is an option of Enterprise Edition. Check the licence for your edition before using it in production.

Range Partitioning with Interval

Best for dates. Interval partitioning creates new monthly partitions automatically as data arrives.

sql CREATE TABLE shop.sales_hist ( sale_id NUMBER, sale_date DATE NOT NULL, region VARCHAR2(10), amount NUMBER(10,2) ) PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION p_before_2026 VALUES LESS THAN (DATE '2026-01-01') );

List Partitioning

sql CREATE TABLE shop.customers_region ( customer_id NUMBER, full_name VARCHAR2(60), region VARCHAR2(10) ) PARTITION BY LIST (region) ( PARTITION p_north VALUES ('NORTH'), PARTITION p_south VALUES ('SOUTH'), PARTITION p_other VALUES (DEFAULT) );

Hash Partitioning

sql CREATE TABLE shop.big_log ( log_id NUMBER, message VARCHAR2(200) ) PARTITION BY HASH (log_id) PARTITIONS 4;
MethodUse when
Range / IntervalTime-based data (orders, logs, sales)
ListData split by a known category (region, status)
HashYou want even spread and no natural range

See the Partitions

sql SELECT partition_name, high_value, num_rows FROM dba_tab_partitions WHERE table_owner = 'SHOP' AND table_name = 'SALES_HIST' ORDER BY partition_position;

Partition Pruning

sql SELECT SUM(amount) FROM shop.sales_hist WHERE sale_date >= DATE '2026-03-01' AND sale_date < DATE '2026-04-01';

The execution plan shows PARTITION RANGE SINGLE. Oracle read only March, not the whole table.

Always filter on the partition key: If your query does not use sale_date, Oracle must read every partition and you lose the benefit.

Local Indexes

sql CREATE INDEX idx_sales_region ON shop.sales_hist (region) LOCAL;

A local index is split the same way as the table, so dropping a partition does not invalidate the whole index.

Everyday Maintenance

sql -- remove old data instantly ALTER TABLE shop.sales_hist DROP PARTITION p_before_2026 UPDATE INDEXES; -- empty one partition ALTER TABLE shop.sales_hist TRUNCATE PARTITION p_before_2026; -- add a range partition (needed for non-interval tables) -- ALTER TABLE t ADD PARTITION p_2027 VALUES LESS THAN (DATE '2028-01-01');
Why this is powerful: Deleting 100 million old rows with DELETE takes hours and fills undo and redo. DROP PARTITION takes a second.

Conclusion

Partitioning keeps huge tables manageable. Use range or interval for dates, always filter on the partition key and use local indexes.

Quick Summary: Partition by date, filter by the key to get pruning, drop old partitions instead of deleting.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators