Partitioning in Oracle. What? Why? When? Who? Where? How?

Post date: 16-Jul-2010 06:35:50

An article for Oracle DBAs interested in finding ways of partitioning the tables as well showing a new form of partitioning using Oracle 9i

.Business applications are growing at a faster rate than salaries, and so is the data supporting them, especially e-business applications where the data growth has been around 20-30 percent or more annually. Oracle

came up with the idea of Partitioning the Tables. 

Yes ! The name sounds right “Partitioning”, the literal meaning of Partitioning is “The Act of dividing something into parts” and in our case that ‘something’ is nothing but…? 

You guessed it right! Data.

Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a "partition". From an "Application Development" perspective, there is no difference between a partitioned and a non-partitioned table. The application need not be modified to access a partitioned table if that application was initially written on a non partitioned tables.

So now you know partitioning in oracle now the only thing that yo u need to know is little bit of syntax and that’s it, and you are a partitioning guru. 

Oracle introduced partitioning with 8.0. With this version only, " Range Partitioning" was supported. I will come to details later about what that means. Then with Oracle 8i " Hash and Composite Partitioning" was also introduced and with 9i " List Partitioning", it was introduced with lots of other features with each upgrade. Each method of partitioning has its own advantages and disadvantages and the decision which one to use will depend on the data and type of application. Also one can MODIFY , RENAME, MOVE, ADD, DROP, TRUNCATE, SPLIT partitions. We will go thru the details now. 

Advantages of using Partition’s in Table

1. Smaller and more manageable pieces of data ( Partitions )

2. Reduced recovery time

3. Failure impact is less

4. import / export can be done at the " Partition Level".

5. Faster access of data

6. Partitions work independent of the other partitions.

7. Very easy to use

Types of Partitioning Methods

1. RANGE Partitioning

This type of partitioning creates partitions based on the " Range of Column" values. Each partition is defined by a " Partition Bound" (non inclusive ) that basically limits the scope of partition. Most commonly used values for " Range Partition" is the Date field in a table. Lets say we have a table SAMPLE_ORDERS and it has a field ORDER_DATE. Also, lets say we have 5 years of history in this table. Then, we can create partitions by date for, lets say, every quarter. 

So Every Quarter Data becomes a partition in the SAMPLE_ORDER table. The first partition will be the one with the lowest bound and the last one will be the Partition with the highest bound. So if we have a query that want to look at the Data of first quarter of 1999 then instead of going through the complete data it will directly go to the Partition of first quarter 1999. 

This is example of the syntax needed for creating a RANGE PARTITION.

CREATE TABLE SAMPLE_ORDERS

(ORDER_NUMBER NUMBER,

ORDER_DATE DATE,

CUST_NUM NUMBER,

TOTAL_PRICE NUMBER,

TOTAL_TAX NUMBER,

TOTAL_SHIPPING NUMBER)

PARTITION BY RANGE(ORDER_DATE)

(

PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),

PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),

PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),

PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)

)

;

the above example basically created 8 partitions on the SAMPLE_ORDERS Table all these partitions correspond to one quarter. Partition SO99Q1 will contain the orders for only first quarter of 1999. 

2. HASH Partitioning

Under this type of partitioning the records in a table, are partitions based of a Hash value found in the value of the column, that is used for partitioning. " Hash Partitioning" does not have any logical meaning to the partitions as do the range partitioning. Lets take one example. 

CREATE TABLE SAMPLE_ORDERS

(ORDER_NUMBER NUMBER,

ORDER_DATE DATE,

CUST_NUM NUMBER,

TOTAL_PRICE NUMBER,

TOTAL_TAX NUMBER,

TOTAL_SHIPPING NUMBER,

ORDER_ZIP_CODE)

PARTITION BY HASH (ORDER_ZIP_CODE)

(PARTITION P1_ZIP TABLESPACE TS01, 

PARTITION P2_ZIP TABLESPACE TS02,

PARTITION P3_ZIP TABLESPACE TS03, 

PARTITION P4_ZIP TABLESPACE TS04)

ENABLE ROW MOVEMENT;

The above example creates four hash partitions based on the zip codes from where the orders were placed.

3. List Partitioning ( Only with 9i)

Under this type of partitioning the records in a table are partitioned based on the List of values for a table with say communities column as a defining key the partitions can be made based on that say in a table we have communities like ‘Government’ , ‘Asian’ , ‘Employees’ , ‘American’, ‘European’ then a List Partition can be created for individual or a group of communities lets say ‘American-partition’ will have all the records having the community as ‘American’

Lets take one example. In fact, we will modify the same example.

CREATE TABLE SAMPLE_ORDERS

