NOTE
Range-list partitioning is new as of Oracle 10g.
To use composite range-list partitioning, you must specify the following criteria:
Primary partitioning method (range)
Range partitioning column(s)
Partition names identifying the bounds of the partition
Subpartitioning method (list)
Subpartitioning column
Partition names, with each partition associated with a discrete list of literal values that
place it in the partition
In the following example, we will expand on the previous ???Sales by Region??? list partitioning
example and make the partitioned table more scalable by using the sales date for range
partitioning, and we will use the state code for subpartitioning. Here is the create table command
to accomplish this:
create table sales_by_region_by_quarter
(state_cd char(2),
sales_dt date,
sales_amt number(16,2))
partition by range (sales_dt)
subpartition by list (state_cd)
(partition q1_2007 values less than (to_date('20070401','YYYYMMDD'))
(subpartition q1_2007_midwest values ('WI','IL','IA','IN','MN')
tablespace prd01,
subpartition q1_2007_westcoast values ('CA','OR','WA')
tablespace prd02,
subpartition q1_2007_other_states values (default)
tablespace prd03
),
partition q2_2007 values less than (to_date('20070701','YYYYMMDD'))
(subpartition q2_2007_midwest values ('WI','IL','IA','IN','MN')
tablespace prd01,
subpartition q2_2007_westcoast values ('CA','OR','WA')
tablespace prd02,
subpartition q2_2007_other_states values (default)
tablespace prd03
),
partition q3_2007 values less than (to_date('20071001','YYYYMMDD'))
(subpartition q3_2007_midwest values ('WI','IL','IA','IN','MN')
tablespace prd01,
subpartition q3_2007_westcoast values ('CA','OR','WA')
tablespace prd02,
subpartition q3_2007_other_states values (default)
?–
?–
?–
?–
?–
?–
Chapter 16: Managing Large Databases 565
tablespace prd03
),
partition q4_2007 values less than (maxvalue)
(subpartition q4_2007_midwest values ('WI','IL','IA','IN','MN')
tablespace prd01,
subpartition q4_2007_westcoast values ('CA','OR','WA')
tablespace prd02,
subpartition q4_2007_other_states values (default)
tablespace prd03
)
);
Each row stored in the table SALES_BY_REGION_BY_QUARTER is placed into one of 12
subpartitions, depending first on the sales date, which narrows the subpartition choice to three
subpartitions.
Pages:
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844