Donald K. Burleson Oracle Tips

New Oracle list partitioning method

Oracle9i now has two methods for partitioning, range partitioning and the new list partitioning.  While range partitions required scalar numeric values, list partitioning allows partitioning by non-scalar data.

To see how list partitioning differs from range partitioning, let’s use a simple example. Here we see a range partitioned table whereby the area_code is used to partition the data.

Create table customer

(

   Last_name varchar2(30),

   area_code number

   . . .

)

PARTITION BY RANGE (area_code)

(

   PARTITION part_505 values less than(505),

   PARTITION part_212 values less than(212),

   PARTITION part_919 values less than(919),

   PARTITION part_252 values less than(252),

   PARTITION part_415 values less than(MAXVALUE)

);

Unlike range partitioning that requires a NUMBER or DATE scalar value, a list partition can be used to segregate data according to any value you desire.  Here is an example for a customer database where data is partitioned by state:

create table

   customer

   (col values)

partition by list (state_name)

partition yankees values (‘New York’,’Maine’),

partition rebels values (‘North Carolina’, South Carolina’);

As we can see, list partitioning adds great flexibility to large Oracle tables and give you more choices for logically segregating your row data.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: