Oracle Partitioning

Oracle Partitioning

In Oracle a Partition is just like the "Divide and conquer" approach, it is the way or the ability of the database to allow a DBA to physically split or break up all the very large tables, indexes and index organized tables into smaller and manageable pieces and/or segments. Each partition is known by its own characteristics. To achieve better performance is the primary objective of partitioning. It enables the database objects to be accessed and managed at a finer level of granularity.

Table types to be partitioned

The following are the table types to be partitioned:

If the size of the table is greater than 2GB, then the partition of the same table is to be required.
If the table is having some content and that content needs to be distributed across various type of storage devices.
If any table in the database having historical data is also to be partitioned.

Advantages of Partition

The following are the Advantages of Partition:

More flexible to match business needs.
Can be stored in a different tablespace.
Cheaper
Faster

Types of Partition

1. RANGE PARTITIONING METHOD

The Range Partitioning method is a technique of a partition that is used when there are local ranges of data that you want to store together. Here a table is partitioned in such a way that all the data of similar type to be stored is in the same partition. In a Range Partition each partition is based on the range of the partition key values.

Example: Date range.

Example

CREATE TABLE Students_range

(

Student_id NUMBER(10),

Student_name VARCHAR2(20),

Fee_amount NUMBER(15),

Admission_date DATE

)

PARTITION BY RANGE(Addmission_date)

(

PARTITION Students_Jan2013 VALUES LESS THAN(TO_DATE('01/02/2013','DD/MM/YYYY')),

PARTITION Students_Feb2013 VALUES LESS THAN(TO_DATE('01/03/2013','DD/MM/YYYY')),

PARTITION Students_Mar2013 VALUES LESS THAN(TO_DATE('01/04/2013','DD/MM/YYYY')),

PARTITION Students_Apr2013 VALUES LESS THAN(TO_DATE('01/05/2013','DD/MM/YYYY'))

);

2. HASH PARTITIONING METHOD

The Hash Partitioning method is much better than the Range Partitioning, since it enables the easy partitioning of data where a hash key is used to distribute rows evenly across the various partitions.

Example: Order_Number, Order_id.

Example

CREATE TABLE Employee

(

Emp_Id number(10),

Emp_name varchar2(20),

Contact_num Number,

Address Varchar2(50),

Salary Number

)

PARTITION BY HASH(Emp_Id)

(

PARTITION Emp01 tablespace Employee1,

PARTITION Emp02 tablespace Employee2,

PARTITION Emp03 tablespace Employee3,

PARTITION Emp04 tablespace Employee4

PARTITION Emp05 tablespace Employee5

PARTITION Emp06 tablespace Employee6

);

CREATE TABLE Employee

(

Emp_Id number(4),

Emp_name varchar2(30),

Contact_num Number,

Address Varchar2(50),

Salary Number

)

PARTITION BY HASH(Emp_Id)

PARTITIONS 6

STORE IN (Empts1, Empts2, Empts3, Empts4, Empts5, Empts6);

3. COMPOSITE PARTITIONING METHOD

The Composite Partitioning method is a combination of various partitioning techniques like:

Range-hash
Range-list
List-hash
Interval-hash
Interval-range and so on.

Where partitions are based on columns to specify range. Using Composite Partitioning will allow queries to benefit from table partitioning. It supports historical operations.

Example

CREATE TABLE Customers_composite

(

Customer_id NUMBER(10),

Customer_name VARCHAR2(20),

Item_Price NUMBER(15),

Purchase_date DATE

)

PARTITION BY RANGE(Purchase_date)

SUBPARTITION BY HASH(Customer_id)

SUBPARTITION TEMPLATE

(

SUBPARTITION SubPart1 TABLESPACE ts1,

SUBPARTITION SubPart2 TABLESPACE ts2,

SUBPARTITION SubPart3 TABLESPACE ts3,

SUBPARTITION SubPart4 TABLESPACE ts4

)

