Table partitioning - II

Range Partitioning

Range Partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates. For example, you might want to partition sales data on a monthly basis. You can also partition by a range of alpha characters.

Example.

Create TABLE sales_range
(salesman_id Number(5),
salesman_name varchar2(30),
sales_amount Number(10),
sales_date Date)
Partition by Range(sales_date)
(
Partition sales_jan2009 values less than (to_date(‘02/01/2009’,’DD/MM/YYYY’)),
Partition sales_feb2009 values less than (to_date(‘03/01/2009’,’DD/MM/YYYY’)),
Partition sales_Mar2009 values less than (to_date(‘04/01/2009’,’DD/MM/YYYY’))
Enable row movement);

Create Table Students (
Student_id Number(6),
Student_fn Varchar2(25),
Student_ln Varchar2(35),
Primary key (student_id))
Partition by range (student_ln)
(partition student_ae Values less that (‘F%’),
partition student_fl Values less that (‘M%’),
partition student_mr Values less that (‘S%’),
partition student_sz Values less that (MAXVALUE)
ENABLE row movement);


Oracle 11g Feature – Interval Partitioning

In the above sales_range example, the DBA has to manually create the new partitions every month to accommodate the the values beyond the range specified. The system automatically creates a new partition ,if the specified values above the specified range for Interval partition.


Example:

Create TABLE sales_range
(salesman_id Number(5),
salesman_name varchar2(30),
sales_amount Number(10),
sales_date Date)
Partition by Range(sales_date)
Interval (numtoyminterval (1,’month’))
(
Partition sales_jan2009 values less than (to_date(‘02/01/2009’,’DD/MM/YYYY’))
);

List Partitioning

List partitioning enables you to control explicitly how rows map to partitions. You do this by specifying a list of discrete values for the partitioning column in the description for each partition. List partitioning allows for partitions to reflect real-world groupings (eg. Business units and regions). It differs from range partition in that the groupings in the list partitioning are not side by side or in a logical range. List partitioning gives us the ability to group together seemingly unrelated data into a specific partition.

Example

Create table sales_list
(salesman_id number(5),
salesman_name varchar2(30),
salesman_state varchar2(20),
sales_amount number(10),
sales_date date )
Partition by list (sales_state)
(
partition sales_west values (‘california’,’hawaii’),
partition sales_east values (‘New York’,’virginia’,’florida’),
partition sales_central values (‘texas’,’illinois’)
partition sales_other values (default)
enable row movement);

Composite Partitioning

There are two types of composite partitioning. They are composite Range-hash partitioning and Composite Range-List Partitioning

Composite Range-hash partitioning is used to range partition first, then use a hashing algorithm to further divide the data into sub partitions within each range partition. It combines both the ease of range partitioning and the benefits of hashing for date placement, striping and parallelism.

Possible usage : Range partition by date of birth then hash partition by name.

Composite range-list partitioning is used to range partition first, the divide the data in to subpartitions within each range partition based on the explicit list you chose. It combines both the ease of range partitioning and the benefits of list partitioning at the sub partition level.

Possible usage :- Range partition by date of birth then list partition by state.

Example

Create table sales_composite
(salesman_id number(5),
salesman_name varchar2(30),
sales_amount number(10),
sales_date date)
Partition by range (sales_date) subpartition by hash (salesman_id)
Subpartition template (
Subpartition sp1 tablespace data1,
Subpartition sp2 tablespace data2,
Subpartition sp3 tablespace data3,
Subpartition sp4 tablespace data4)
(partition sales_jan2009 values less
than (to_date (‘02/01/2009’,’dd/mm/yyyy’))
partition sales_feb2009 values less
than (to_date (‘03/01/2009’,’dd/mm/yyyy’))
partition sales_mar2009 values less
than (to_date (‘04/01/2009’,’dd/mm/yyyy’))
partition sales_apr2009 values less
than (to_date (‘05/01/2009’,’dd/mm/yyyy’))
partition sales_may2009 values less
than (to_date (‘06/01/2009’,’dd/mm/yyyy’))
enable row movement);

Create table bimonthly_regional_sales
(deptno number,
item_no varchar2(20),
txn_date date,
txn_amount number,
state varchar2(2))
Partition by Rnage (txn_date)
Subpartition by list (state)
Subpartition template (
Subpartition east values (‘NY’,’VA’,’FL’) tablespace ts1,
Subpartition west values (‘CA’,’OR’,’HI’) tablespace ts2,
Subpartition CENTRAL values (‘IL’,’TX’,’KS’) tablespace ts3
(Partition janfeb2009 values less than (to_date(‘1-MAR-2009’,’DD-MON-YYYY’)),
Partition marapr2009 values less than (to_date(‘1-MAY-2009’,’DD-MON-YYYY’)),
Partition mayjun2009 values less than (to_date(‘1-JU;-2009’,’DD-MON-YYYY’))
ENABLE ROW MOVEMENT);



Hash Partitioning

Hash Partitioning distributes data by applying a proprietary hashing algorith to the partition key and the assigning the data to the appropriate partition. With hash partitioning you can partition data that may not have any logical ranges. Oracle handles all of the distribution of date once the partition key is identified. Hash partitioning is used to spread data evenly over partitions.

Possible usage: Data has no logical groupings.

Example

Create table sales_hash
(salesman_id number(5),
salesman_name varchar2(30),
sales_amount number(10),
week_no number(2))
partition by hash (salesman_id)
partitions 4
store in (ts1, ts2, ts3, ts4);

In the next post I’ll talk about partitioned table maintenance activities and some guide lines.