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 is available in Oracle 12c and is a powerful and well-established feature used in many database applications. Oracle 12c has enhanced range partitioning compared to previous versions. It offers:

• Improved performance: Partitioning allows you to focus queries on specific data subsets, leading to faster execution times.
• Simplified management: Oracle 12c introduces features like interval partitioning and auto-list partitioning, making partition management easier than ever.
• Greater flexibility: You can even combine range partitioning with other partitioning methods like list partitioning or hash partitioning for even more granular control over your data.

Range partitioning remains a crucial tool in the Oracle 12c database toolbox. If you are working with large datasets or need to optimize query performance, consider using range partitioning for significant benefits.

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 sales2 VALUES LESS THAN 10,
 PARTITION sales2 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

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 time_id. Also assume you have two years and that each partition represents a quarter. Instead of using range partitioning, you can use composite partitioning to enable a full partition-wise join while preserving the partitioning on time_id. For example, partition the sales table by range on time_id and then subpartition each partition by hash on cust_id using 16 subpartitions for each partition, for a total of 128 subpartitions. The customers table can use hash partitioning with 16 partitions. When you use the method just described, a full partition-wise join works similarly to the one created by a single-level - 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 now each hash partition in the sales table is 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, for a total of 8 range partitions. Each range partition has 16 subpartitions. Each column corresponds to one hash partition for a total of 16 hash partitions; each hash partition has 8 subpartitions. Hash partitions can be defined only if all partitions have the same number of subpartitions, in this case, 16. 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 as you can range or list partitions.