(ORDER_NUMBER NUMBER,

ORDER_DATE DATE,

CUST_NUM NUMBER,

TOTAL_PRICE NUMBER,

TOTAL_TAX NUMBER,

TOTAL_SHIPPING NUMBER,

SHIP_TO_ZIP_CODE,

SHIP_TO_STATE)

PARTITION BY LIST (SHIP_TO_STATE)

(PARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01, 

PARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02, 

PARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03, 

PARTITION SHIP_TO_MASACHUSETTES VALUES (‘MA’) TABLESPACE TS04, 

PARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)

ENABLE ROW MOVEMENT;

The above example creates List partition based on the SHIP_TO_STATE each partition allocated to different table spaces.

4. Composite Range-Hash Partitioning

This is basically a combination of range and hash partitions. So basically, the first step is that the data is divided using the range partition and then each range partitioned data is further subdivided into a hash partition using hash key values. All sub partitions, together, represent a logical subset of the data. 

Lets modify the above example again:

CREATE TABLE SAMPLE_ORDERS

(ORDER_NUMBER NUMBER,

ORDER_DATE DATE,

CUST_NUM NUMBER,

CUST_NAME VARCAHR2, 

TOTAL_PRICE NUMBER,

TOTAL_TAX NUMBER,

TOTAL_SHIPPING NUMBER,

SHIP_TO_ZIP_CODE,

SHIP_TO_STATE)

TABLESPACE USERS

PARTITION BY RANGE (ORDER_DATE) 

SUBPARTITION BY HASH(CUST_NAME) 

SUBPARTITION TEMPLATE(

(SUBPARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01, 

SUBPARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02, 

SUBPARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03, 

SUBPARTITION SHIP_TO_NORTHEAST VALUES (‘MA’, ‘NY’, ‘NJ’) TABLESPACE TS04, 

SUBPARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)

(

PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),

PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),

PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),

PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)

)

ENABLE ROW MOVEMENT;

The above example shows that each range partition has been further sub-partitioned into smaller partitions based on the list value specified. SHIP_TO_ARIZONA is a sub-partition by a List value AZ. This partition will be present in the main partitions by range SO99Q1 etc.

5. Composite Range-List Partitioning ( Only with 9i)

This is also a combination of Range and List Partitions, basically first the data is divided using the Range partition and then each Range partitioned data is further subdivided into List partitions using List key values. Each sub partitions individually represents logical subset of the data not like composite Range-Hash Partition.

Index organized tables can be partitioned using Range or Hash Partitions

Lets modify the above partition once more.

CREATE TABLE SAMPLE_ORDERS

(ORDER_NUMBER NUMBER,

ORDER_DATE DATE,

CUST_NUM NUMBER,

CUST_NAME VARCAHR2, 

TOTAL_PRICE NUMBER,

TOTAL_TAX NUMBER,

TOTAL_SHIPPING NUMBER,

SHIP_TO_ZIP_CODE,

SHIP_TO_STATE)

TABLESPACE USERS

PARTITION BY RANGE (ORDER_DATE) 

SUBPARTITION BY LIST(SHIP_TO_STATE) 

SUBPARTITION TEMPLATE(

SUBPARTITION SP1 TABLESPACE TS01,

SUBPARTITION SP2 TABLESPACE TS02,

SUBPARTITION SP3 TABLESPACE TS03,

SUBPARTITION SP4 TABLESPACE TS04,

SUBPARTITION SP5 TABLESPACE TS05)

(

PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),

PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),

PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),

PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)

)

ENABLE ROW MOVEMENT;

With Oracle 9i, there is also a feature to create indexes on the partitions. The indexes can be:

a. Local indexes

This is created the same manner as the index on existing partitioned table. Each partition of a local index corresponds to one partition only.

b. Global Partitioned Indexes

This can be created on a partitioned or a non-partitioned tables. But for now, they can be partitioned using the " Range Partitioning" only. For example, in above example, where I divided the table into partitions representing a quarter, a " Global Index" can be created by using a different " Partitioning Key" and can have different number of partitions.

c. Global Non- Partitioned Indexes

This is no different than the ordinary index created on a non-partitioned table. The index structure is not partitioned.

Partitioning greatly enhances the performance, manageability and availability of most databases. Partitioning can be applied to any databases and software shops using such a great option, have greatly improved the user satisfaction for there business applications.

I hope this overview of partitioning will provide some answers to What? When? Who? Where ? Why and How? of partitioning in Oracle

. 

For detailed syntax and documentation refer to Oracle documentation. This article does not claim to provide a full understanding or the partitioning feature in oracle. Oracle has been modifying this feature with new releases. So lot more to come. Watch out.

Ref: http://www.devarticles.com/c/a/Oracle/Partitioning-in-Oracle/2/