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;

36 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. 4.sub partitions/Composite Partitioning

    Composite partitioning is a combination of the basic data distribution methods; a table is partitioned by one data distribution method and then each partition is further subdivided into subpartitions using a second data distribution method. All subpartitions for a given partition together represent a logical subset of the data.

    Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of potential partition pruning and finer granularity of data placement through subpartitioning.

    ReplyDelete
  9. 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
  10. Overall excellent. Easy explanation.

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

    ReplyDelete
  12. Its worth contribution. Thanks

    ReplyDelete
  13. How about sub partitions? Please explain even that.

    ReplyDelete
  14. Nice information about Oracle Partitions.
    Stay blessed.

    ReplyDelete
  15. HI,

    Nice article but what about sub partition.

    ReplyDelete
  16. I have been searching for such an informative publication for many days, and it seems that my search here has just ended. Good job. Continue publishing.
    Gypsum & Glass Partition

    ReplyDelete
  17. thanks for sharing such a useful article for home appliances visit Lg Service Center

    ReplyDelete
  18. I have been searching for such an informative publication for many days, and it seems that my search here has just ended. Good job. Continue publishing.
    Partition

    ReplyDelete
  19. Our Pet Empire is a pet shop where customers can come in, enjoy the company of our cats and dogs over drinks and experience the benefits of some kitty therapy. Some of our wonderful cats and dogs are available for sale so we are hoping we can find forever homes for these fur babies. We believe that Pet empire is the perfect place for cat and dog lovers, especially those who cannot keep pets in their homes due to their fast pace of life.

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis