Friday, October 28, 2011

ORACLE SQL TUTORIAL: 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 saketh_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 saketh_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 saketh_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  


0 Responses to “ORACLE SQL TUTORIAL: PARTITIONS”

Post a Comment

Subscribe to: Post Comments (Atom)

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search