Table partitioning

Table partition is used to improve the performance, manageability and availability of applications

Before creating table partitioning:

1. Create user:

SQL>create user user1 identified by user1;

2. Grant privileges to user:

SQL>grant connect,resource to user1;

SQL>grant create tablespace to user1;

3. Create tablespaces:

SQL> create tablespace t1 datafile ‘/u01/user38/test/t01.dbf’ size 100m autoextend on;

Similarly create 3 more tablespaces t2,t3,t4

SQL>alter user user1 quota unlimited on t1, quota unlimited on t2, quota unlimited on t3, quota unlimited on t4;

SQL>conn user1/user1

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-2007” will contain all the rows that have the partition key values between 01-JAN-2007 and 31-JAN-2007 (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) );

d. Composite Partitioning: A combination of 2 data distribution methods are used to create a composite partition. The table is initially partitioned by the first data distribution method and then each partition is sub-partitioned by the second data distribution method. The following composite partitions are available:
Range-Hash, Range-List, Range-Range, List-Range, List-List and List-Hash.

SQL> create table emp_composit(empid number(4),empname  varchar2(10),empdoj date) PARTITION BY RANGE(empdoj) 

SUBPARTITION BY HASH(empid)

SUBPARTITION TEMPLATE(SUBPARTITION sp1 TABLESPACE t1,

SUBPARTITION sp2 TABLESPACE t2, SUBPARTITION sp3 TABLESPACE t3,

SUBPARTITION sp4 TABLESPACE t4) (PARTITION emp_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))  PARTITION emp_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))  PARTITION emp_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))  PARTITION emp_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))

 PARTITION emp_may2000 VALUES LESS THAN (TO_DATE('06/01/2000','DD/MM/YYYY')));

Next: **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;