Lastly, we’ll look at some examples of composite partitioning, which is a mixture of range, hash, and/or list. Table 13-1 lists the various ways you can mix and match types of partitioning. In other words, the table shows there are nine different combinations of composite partitioning currently available.

Table 13-1.  Oracle Database Supported Composite Partitioning Schemes by Version

It is interesting to note that when you use composite partitioning, there will be no partition segments; there will be only subpartition segments.
When using composite partitioning, the partitions themselves do not have segments (much like a partitioned table doesn’t have a segment).
The data is physically stored in subpartition segments, and the partition becomes a logical container, or a container that points to the actual subpartitions.

In our example, we’ll look at a range-hash composite partitioning. Here, we are using a different set of columns for the range partition from those used for the hash partition. This is not mandatory; we could use the same set of columns for both:
$ sqlplus eoda/foo@PDB1
SQL> CREATE TABLE composite_example
( range_key_column date,hash_key_column int,data varchar2(20))PARTITION BY RANGE (range_key_column)subpartition by hash(hash_key_column) subpartitions 2

PARTITION part_1
VALUES LESS THAN(to_date(’01/01/2021′,’dd/mm/yyyy’))
(subpartition part_1_sub_1,subpartition part_1_sub_2),

PARTITION part_2
VALUES LESS THAN(to_date(’01/01/2022′,’dd/mm/yyyy’))
(subpartition part_2_sub_1,subpartition part_2_sub_2));Table created.

In range-hash composite partitioning, Oracle will first apply the range partitioning rules to figure out which range the data falls into.
Then it will apply the hash function to decide into which physical partition the data should finally be placed. This process is described in Figure 13-4.

Figure 13-4.  Range-hash composite partition example

So, composite partitioning gives you the ability to break your data up by range and, when a given range is considered too large or further partition elimination could be useful, to break it up further by hash or list. It is interesting to note that each range partition need not have the same number of subpartitions; for example, suppose you were range partitioning on a date column in support of data purging (to remove all old data rapidly and easily).

In the year 2020 and before, you had equal amounts of data in odd code numbers in the CODE_KEY_COLUMN and in even code numbers. But after that, you knew the number of records associated with the odd code number was more than double, and you wanted to have more subpartitions for the odd code values.

You can achieve that rather easily just by defining more subpartitions:
SQL> CREATE TABLE composite_range_list_example ( range_key_column date,code_key_column int,data varchar2(20))PARTITION BY RANGE (range_key_column)subpartition by list(code_key_column)

PARTITION part_1
VALUES LESS THAN(to_date(’01/01/2021′,’dd/mm/yyyy’)) (subpartition part_1_sub_1 values( 1, 3, 5, 7 ), subpartition part_1_sub_2 values( 2, 4, 6, 8 )),

PARTITION part_2
VALUES LESS THAN(to_date(’01/01/2022′,’dd/mm/yyyy’)) (subpartition part_2_sub_1 values ( 1, 3 ), subpartition part_2_sub_2 values ( 5, 7 ), subpartition part_2_sub_3 values ( 2, 4, 6, 8 )));Table created.

Here, you end up with five partitions altogether: two subpartitions for partition PART_1 and three for partition PART_2.