Oracle9i New Feature Series: Range-List partitioning

Prior to Oracle9i, the only sub-partitioning method available was to partition the table by range, and sub-partition by hash. Oracle9i release 2 allows you to partition the table by range and then sub-partition by list. These are the only sub-partitioning methods in Oracle9i.

The following are the versions and partitioning methods available, just to refresh your memory: 

Here is an example of creating a range-list partitioned table:

create table range_list_example (

sales_dt date,

state char(2),

amount number)

partition by range (sales_dt)

subpartition by list (state)

(

partition s2004q1

values less than (to_date('04-2004','MM-YYYY'))

(subpartition s2004q1_south values  ('TX','LA','OK'),

subpartition s2004q1_north values ('NY','DE','MA'),

subpartition s2004q1_others values (DEFAULT)),

partition s2004q2

values less than (to_date('07-2004','MM-YYYY'))

(subpartition s2004q2_south values  ('TX','LA','OK'),

subpartition s2004q2_north values ('NY','DE','MA'),

subpartition s2004q2_others values (DEFAULT))

)

/

The same sub-partitions can be created using a sub-partition template as in the following example:

create table range_list_example (

sales_dt date,

state char(2),

amount number)

partition by range (sales_dt)

subpartition by list (state)

subpartition template

(subpartition south values  ('TX','LA','OK'),

subpartition north values ('NY','DE','MA'),

subpartition others values (DEFAULT))

(

partition s2004q1

values less than (to_date('04-2004','MM-YYYY')),

partition s2004q2

values less than (to_date('07-2004','MM-YYYY'))

)

/