- 21 Nov, 2021
- 0 Comments
- 8 Mins Read
Table partition | How to do Table Partitioning in Oracle with Example
Table partition | How to do Table Partitioning in Oracle with Example
Table partition :
There are so many aspects which are important in improving the performance of SQL. Partition allows tables, indexes and index organized tables to be subdivided into smaller pieces. Table partition is used to reduce the cost and improving performance of the application. There are some partition mechanisms using which one can divide a table into smaller pieces. Partitions can be used in so many application where we need to improve the performance. Each partition has its own name and it has own memory storage. partition allows table,index or index organized tables to be subdivided in to smaller pieces and each piece of table,index or index organized table is called as Partition
What is mean by Table Partition :
Following are Advantages of Partition:
1.Increase Performance
2.Increases availability
3.Enable storage cost optimization
4.Enables Simpler management
“We can not partition a table with Long and long raw datatype…”
When to partition the table?
1.Table should be greater than 2 GB
2.Tables which contains historical data in which new data will be added in to newest partition. The real life example of this is historical table which contains updatable data for one year other data is read only.
3.When contents of the table needs to be distributed in different storage devices.
4.When table performance is weak and we need to improve performance of application. Each row in partitioned table is unambiguously assigned to single partition table. The Partitioning key is comprised of one or more columns that determine the partition where each row will be stored.
Types Of Table partitioning
There are following types of Table partition:
1.Range Partition
2.List Partition
3.Hash Partition
1.Range Partition:
When in the specified table the data is based on the specific date range and it is properly divided in some range then user should go for the partitioned named as ‘Range Partition’.This partition type is most common type of Table partitioning which is been useful for Data warehouse to store the historical data in given date range.The partitioning is done in such way that the expression values lies within the specific range.This kind of Table partition is used when there is a particular date range available
.
Syntax:
Create table Tablename (Column_name1 datatype(size)…. Column_name-n datatype(size)) Partition by range(Column needs to be partitioned) (Partition partition_name1 values less than(value1)…. Partition partition_name-n values less than(maxvalue));
Real Life Example of Range Partition:
Step 1: Creation of partitioned table
Create table Employee(emp_no number(2), Salary number) partition by range(Salary) (partition p1 values less than(10000), partition p2 values less than(20000), partition p3 values less than(30000), partition p4 values less than(maxvalue));
SQL> Create table Employee(emp_no number(2), Salary number) partition by range(Salary) (partition p1 values less than(10000), partition p2 values less than(20000), partition p3 values less than(30000), partition p4 values less than(maxvalue)); 2 3 4 5 6 7 Table created.
Here We put the partition on salary of the employee.
Step 2: Insert the values in table.
Insert into Employee values(1,40000); Value inserted in partition p4 which is maximum value. Insert into Employee values(2,11000); Value inserted in partition p2. Insert into Employee values(3,25000); Value inserted in partition p3. SQL> Insert into Employee values(1,40000); 2 1 row created. SQL> Insert into Employee values(2,11000); 2 1 row created. SQL> Insert into Employee values(3,25000); 2 1 row created.
Queries for Range Partition:
1.Selecting records from partitioned tables.
Select * from Employee;
SQL> Select * from Employee order by 1;
EMP_NO SALARY
---------- ----------
1 40000
2 11000
3 25000
SQL>
Select * from Employee partition(p4);
SQL> Select * from Employee partition(p4);
EMP_NO SALARY
---------- ----------
1 40000
LISTING INFORMATION ABOUT PARTITION TABLES
To see how many partitioned tables are there in your schema give the following statement
SQL> set linesize 200
SQL> set pagesize 200
col table_name format a15
SQL> select TABLE_NAME , PARTITIONING_TYPE from
user_part_tables
2 where TABLE_NAME ='EMPLOYEE';
TABLE_NAME PARTITION
--------------- ---------
EMPLOYEE RANGE
To see on partition level partitioning information
select * from user_tab_partitions;
2.Adding new Table partition:
Alter table Employee add partition p5 values less than(50000); col partition_name format a15 SQL> SELECT TABLE_NAME,PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='EMPLOYEE'; SQL> col partition_name format a15 SQL> / TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE --------------- - -------------- ------------------ ----------------------------------- --------------------------------------------- EMPLOYEE P1 1 10000 EMPLOYEE P2 2 20000 EMPLOYEE P3 3 30000 EMPLOYEE P4 4 MAXVALUE
3.Drop Table partition:
SQL> Alter table Employee drop partition p1; 2 Table altered. SQL> SELECT TABLE_NAME,PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='EMPLOYEE'; TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE --------------- --------------- ------------------ ----------------------------------------------------- EMPLOYEE P2 1 20000 EMPLOYEE P3 2 30000 EMPLOYEE P4 3 MAXVALUE
4.Rename Table partition:
SQL> Alter table Employee Rename partition p2 to p6; 2 Table altered. SQL> SELECT TABLE_NAME,PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='EMPLOYEE'; TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE --------------- --------------- ------------------ -------------------------------------------------------------------------------- EMPLOYEE P6 1 20000 EMPLOYEE P3 2 30000 EMPLOYEE P4 3 MAXVALUE
5.Truncate partition:
SQL> Alter table Employee Truncate partition p6; 2 Table truncated. SQL> Select * from Employee partition (p6); no rows selected
6.Split partition:
SQL> Alter table Employee Split partition p6 at (10000) into (partition p10,partition p11); 2 3 Table altered. SQL> SELECT TABLE_NAME,PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='EMPLOYEE'; TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE --------------- --------------- ------------------ -------------------------------------------------------------------------------- EMPLOYEE P10 1 10000 EMPLOYEE P11 2 20000 EMPLOYEE P3 3 30000 EMPLOYEE P4 4 MAXVALUE
7.Moving partition:
Alter table Employee move partition p6 to tablespace ABCD;
2.List Partitioning:
List partition enables you to explicitly control how the partition of tables needs to be don by specifing list of distinct values as partition key in description of each partition.When there is a set of distinct values in the table which is properly divided then user should go with list partition.By listing the distinct values user should do the partition.Simple example is the table storing the country data in which state is distinct column.So You can partition the table using list of state values.
Syntax:
Create table Tablename (Column_name1 datatype(size)…. Column_name-n datatype(size)) Partition by range(Column needs to be partitioned) (Partition partition_name1 values less than(value1)…. Partition partition_name-n values less than(maxvalue));
Real life Example:
Create table with partition to State column:
Create table Employee1(emp_no number(2),
State varchar2(20))
partition by List(State)
(partition p1_Maharashtra values ('Maharashtra'),
partition p2_Gujrath values('Gujrath'),
partition p3_Rajsthan values('Bengal'),
partition p4_Other values (Default));
SQL> Create table Employee1(emp_no number(2),
State varchar2(20))
partition by List(State)
(partition p1_Maharashtra values ('Maharashtra'),
partition p2_Gujrath values('Gujrath'),
partition p3_Rajsthan values('Bengal'),
partition p4_Other values (Default)); 2 3 4 5 6 7
Table created.
Insert into table:
SQL>
SQL> Insert into Employee1
values(1,'Maharashtra');
2
1 row created.
Value inserted in partition p1_Maharashtra which is maximum value.
SQL>
Insert into Employee1
values(2,'Kerala')
;SQL>
2
1 row created.
Value inserted in partition p4_Others.
Queries Related to List Partition:
1.Selecting records from partitioned tables.
SQL> Select * from Employee1;
EMP_NO STATE
---------- --------------------
1 Maharashtra
2 Kerala
SQL>
Select * from Employee1 partition(p1_Maharashtra);SQL>
EMP_NO STATE
---------- --------------------
1 Maharashtra
2.Adding new partition:
Alter table Employee1
add partition p5_Kerala values('Kerala');
3.Drop partition:
Alter table Employee drop partition p1_Maharashtra;
4.Rename partition:
Alter table Employee Rename partition p1_Maharashra to p6_Maha;
5.Truncate partition:
Alter table Employee
6.Moving partition:
Alter table Employee move partition p1_Maharashtra to tablespace ABCD;
3.Hash Partition:
Hash partitioning is type of partitioning where data is partitioned by using the hashing algorithms.Different hashing algorithms are applied to partition key that you identify.Hash partition is mainly used to distribute data among the different storage devices.Hash partition is easy to use and best alternative for list partition when data is not historical.
Syntax:
Create table Table_name
(Column_name1 datatype1…
Column_name n datatype ‘n’)
Partition by Hash(column_name)
Partitions partition_number);
Create table Employee2
(emp_no number(2),
emp_name varchar(2))
partition by hash(emp_no) partitions 5;
The Above statement will create 5 partitions named:
Sys_P1
Sys_P2
Sys_P3
Sys_P4
Sys_P5
set linesize 200
set pagesize 200
col table_name format a15
col partition_name format a15
break on table_name skip 1
SELECT TABLE_NAME,PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='EMPLOYEE2';
SQL> /
TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
--------------- --------------- ------------------ --------------------------------------------------------------------------------
EMPLOYEE2 SYS_P4906 1
SYS_P4907 2
SYS_P4908 3
SYS_P4909 4
SYS_P4910 5
When we insert the records into table according to Hash algorithm data will split into different partitions.
SQL> Insert into Employee2
values(1,'AB'); 2
SQL> Insert into Employee2
values(2,'CD'); 2
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select * from Employee2 ;
EMP_NO EM
---------- --
2 CD
1 AB
SET LINESIZE 200
SET PAGESIZE 200
col table_name format a15
col partition_name format a15
break on table_name skip 1
SQL>
select table_name,partition_name,tablespace_name,num_rows,blocks,avg_row_len,segment_created from user_tab_partitions
where table_name like 'EMPLOYEE2';
How To create partition on non partitioned Tables?
If you have Employee table which is not partitioned and you need to add the partition to the Employee table.There is direct way to add the partition to the table.The ‘Alter Table Modify’ clause is used to add the partition to the existing non partitioned table.In Addition we need to use the keyword named ‘Online’.
Syntax:
Alter table tablename Modify Partition by partition_name(Column_name)( Partition partition_name values …… ) online;
Real life Example:
Suppose you want to add the partition to the existing table named ‘Employee’ and partition it by using City column.
Query used:
Alter table Employee Modify Partition by LIST(Employee_City) ( Partition P_Kolhapur values(‘Kolhapur’), Partition P_SQuery used:angali values(‘Sangli’), Partition P_OTH values(default) ) online;