Table Partition and type of partition in Oracle

Partition in Oracle and Type of Partition

Partition: Oracle is used partitioning for Tables and Indexes to convert them into smaller parts for easy to use and maintained.
Managed large amount of data with great performance will be acheived by partitioning the table and index.

A table partition is created on based on a particular column while creating table. While using sql queries, Oracle automatically used only partition based on sql queries column, these make the oracle use smaller portition of table which will increase the performance.

Check the partition table present in Database

COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
SELECT table_name, partition_name, high_value, num_rows
FROM dba_tab_partitions
ORDER BY table_name, partition_name;
 
— For sub partition
SELECT table_name, partition_name, subpartition_name, num_rows
FROM dba_tab_subpartitions
ORDER by table_name, partition_name, subpartition_name;

 
How to create the partition table?
 
Partition has following types:

List Partition

List partitioning in which you can control the row movement in partition table by specifying a list of values for the partitioning key.
 
Example If you have table “customer” having column “country” as partition key then you can create seperate partition for each country level like India, USA , Japan etc.

Create table customer (Cust_id number(2),Cust_name varchar(2),Country varchar(10))
partition by list(country)
(partition p1 values(‘INDIA’,’JAPAN’),
partition p2 values(‘USA’,’UK’),
partition p3 values(‘AUSTRALIA’) );

Note: Specify a default partition into which rows that do not map to any other partition are mapped.

Create table customer (Cust_id number(2),Cust_name varchar(2),Country varchar(10))
partition by list(country)
(partition p1 values(‘INDIA’,’JAPAN’),
partition p2 values(‘USA’,’UK’),
partition p3 values(‘AUSTRALIA’)
PARTITION P_null VALUES (NULL),
PARTITION P_unknown VALUES (DEFAULT) );

 

Selecting records from list partitioned table
Select * from customer;
Select * from customer partition(p1);
Adding a partition
Alter table customer add partition p5 values(‘CHINA’);
Dropping a partition
Alter table customer drop partition p5;
Renaming a partition
Alter table customer rename partition p5 to p1;
Truncate a partition
Alter table customer truncate partition p5;
Exchanging a partition
alter table customer exchange partition p1 with table CUSTOMER_1;
Moving a partition
Alter table customer move partition p2 tablespace USERS;
In 12c online, Moving a partition to new tablespace
ALTER TABLE TRAN MOVE PARTITION P2 ONLINE TABLESPACE users UPDATE INDEXES;

Range Partition:

Range partition in which you can select partition key value as date, serial no then you specify the range of data stored in particular partition.
 
Example: If i have table “TRAN” which having partition key column “DATE_CAP” then i want to create quarterly partition as follows:

CREATE TABLE tran
( tran_id NUMBER(6)
, cust_id NUMBER
, date_cap DATE
, selling_price NUMBER(10,2)
)
PARTITION BY RANGE (date_cap)
( PARTITION TRAN_PQ1_2017 VALUES LESS THAN (TO_DATE(’01-APR-2017′,’dd-MON-yyyy’))
TABLESPACE TRANQ12017
, PARTITION TRAN_PQ2_2017 VALUES LESS THAN (TO_DATE(’01-JUL-2017′,’dd-MON-yyyy’))
TABLESPACE TRANQ22017
, PARTITION TRAN_PQ3_2017 VALUES LESS THAN (TO_DATE(’01-OCT-2017′,’dd-MON-yyyy’))
TABLESPACE TRANQ32017
, PARTITION TRAN_PQ4_2017 VALUES LESS THAN (TO_DATE(’01-JAN-2018′,’dd-MON-yyyy’))
TABLESPACE TRANQ42017
) ENABLE ROW MOVEMENT;

Note:ENABLE ROW MOVEMENT clause is used for the automatic movement of a row to a new partition if an update to a key value is done.
Note:PARTITION TRAN_MAX_value VALUES LESS THAN (maxvalue); You can use maxvalue parameter if date is above then 2018.
 

Selecting records from range partitioned table
Select *from TRAN;
Select *from TRAN partition(p1);
Adding a partition
Alter table TRAN add partition TRAN_Q_2019 values less than (TO_DATE(’01-JAN-2019′,’dd-MON-yyyy’)) ;
Dropping a partition
Alter table TRAN drop partition TRAN_PQ4_2017;
Renaming a partition
Alter table TRAN rename partition TRAN_PQ3_2017 to Q3_2017;
Truncate a partition
Alter table TRAN truncate partition TRAN_PQ4_2017;
Splitting a partition
Alter table TRAN split partition TRAN_PQ1_2017 at (TO_DATE(’15-MAY-2017′,’dd-MON-yyyy’)) into (partition TRAN_PQ1_1_2017,partition TRAN_PQ1_2_2017);
Exchanging a partition: Creating non partition table to partition table by creating new table partition table, use exchange, rename process:
Alter table TRAN exchange partition TRAN_PQ1_2017 with table TRAN_1;
Moving a partition
Alter table TRAN move partition TRAN_PQ1_2017 tablespace USERS;
In 12c online, Moving a partition to new tablespace
ALTER TABLE TRAN MOVE PARTITION TRAN_PQ1_2017 ONLINE TABLESPACE users UPDATE INDEXES;

 

Hash Partition

Hash partition in which maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that move data to specific partitions
Example: “Employee” table which is having partition key column as “DEPTNP” then on based on hashing algorithm it data move to specific partition.

CREATE TABLE employee (employee_no number,deptno NUMBER, empname VARCHAR(32))
PARTITION BY HASH(deptno)
(PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2,
PARTITION p3 TABLESPACE ts1, PARTITION p4 TABLESPACE ts3);

 

Selecting records from hash partitioned table
Select * from Employee;
Select * from Employee partition(p1);
Adding a partition
Alter table Employee add partition p5;
Renaming a partition
Alter table Employee rename partition p5 to p10;
Truncate a partition
Alter table Employee truncate partition p10;
Exchanging a partition: For creating the non partition table to partition table and rename it.
Alter table Employee exchange partition P1 with table Employee_1;
Moving a partition to new tablespace:
Alter table Employee move partition P1 tablespace USERS;
In 12c online, Moving a partition to new tablespace
ALTER TABLE t1 MOVE PARTITION p1 ONLINE TABLESPACE users UPDATE INDEXES;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s