Partitioning - Expert Oracle Database Architecture - page 587

• Interval reference partitioning : As the name implies, this is a combination of interval and
reference partitioning. This partitioning type is available starting with Oracle 12 c . It allows for
the automatic adding of partitions to parent/child reference partitioned tables.

• Virtual column partitioning : This allows partitioning on an expression based on one or more
existing columns of the table. The expression is stored as metadata only.

• Composite partitioning : This is a combination of range, hash, and list partitioning. It allows
you to first apply one partitioning scheme to some data, and then within each resulting
partition, have that partition subdivided into subpartitions using some other partitioning
scheme.

• System partitioning : The application determines which partition a row is explicitly inserted
into. This partitioning type has limited uses and won't be covered in this chapter; we only
mention it here to complete the list of partition types that Oracle supports. For more details on
system partitioning, see the Oracle Database Cartridge Developer's Guide.

In the following sections, we'll look at the benefits of each type of partitioning and at the differences between
them. We'll also look at when to apply which schemes to different application types. This section is not intended
to present a comprehensive demonstration of the syntax of partitioning and all of the available options. Rather, the
examples are simple and illustrative, and designed to give you an overview of how partitioning works and how the
different types of partitioning are designed to function.

■ For full details on partitioning syntax, I refer you to either the Oracle Database SQL Language Reference manual
or to Oracle Database Administrator's Guide . additionally, the Oracle Database VLDB and Partitioning Guide and Oracle
Database Data Warehousing Guide are both excellent sources of information on the partitioning options and are
must-reads for anyone planning to implement partitioning.

Note

Range Partitioning
The first type we will look at is a range partitioned table. The following CREATE TABLE statement creates a range
partitioned table using the column RANGE_KEY_COLUMN . All data with a RANGE_KEY_COLUMN strictly less than
01-JAN-2014 will be placed into the partition PART_1 , and all data with a value strictly less than 01-JAN-2015
(and greater than or equal to 01-JAN-2014 ) will go into partition PART_2 . Any data not satisfying either of those
conditions (e.g., a row with a RANGE_KEY_COLUMN value of 01-JAN-2015 or greater) will fail upon insertion, as it
cannot be mapped to a partition:

EODA@ORA12CR1> CREATE TABLE range_example
2 ( range_key_column date NOT NULL,
3 data varchar2(20)
4 )
5 PARTITION BY RANGE (range_key_column)
6 ( PARTITION part_1 VALUES LESS THAN
7 (to_date('01/01/2014','dd/mm/yyyy')),
8 PARTITION part_2 VALUES LESS THAN
9 (to_date('01/01/2015','dd/mm/yyyy'))
10 )
11 /

Table created.