Using Composite Range-Range Partitioning As the name implies, the range-range partitioning
method uses a range of values in two table columns. Both columns would otherwise lend themselves
to a range-partitioned table, but the columns do not need to have the same datatype. For example,
a medical analysis table can use a primary range column of patient birth date, and a secondary
range column of patient birth weight in ounces. Here is an example of a patient table using these
two attributes:
create table patient_info
(patient_id number,
birth_date date,
birth_weight_oz number)
partition by range (birth_date)
subpartition by range (birth_weight_oz)
(
partition bd_1950 values less than (to_date('19501231','YYYYMMDD'))
(
subpartition bd_1950_4lb values less than (64),
subpartition bd_1950_6lb values less than (96),
subpartition bd_1950_8lb values less than (128),
subpartition bd_1950_12lb values less than (192),
subpartition bd_1950_o12lb values less than (maxvalue)
),
partition bd_1960 values less than (to_date('19601231','YYYYMMDD'))
(
subpartition bd_1960_4lb values less than (64),
subpartition bd_1960_6lb values less than (96),
subpartition bd_1960_8lb values less than (128),
subpartition bd_1960_12lb values less than (192),
subpartition bd_1960_o12lb values less than (maxvalue)
),
Chapter 16: Managing Large Databases 567
partition bd_1970 values less than (to_date('19701231','YYYYMMDD'))
(
subpartition bd_1970_4lb values less than (64),
subpartition bd_1970_6lb values less than (96),
subpartition bd_1970_8lb values less than (128),
subpartition bd_1970_12lb values less than (192),
subpartition bd_1970_o12lb values less than (maxvalue)
),
partition bd_1980 values less than (to_date('19801231','YYYYMMDD'))
(
subpartition bd_1980_4lb values less than (64),
subpartition bd_1980_6lb values less than (96),
subpartition bd_1980_8lb values less than (128),
subpartition bd_1980_12lb values less than (192),
subpartition bd_1980_o12lb values less than (maxvalue)
),
partition bd_1990 values less than (to_date('19901231','YYYYMMDD'))
(
subpartition bd_1990_4lb values less than (64),
subpartition bd_1990_6lb values less than (96),
subpartition bd_1990_8lb values less than (128),
subpartition bd_1990_12lb values less than (192),
subpartition bd_1990_o12lb values less than (maxvalue)
),
partition bd_2000 values less than (to_date('20001231','YYYYMMDD'))
(
subpartition bd_2000_4lb values less than (64),
subpartition bd_2000_6lb values less than (96),
subpartition bd_2000_8lb values less than (128),
subpartition bd_2000_12lb values less than (192),
subpartition bd_2000_o12lb values less than (maxvalue)
),
partition bd_2010 values less than (maxvalue)
(
subpartition bd_2010_4lb values less than (64),
subpartition bd_2010_6lb values less than (96),
subpartition bd_2010_8lb values less than (128),
subpartition bd_2010_12lb values less than (192),
subpartition bd_2010_o12lb values less than (maxvalue)
)
);
Using Interval Partitioning Interval partitioning automates the creation of new range partitions.
Pages:
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846