PARTITIONS in Oracle Database

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 cannot 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.
  • Backup 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

  • Add
  • Drop
  • Truncate
  • Rename 
  • Split
  • Move
  • 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

  • Add
  • Drop
  • Truncate
  • Rename 
  • Move
  • 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

  • Add
  • Truncate
  • Rename 
  • Move
  • 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
Sub partitions clause is used by hash only. We cannot create sub partitions with list and hash partitions.
a) Creating sub partitioned 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);

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

Related posts:

Leave a Reply