Table partitioning, partition types with example in Oracle

Partitioning is dividing and-storing table data into smaller pieces to improve maintenance and query performance.

When to Partition a Table??

• Tables greater than 2 GB should always be considered as candidates for partitioning.
• Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.
• When the contents of a table need to be distributed across different types of storage devices.

TYPES OF PARTITION

•   Range partitions
•   List partitions
•   Hash partitions

ADVANTAGES OF PARTITIONING

• Reducing downtime for scheduled maintenance, which allows maintenance operations to be carried out on selected partitions while other partitions are available to users.
• Reducing downtime due to data failure, failure of a particular partition will no way affect other partitions.
• Partition independence allows for concurrent use of the various partitions for various purposes.

What is the advantage of partitions, by storing them in different Tablespaces??

• Reduces the possibility of data corruption in multiple partitions.
• Back up and recovery of each partition can be done independently.

PARTITION KEY

Each row in a partitioned table is unambiguously assigned to a single partition. The partitioning key is comprised of one or more columns that determine the partition where each row will be stored

RANGE PARTITION WITH EXAMPLE

Range partitioning is useful when you have distinct ranges of data you want to store together. The classic example of this is the use of dates. Partitioning a table using date ranges allows all data of a similar age to be stored in same partition. Once historical data is no longer needed the whole partition can be removed. If the table is indexed correctly search criteria can limit the search to the partitions that hold data of a correct age.

CREATE TABLE invoices
(invoice_no    NUMBER NOT NULL,
 invoice_date  DATE   NOT NULL,
 comments      VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users);

Example: Creating range partitioned table

 SQL> Create table Employee(emp_no number(2),emp_name varchar(2)) partition by range(emp_no) (partition p1 values less than(100), partition p2 values less than(200), partition p3 values less than(300),partition p4 values less than(maxvalue)); 
Inserting records into range partitioned table
     SQL> Insert into Employee values(101,’a’);      -- this will go to p1
     SQL> Insert into Employee values(201,’b’);     -- this will go to p2
     SQL> Insert into Employee values(301,’c’);      -- this will go to p3
     SQL> Insert into Employee values(401,’d’);     -- this will go to p4

 Selecting records from range partitioned table

     SQL> Select *from Employee;

     SQL> Select *from Employee partition(p1);

 Adding a partition

     SQL> Alter table Employee add partition p5 values less than(400);

 Dropping a partition

    SQL> Alter table Employee drop partition p1;

 Renaming a partition

     SQL> Alter table Employee rename partition p3 to p6;

 Truncate a partition

     SQL> Alter table Employee truncate partition p5;

 Splitting a partition

    SQL> Alter table Employee split partition p2 at(120) into (partition p21,partition p22);

 Exchanging a partition

  SQL> Alter table Employee exchange partition p2 with table Employee_x;

 Moving a partition

     SQL> Alter table Employee move partition p21 tablespace ABC_TBS;

LIST PARTITION WITH EXAMPLE

List partitioning is a partitioning technique where you specify a list of discrete values for the partitioning key in the description for each partition.

Example: Creating list partitioned table

SQL> Create table Employee (Emp_no number(2),Emp_name varchar(2)) partition by list(Emp_no)  (partition p1 values(1,2,3,4,5), partition p2 values(6,7,8,9,10),partition p3             values(11,12,13,14,15), partition p4 values(16,17,18,19,20));

 Inserting records into list partitioned table

      SQL> Insert into Employee values(4,’xxx’);     -- this will go to p1
      SQL> Insert into Employee values(8,’yyy’);     -- this will go to p2
      SQL> Insert into Employee values(14,’zzz’);    -- this will go to p3
      SQL> Insert into Employee values(19,’bbb’);  -- this will go to p4

Selecting records from list partitioned table
     SQL> Select *from Employee;
     SQL> Select *from Employee partition(p1);

 Adding a partition

     SQL> Alter table Employee add partition p5 values(21,22,23,24,25);

 Dropping a partition

     SQL> Alter table Employee drop partition p5;

 Renaming a partition

     SQL> Alter table Employee rename partition p5to p1;

 Truncate a partition

     SQL> Alter table Employee truncate partition p5;

 Exchanging a partition

    SQL> Alter table Employee exchange partition p1 with table Employee_x;

 Moving a partition

    SQL> Alter table Employee move partition p2 tablespace ABC_TBS;

HASH PARTITION WITH EXAMPLE

Hash partitioning is useful when there is no obvious range key, or range partitioning will cause uneven distribution of data. The number of partitions must be a power of 2 (2, 4, 8, 16...) and can be specified by the PARTITIONS...STORE IN clause.

CREATE TABLE invoices
(invoice_no    NUMBER NOT NULL,
 invoice_date  DATE   NOT NULL,
 comments      VARCHAR2(500))
PARTITION BY HASH (invoice_no)
PARTITIONS 4
STORE IN (users, users, users, users);

Or specified individually.

CREATE TABLE invoices
(invoice_no    NUMBER NOT NULL,
 invoice_date  DATE   NOT NULL,
 comments      VARCHAR2(500))
PARTITION BY HASH (invoice_no)
(PARTITION invoices_q1 TABLESPACE users,
 PARTITION invoices_q2 TABLESPACE users,
 PARTITION invoices_q3 TABLESPACE users,
 PARTITION invoices_q4 TABLESPACE users);

Example: Creating hash partitioned table
   
SQL> Create table Employee(emp_no number(2),emp_name varchar(2)) partition by      hash(emp_no) partitions 5;

     Here oracle automatically gives partition names like
                                                SYS_P1
                                                SYS_P2
                                                SYS_P3
                                                SYS_P4
                                                SYS_P5

 Inserting records into hash partitioned table(based on hash function)
     SQL> Insert into Employee values(5,’a’);      
     SQL> Insert into Employee values(8,’b’);      
     SQL> Insert into Employee values(14,’c’);    
     SQL> Insert into Employee values(19,’d’);   

Selecting records from hash partitioned table

     SQL> Select *from Employee;

     SQL> Select *from Employee partition(SYS_P2);

 Adding a partition

     SQL> Alter table Employee add partition p9;

 Renaming a partition

    SQL> Alter table Employee rename partition p9 to p10;

 Truncate a partition

     SQL> Alter table Employee truncate partition p9;

Exchanging a partition

 SQL> Alter table Employee exchange partition SYS_P1 with table Employee_X;

 Moving a partition
 
 SQL> Alter table Employee move partition SYS_P1 tablespace ABC_TBS;