Tuesday, 14 August 2012

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;

22 comments:

  1. thnx for such a gr8 doc..
    stay bless. .

    ReplyDelete
  2. Great post! Many thanks!

    Just one question: haven't you forgotten option 4, subpartitions? It is not explained.

    Kind regards.

    ReplyDelete
  3. One problem is that, You defined in Range Partition when you are creating table and you create column emp_no number(2). But when you insert data in that table you are inserting 3 digits in that particular column 'emp_no number(2)'

    ReplyDelete
  4. Corrected Example : Range Partition

    Insert into tango values(101,'a');-- this will go to p2
    Insert into tango values(201,'b'); -- this will go to p3
    Insert into tango values(301,'c');-- this will go to p4
    Insert into tango values(401,'d'); -- this will go to p4

    ReplyDelete
  5. Anonymous(before me) said it correct; still the data type needs to be changed above for Emp_no column to 3 digits

    ReplyDelete
  6. Very precise article..
    Thanks.

    ReplyDelete
  7. Very Gud Article

    ReplyDelete
  8. For Range partition, I tried to add new partition -
    Alter table Employee add partition p5 values less than(1000);
    I got below error -- (previous transformation was on less than(maxvalue))
    14074. 00000 - "partition bound must collate higher than that of the last partition"
    Could you please tell how to add a new partition after adding max value partition.

    ReplyDelete
  9. Overall excellent. Easy explanation.

    ReplyDelete
  10. Thanks for the article... very useful

    ReplyDelete
  11. SUB-PARTITIONS WITH RANGE AND HASH
    Subpartitions clause is used by hash only. We can not create subpartitions with list and hash partitions.
    a) Creating subpartitioned table
    SQL> Create table student(no number(2),name varchar(2),marks number(3))
    Partition by range(no) subpartition by hash(name) subpartitions 3
    (Partition p1 values less than(10),partition p2 values less than(20));
    This will create two partitions p1 and p2 with three subpartitions for each partition
    P1 – SYS_SUBP1
    SYS_SUBP2
    SYS_SUBP3
    P2 – SYS_SUBP4
    SYS_SUBP5
    SYS_SUBP6
    ** if you are using maxvalue for the last partition, you can not add a partition.
    b) Inserting records into subpartitioned table
    SQL> Insert into student values(1,‟a‟); -- this will go to p1
    SQL> Insert into student values(11,‟b‟); -- this will go to p2
    c) Retrieving records from subpartitioned table
    SQL> Select *from student;
    SQL> Select *from student partition(p1);
    SQL> Select *from student subpartition(sys_subp1);
    d) Possible operations with subpartitions
     Add
     Drop
     Truncate
     Rename
     Split
    e) Adding a partition
    SQL> Alter table student add partition p3 values less than(30);
    f) Dropping a partition
    SQL> Alter table student drop partition p3;
    g) Renaming a partition
    SQL> Alter table student rename partition p2 to p3;
    h) Truncate a partition
    SQL> Alter table student truncate partition p1;
    i) Splitting a partition
    SQL> Alter table student split partition p3 at(15) into (partition p31,partition p32);

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis