The value of the state code then determines which of the three subpartitions will be
used to store the row. If a sales date falls beyond the end of 2007, it will still be placed in one of
the subpartitions of Q4_2007 until you create a new partition and subpartitions for Q1_2008.
Reorganizing partitioned tables is covered later in this chapter.
Using Composite List-Hash, List-List, and List-Range Partitioning Using list-hash, list-list,
and list-range composite partitioning is similar to using range-hash, range-list, and range-range
partitioning as discussed earlier in this section, except that you use the partition by list clause
instead of the partition by range clause as the primary partitioning strategy.
NOTE
Composite list-hash partitioning and all subsequent partitioning
methods in this chapter are new as of Oracle 11g.
As an example, I??™ll recreate the sales_by_region_by_quarter table (which uses a range-list
scheme) using a list-range partitioning scheme instead, as follows:
create table sales_by_region_by_quarter
(state_cd char(2),
sales_dt date,
sales_amt number(16,2))
partition by list (state_cd)
subpartition by range(sales_dt)
(partition midwest values ('WI','IL','IA','IN','MN')
(
subpartition midwest_q1_2007 values less than
(to_date('20070401','YYYYMMDD')),
subpartition midwest_q2_2007 values less than
(to_date('20070701','YYYYMMDD')),
subpartition midwest_q3_2007 values less than
(to_date('20071001','YYYYMMDD')),
subpartition midwest_q4_2007 values less than (maxvalue)
),
partition westcoast values ('CA','OR','WA')
(
subpartition westcoast_q1_2007 values less than
(to_date('20070401','YYYYMMDD')),
566 Oracle Database 11g DBA Handbook
subpartition westcoast_q2_2007 values less than
(to_date('20070701','YYYYMMDD')),
subpartition westcoast_q3_2007 values less than
(to_date('20071001','YYYYMMDD')),
subpartition westcoast_q4_2007 values less than (maxvalue)
),
partition other_states values (default)
(
subpartition other_states_q1_2007 values less than
(to_date('20070401','YYYYMMDD')),
subpartition other_states_q2_2007 values less than
(to_date('20070701','YYYYMMDD')),
subpartition other_states_q3_2007 values less than
(to_date('20071001','YYYYMMDD')),
subpartition other_states_q4_2007 values less than (maxvalue)
)
);
This alternate partitioning scheme makes sense if the regional managers perform their
analyses by date only within their regions.
Pages:
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845