Range-List Partitioning

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: 

Oracle8

Oracle8i

Oracle9iR1

Oracle9iR2

Range

Range
Hash

Range-Hash

Range
Hash
List

Range-Hash

Range
Hash
List

Range-Hash
Range-List

 

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'))
)
/

 

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.