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;
