PARTITION IN ORACLE

PARTITIONS

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

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;

Search This Blog

SAP BASIS,HANA AND ORACLE DBA BLOGS