PARTITIONS
Partitioning is powerful functionality that 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. Oracle provides a comprehensive range of partitioning schemes to address every business requirement. Moreover, since it is entirely transparent in SQL statements, partitioning can be used with any application, from packaged OLTP applications to data warehouses.
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;
