Table partitioning

 

                                       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) );

 

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;