Database Reference
In-Depth Information
Composite Partitioning
There's little that can be said about composite partitioning. Basically, everything that applies at the partition level
applies at the subpartition level as well. Nevertheless, it makes sense to illustrate at least one example. The following
test table is partitioned by range (based on the
d1
column) and subpartitioned by list (based on the
n1
column).
The following SQL statement, which is an excerpt of the
pruning_composite.sql
script, was used to create the table.
Note that also in this case, there are 48 partitions per year:
CREATE TABLE t (
id NUMBER,
d1 DATE,
n1 NUMBER,
n2 NUMBER,
n3 NUMBER,
pad VARCHAR2(4000),
CONSTRAINT t_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (d1)
SUBPARTITION BY LIST (n1)
SUBPARTITION TEMPLATE (
SUBPARTITION sp_1 VALUES (1),
SUBPARTITION sp_2 VALUES (2),
SUBPARTITION sp_3 VALUES (3),
SUBPARTITION sp_4 VALUES (4)
)(
PARTITION t_jan_2014 VALUES LESS THAN (to_date('2014-02-01','YYYY-MM-DD')),
PARTITION t_feb_2014 VALUES LESS THAN (to_date('2014-03-01','YYYY-MM-DD')),
PARTITION t_mar_2014 VALUES LESS THAN (to_date('2014-04-01','YYYY-MM-DD')),
PARTITION t_apr_2014 VALUES LESS THAN (to_date('2014-05-01','YYYY-MM-DD')),
PARTITION t_may_2014 VALUES LESS THAN (to_date('2014-06-01','YYYY-MM-DD')),
PARTITION t_jun_2014 VALUES LESS THAN (to_date('2014-07-01','YYYY-MM-DD')),
PARTITION t_jul_2014 VALUES LESS THAN (to_date('2014-08-01','YYYY-MM-DD')),
PARTITION t_aug_2014 VALUES LESS THAN (to_date('2014-09-01','YYYY-MM-DD')),
PARTITION t_sep_2014 VALUES LESS THAN (to_date('2014-10-01','YYYY-MM-DD')),
PARTITION t_oct_2014 VALUES LESS THAN (to_date('2014-11-01','YYYY-MM-DD')),
PARTITION t_nov_2014 VALUES LESS THAN (to_date('2014-12-01','YYYY-MM-DD')),
PARTITION t_dec_2014 VALUES LESS THAN (to_date('2015-01-01','YYYY-MM-DD'))
)
Figure
13-9
is a graphical representation of this test table. If you compare it with the previous one (see Figure
13-5
),
the only difference is that no single value identifies the position of subpartitions throughout the table. In fact, the
position of a subpartition is based on its “parent” partition.
