Partitioning - Expert Oracle Database Architecture - page 610

Here's a partial snippet of the output:

RESERVATION_CODE
------------------------------
C1
D2
...
A72827
B72828

In this scenario, we know that the first character represents the region and we want to be able to list partition
by region. With the data as it is, it's not practical to list partition by the RESERVATION_CODE column. Whereas virtual
partitioning allows us to apply a SQL function to the column and list partition by the first character. Here's what the
table definition looks like with virtual column partitioning:

EODA@ORA12CR1> create table res(
reservation_code varchar2(30),
region as
(decode(substr(reservation_code,1,1),'A','NE'
,'C','NE'
,'B','SW'
,'D','NW')
)
)
partition by list (region)
(partition p1 values('NE'),
partition p2 values('SW'),
partition p3 values('NW'));
Table created.

In this way, virtual column partitioning is often appropriate when there is a business requirement to partition on
portions of data in a column, or combinations of data from different columns (especially when there might not be an
obvious way to list or range partition). The expression behind a virtual column can be a complex calculation, return a
subset of a column string, combinations of column values, and so on.

Composite Partitioning
Lastly, we'll look at some examples of composite partitioning, which is a mixture of range, hash, and/or list. The
methods by which you can composite partition, that is the types of partitioning schemes you can mix and match,
varies by release. Table 13-1 lists what is available in each of the major releases. The partitioning scheme listed down
the table is the top level partitioning scheme permitted, whereas the partitioning scheme listed across the table is the
subpartition—the partition within the partition—scheme.

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

Range List Hash

Range 11 g Release 1 9 i Release 2 9 i Release 1

List 11 g Release 1 11 g Release 1 11 g Release 1

Hash 11 g Release 2 11 g Release 2 11 g Release 2