Table partitioning

Table partition is used to improve the performance, manageability and availability of applications in large size databases.

Types of table partitioning:

a. Range partitioning: The data is distributed based on a range of values of the partitioning key. For example, if we choose a date column as the partitioning key, the partition “JAN-2017” will contain all the rows that have the partition key values between 01-JAN-2017 and 31-JAN-2017 (assuming the range of the partition is from first of the month to the last date in the month).

SQL>Create table emp_range(empid number(4),empname varchar2(10), empsal(10,2)) partition by range(empid) (partition p1 values less than (10) tablespace t1, partition p2 values less than (20) tablespace t2, partition p3 values less than (30) tablespace t3, partition p4 values less than (40) tablespace t4);

b. Hash Partitioning: A hash algorithm is applied to the partitioning key to determine the partition for a given row. This provides I/O balancing, but cannot be used for range or inequality queries.

SQL>Create table emp_hash(empid number(4),empname varchar2(10), empsal(10,2)) 

PARTITION BY HASH (empid) PARTITIONS 4  STORE IN (data1, data2, data3, data4);

NOTE: The concepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added.

c. List Partitioning: The data distribution is defined by a list of values of the partitioning key. This is useful for discrete lists. e.g: Regions, States etc.

SQL>Create table emp_list(empid number(4),empname varchar2(10), empsal(10,2), dept_name varchar2(10)) partition by list(dept_name) (partition dept_A va;ues ('sales', 'marketing'), partition dept_B VALUES (‘advt'), Partition  dept_c values (‘manager') partition  college_others VALUES(DEFAULT) );

Insert data into the table

Retrieve data from the table:

SQL>select * from emp;

SQL> select * from <table_name>   <partition_name>;

SQL>select * from emp partition(p1);

SQL>select * from emp partition(p2);

To add another partition:

SQL>alter table emp add partition p4 values less than (maxvalue) tablespace t4;

To merge partition:

SQL>alter table emp merge partitions p1,p2 into partition p5 tablespace t3;

Splitting a partition:

SQL> alter table emp split partition p5 at(10) into (partition p1 tablespace t1,partition p2 tablespace t2);

Dropping a partition:

SQL> alter table emp drop partition p1;