(

PARTITION Customers_Jan2013 VALUES LESS THAN(TO_DATE('01/02/2000','DD/MM/YYYY'))

PARTITION Customers_Feb2013 VALUES LESS THAN(TO_DATE('01/03/2000','DD/MM/YYYY'))

PARTITION Customers_Mar2013 VALUES LESS THAN(TO_DATE('01/04/2000','DD/MM/YYYY'))

PARTITION Customers_Apr2013 VALUES LESS THAN(TO_DATE('01/05/2000','DD/MM/YYYY'))

PARTITION Customers_May2013 VALUES LESS THAN(TO_DATE('01/06/2000','DD/MM/YYYY'))

PARTITION Customers_Jun2013 VALUES LESS THAN(TO_DATE('01/07/2000','DD/MM/YYYY'))

PARTITION Customers_Jul2013 VALUES LESS THAN(TO_DATE('01/08/2000','DD/MM/YYYY'))

);

4. LIST PARTITIONING METHOD

The List Partitioning technique is similar to the range partition method except it does not have a max value. It enables us to explicitly control how rows map to a partition. We can group and organize the unrelated and unordered sets of data in a natural way.

Example

CREATE TABLE Products_list

(

Product_id NUMBER(10),

Product_name VARCHAR2(20),

Sale_state VARCHAR2(15),

Sales_amount NUMBER(15),

Sales_date DATE

)

PARTITION BY LIST(Products_state)

(

PARTITION Products_west VALUES('ABC', 'ASD'),

PARTITION Products_east VALUES ('DDD', 'TTT', 'QQQ'),

PARTITION Products_central VALUES('PDD', 'LKK'),

PARTITION Products_other VALUES(DEFAULT)

);

5. INTERVAL PARTITIONING METHOD

The Interval Partitioning method was introduced with Oracle 11g. It is based on the numeric value like: number or date type. Using Interval Partitioning we can define equal-sized partitions. It works well especially for Range Partitioning where partitions were done based by date range. It doesn't support index organized tables.

Example

CREATE TABLE interval_Sales

(

Product_id NUMBER(6),

Vendor_id NUMBER,

Time_id DATE,

Channel_id CHAR(1),

Supplier_id NUMBER(10),

Quantity NUMBER(5),

Tot_Amount NUMBER(15,2)

)

PARTITION BY RANGE (Time_id)

INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

(

PARTITION p0 VALUES LESS THAN (TO_DATE('1-9-2010', 'DD-MM-YYYY')),

PARTITION p1 VALUES LESS THAN (TO_DATE('1-3-2011', 'DD-MM-YYYY')),

PARTITION p2 VALUES LESS THAN (TO_DATE('1-11-2012', 'DD-MM-YYYY')),

PARTITION p3 VALUES LESS THAN (TO_DATE('1-2-2013', 'DD-MM-YYYY'))

);

6. SYSTEM PARTITIONING METHOD

The System Partitioning technique is also a new method that was launched with Oracle edition 11g, it provides the ability to implement and manage new partitions without a specific partition key. It doesn't have a partition key.

Example

CREATE TABLE Employees

(

Employee_id number(10),

Employee_name varchar2(20),

Department_id number(10),

Location_id number(10)

)

PARTITION BY SYSTEM

(

PARTITION p1 tablespace users01,

PARTITION p2 tablespace users02

);

For Insert

INSERT INTO Employees PARTITION (p1) values (1,'sales', 1, 10);

For Delete

DELETE Employees PARTITION (p1) where Employees_id = 1;

For Update

UPDATE Employees PARTITION (p1) set Location_id=2 where Employees_id = 1;

7. REFERENCE PARTITIONING METHOD

It is also another new method of Oracle 11g. This is a type of partition that is based on referential integrity. It partitions a parent table into child tables and again the child tables into sub-child tables using the same partitioning key as the parent table without having to duplicate the key columns.

Example

CREATE TABLE Order_parent

(

Order_id NUMBER NOT NULL,

Order_name VARCHAR2(10) NOT NULL,

Order_date DATE,

CONSTRAINT Order_parent_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (Order_date)

(

PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY')),

PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2013','DD-MON-YYYY'))

);

CREATE TABLE Order1_child

(

Order1_id NUMBER NOT NULL,

Order_parent_id NUMBER NOT NULL,

Order1_name VARCHAR2(10),

Order1_date DATE,

CONSTRAINT Order1_child_pk PRIMARY KEY (id),

CONSTRAINT Order1_Order_parent_fk FOREIGN KEY (Order_parent_id)

REFERENCES Order_parent (id)

)

PARTITION BY REFERENCE (Order1_Order_parent_fk);