Table partition | How to do Table Partitioning in Oracle with Example
in this blog i have explain Table partition | How to do Table Partitioning in Oracle with Example
oracle configurationintermediate
by OracleDba
12 views
📋Steps in this guide1/1
1
Overview
Table partition | How to do Table Partitioning in Oracle with Example
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
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…”
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.
There are following types of Table partition:
1.Range Partition
2.List Partition
3.Hash 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
.
Step 1: Creation of partitioned table
Here We put the partition on salary of the employee.
Step 2: Insert the values in table.
Queries for Range Partition:
To see how many partitioned tables are there in your schema give the following statement
2.Adding new Table partition:
3.Drop Table partition:
4.Rename Table partition:
5.Truncate partition:
6.Split partition:
7.Moving partition:
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:
Real life Example:
Queries Related to List Partition:
1.Selecting records from partitioned tables.
2.Adding new partition:
3.Drop partition:
4.Rename partition:
5.Truncate partition:
6.Moving partition:
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:
When we insert the records into table according to Hash algorithm data will split into different partitions.
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:
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:
Code/Command (click line numbers to comment):
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329
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));
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.
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.
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
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;
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
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
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
SQL> Alter table Employee
Truncate partition p6; 2
Table truncated.
SQL> Select * from Employee partition (p6);
no rows selected
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
Alter table Employee
move partition p6 to tablespace ABCD;
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));
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.
SQL> Select * from Employee1;
EMP_NO STATE
---------- --------------------
1 Maharashtra
2 Kerala
SQL>
Select * from Employee1 partition(p1_Maharashtra);SQL>
EMP_NO STATE
---------- --------------------
1 Maharashtra
Alter table Employee1
add partition p5_Kerala values('Kerala');
Alter table Employee
drop partition p1_Maharashtra;
Alter table Employee
Rename partition p1_Maharashra to p6_Maha;
Alter table Employee
Alter table Employee
move partition p1_Maharashtra to tablespace ABCD;
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
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';
Alter table tablename
Modify
Partition by partition_name(Column_name)(
Partition partition_name values ……
) online;
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;Comments (0)
Please to add comments
No comments yet. Be the first to comment!