What is a PARTITION in Oracle?Why to use Partition And Types of Partitions

PARTITIONS

Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.

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

1     Range partitions

2     List partitions

3     Hash partitions

4     Sub partitions

  

ADVANTAGES OF PARTITIONS

• 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??

1     Reduces the possibility of data corruption in multiple partitions.

2     Back up and recovery of each partition can be done independently.

 

Partitioning 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

1.RANGE PARTITIONS

Definition: A table that is partitioned by range is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range.

 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;

 2. LIST PARTITIONS

Definition: List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition.

 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;

3. HASH PARTITIONS

Definition:Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify.

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;