Post
Ask Question

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:

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

Advantages of Partition

The following are the Advantages of Partition:

  1. More flexible to match business needs.
  2. Can be stored in a different tablespace.
  3. Cheaper
  4. 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

);


Or
 

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);


Similar Articles