Saturday, October 18, 2014

Oracle Partitions and Partition Types with examples



PARTITIONS

A single logical table can be split into a number of physically separate pieces based on ranges of key values. Each of the parts of the table is called a partition.
A non-partitioned table can not be partitioned later.

TYPES

Ø  Range partitions
Ø  List partitions
Ø  Hash partitions
Ø  Sub partitions

ADVANTAGES

Ø  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.

ADVANTAGES 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.

DISADVANTAGES

Ø  Partitioned tables cannot contain any columns with long or long raw datatypes, LOB types or object types.




RANGE PARTITIONS

a) Creating range partitioned table
     SQL> Create table student(no number(2),name varchar(2)) partition by range(no) (partition 
             p1 values less than(10), partition p2 values less than(20), partition p3 values less     
             than(30),partition p4 values less than(maxvalue));

    ** if you are using maxvalue for the last partition, you can not add a partition.
b) Inserting records into range partitioned 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
     SQL> Insert into student values(21,’c’);        -- this will go to p3
     SQL> Insert into student values(31,’d’);        -- this will go to p4
c) Retrieving records from range partitioned table
     SQL> Select *from student;
     SQL> Select *from student partition(p1);
d) Possible operations with range partitions
v  Add
v  Drop
v  Truncate
v  Rename        
v  Split
v  Move
v  Exchange
e) Adding a partition
     SQL> Alter table student add partition p5 values less than(40);
f) Dropping a partition
    SQL> Alter table student drop partition p4;
g) Renaming a partition
     SQL> Alter table student rename partition p3 to p6;
h) Truncate a partition
     SQL> Alter table student truncate partition p6;
i) Splitting a partition
    SQL> Alter table student split partition p2 at(15) into (partition p21,partition p22);
j) Exchanging a partition
    SQL> Alter table student exchange partition p1 with table student2;
k) Moving a partition
     SQL> Alter table student move partition p21 tablespace Prasad_ts;

LIST PARTITIONS

a) Creating list partitioned table
     SQL> Create table student(no number(2),name varchar(2)) partition by list(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));
 b) Inserting records into list partitioned table
      SQL> Insert into student values(1,’a’);         -- this will go to p1
      SQL> Insert into student values(6,’b’);         -- this will go to p2
      SQL> Insert into student values(11,’c’);       -- this will go to p3
      SQL> Insert into student values(16,’d’);       -- this will go to p4
c) Retrieving records from list partitioned table
     SQL> Select *from student;
     SQL> Select *from student partition(p1);
d) Possible operations with list partitions
v  Add
v  Drop
v  Truncate
v  Rename        
v  Move
v  Exchange
e) Adding a partition
     SQL> Alter table student add partition p5 values(21,22,23,24,25);
f) Dropping a partition
     SQL> Alter table student drop partition p4;
g) Renaming a partition
     SQL> Alter table student rename partition p3 to p6;
h) Truncate a partition
     SQL> Alter table student truncate partition p6;
i) Exchanging a partition
    SQL> Alter table student exchange partition p1 with table student2;
j) Moving a partition
    SQL> Alter table student move partition p2 tablespace Prasad_ts;

HASH PARTITIONS

a) Creating hash partitioned table
     SQL> Create table student(no number(2),name varchar(2)) partition by hash(no) partitions  
             5;
Here oracle automatically gives partition names like
                                    SYS_P1
                                    SYS_P2
                                    SYS_P3
                                    SYS_P4
                                    SYS_P5
b) Inserting records into hash partitioned table
     it will insert the records based on hash function calculated by taking the partition key
     SQL> Insert into student values(1,’a’);         
     SQL> Insert into student values(6,’b’);         
     SQL> Insert into student values(11,’c’);       
     SQL> Insert into student values(16,’d’);       
c) Retrieving records from hash partitioned table
     SQL> Select *from student;
     SQL> Select *from student partition(sys_p1);
d) Possible operations with hash partitions
v  Add
v  Truncate
v  Rename        
v  Move
v  Exchange
e) Adding a partition
     SQL> Alter table student add partition p6 ;
f) Renaming a partition
    SQL> Alter table student rename partition p6 to p7;
g) Truncate a partition
     SQL> Alter table student truncate partition p7;
h) Exchanging a partition
     SQL> Alter table student exchange partition sys_p1 with table student2;
i) Moving a partition
    SQL> Alter table student move partition sys_p2 tablespace Prasad_ts;

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
v  Add
v  Drop
v  Truncate
v  Rename        
v  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);

DATA MODEL

Ø  ALL_IND_PARTITIONS     
Ø  ALL_IND_SUBPARTITIONS  
Ø  ALL_TAB_PARTITIONS      
Ø  ALL_TAB_SUBPARTITIONS  
Ø  DBA_IND_PARTITIONS     
Ø  DBA_IND_SUBPARTITIONS  
Ø  DBA_TAB_PARTITIONS     
Ø  DBA_TAB_SUBPARTITIONS  
Ø  USER_IND_PARTITIONS    
Ø  USER_IND_SUBPARTITIONS 
Ø  USER_TAB_PARTITIONS    
Ø  USER_TAB_SUBPARTITIONS 















No comments:

Post a Comment