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