What is a PARTITION in Oracle?Why to use Partition And Types of Partitions
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;
No comments:
Post a Comment