Implement Table Range Partitioning in Oracle

The most basic type of partitioning for a table is called range partitioning. Range partitioning divides a table into partitions based on a range of values. You can use one or more columns to define the range specification for the partitions.
Range partitioning remains a foundational and well-established feature in Oracle Database, including current Oracle 23ai releases. It offers:

• Improved performance: partitioning lets queries target specific data subsets, leading to faster execution through partition pruning.
• Simplified management: interval partitioning lets Oracle create new range partitions automatically as new data arrives, so you no longer have to pre-define every future partition by hand.
• Greater flexibility: range partitioning can be combined with other partitioning methods, such as list or hash partitioning, for more granular control over how data is distributed.

Range partitioning remains one of the first techniques worth considering when working with large datasets or optimizing query performance.

There are two parts for the syntax used to create a range partitioned table:

1. The PARTITION BY RANGE statement, which is followed by the list of columns used to define the partition
2. The VALUES LESS THAN statements, which define the values used as the upper bound for inclusion in the partition

Oracle automatically uses the upper bound of the next lower VALUES LESS THAN value as the lower bound of a partition.

As an example, consider a table called YEARLY_SALES, which is partitioned based on quarters of the year. The statement used to define this table would be:

CREATE TABLE YEARLY_SALES(
 sales_month INTEGER,
 sales_amount NUMBER)
PARTITION BY RANGE (sales_month)
 (PARTITION sales1 VALUES LESS THAN (4),
 PARTITION sales2 VALUES LESS THAN (7),
 PARTITION sales3 VALUES LESS THAN (10),
 PARTITION sales4 VALUES LESS THAN (13));

You could assign each separate partition to its own tablespace at the end of the PARTITION clause, such as:

PARTITION sales2 VALUES LESS THAN (7) TABLESPACE Q3

Once a table is partitioned, Oracle's data dictionary views let you confirm how it was actually built rather than relying on the CREATE TABLE statement alone. USER_TAB_PARTITIONS lists every partition of a table you own, along with its high value (the upper bound expression), tablespace, and row and block statistics once they've been gathered:

SELECT partition_name, high_value, tablespace_name
FROM   user_tab_partitions
WHERE  table_name = 'YEARLY_SALES'
ORDER BY partition_position;

This is a useful first check any time a query against a partitioned table isn't pruning the way you expect: confirming the actual partition boundaries in the dictionary is faster than re-reading the original DDL, especially on a table that's had partitions added or split since it was first created.

Range-partitioned tables are rarely static once created. Oracle provides DDL to adjust the partition structure as data grows, without rebuilding the table:

• ALTER TABLE ... ADD PARTITION adds a new partition above the current highest VALUES LESS THAN boundary, for example to open a new quarter.
• ALTER TABLE ... SPLIT PARTITION divides an existing partition into two, useful when a single quarter has grown large enough to warrant finer-grained partitioning.
• ALTER TABLE ... MERGE PARTITIONS combines two adjacent partitions into one, typically used to consolidate older, less-active quarters.
• ALTER TABLE ... DROP PARTITION removes a partition and its data entirely, a fast way to purge an aged-out range once it's no longer needed.

For a table that grows on a predictable schedule, such as one new quarter every three months, interval partitioning removes the need for ADD PARTITION altogether: Oracle creates each new partition automatically the first time a row arrives that doesn't fit an existing range.

This method is a variation of the single-level method. In this scenario, one table (typically the larger table) is composite partitioned on two dimensions, using the join columns as the subpartition key. In the example, the sales table is a typical example of a table storing historical data. Using range partitioning is a logical initial partitioning method for a table storing historical information. For example, assume you want to partition the sales table into eight partitions by range on the column salesdate. Also assume you have two years and that each partition represents a quarter. Instead of using range partitioning alone, you can use composite partitioning to enable a full partition-wise join while preserving the partitioning on salesdate. Partition the sales table by range on salesdate, then subpartition each partition by hash on customerid using 16 subpartitions per partition, for a total of 128 subpartitions. The customers table can use hash partitioning with 16 partitions on customerid. With this method, a full partition-wise join works similarly to the one created by a single-level hash-hash method: the join is still divided into 16 smaller joins between hash partition pairs from both tables. The difference is that each hash partition in the sales table is now composed of a set of 8 subpartitions, one from each range partition.

Figure 2-4 illustrates how the hash partitions are formed in the sales table. Each cell represents a subpartition. Each row corresponds to one range partition, defined by salesdate, for a total of 8 range partitions covering two years of quarters. Each column corresponds to one hash partition, computed from customerid, for a total of 16 hash partitions; each hash partition spans 8 subpartitions. Hash partitions are implicit in a composite table. However, Oracle does not record them in the data dictionary, and you cannot manipulate them with DDL commands the way you can range or list partitions.