- Every data’s should be stored in database, because world can’t run without storing the data’s in the database.
- Machinery world should store data’s in database for future reference and process, so the database will be filled up of data’s.
- The records will be more and more and the size of the data might be Tera bytes of ranges. These databases are known as Very Large Databases (VLDB).
- Oracle has provided the feature of table partitioning i.e. we can partition a table according to some criteria.
For example we have an EMPLOYEE table with the following structure,
Suppose this table contains millions of records, but all the records belong to four years only i.e. 2007, 2008, 2009 and 2010. And most of the time we are concerned about only one or two years i.e. we give queries like the following
Employee Table Structure
Column Data Type
Emp_id Number(4)
Emp_Name Varchar2(10)
Salary Number(10,2)
select sum(salary) from sales where year=1991;
select Emp_Name,sum(salary) from sales where year=1992
Group by Emp_Name;
Now whenever you give queries like this Oracle will search the whole table. If you partition this table according to year, then the performance is improve since oracle will scan only a single partition instead of whole table.
CREATING PARTITION TABLES
- To create a partition table gives the following statement
Create table employee(emp_id number(4),
emp_name varchar2(10),
salary number(10,2))
partition by range (year)
partition p1 values less than (2007) tablespace u1,
partition p2 values less than (2008) tablespace u2,
partition p3 values less than (2009) tablespace u3,
partition p4 values less than (2010) tablespace u4,
partition p5 values less than (MAXVALUE) tablespace u5;
- In the above example employee table is created with 5 partitions. Partition p1 will contain rows of year 2007 and it will be stored in tablespace u1.
- Partition p2 will contain rows of year 2008 and it will be stored in tablespace u2. Similarly p3 and p4 have in the respective partitions.
- In the above example if we don’t specify the partition p4 with values less than MAXVALUE, then we will not be able to insert any row with year above 2010.
- Although not required, we can place partitions in different tablespaces. If we place partitions in different tablespaces then we can isolate problems due to failures as only a particular partition will not be available and rest of the partitions will still be available.
The above example the table is partition by range.
- In Oracle we can partition a table by using the following partition methods,
- Range Partitioning
- Hash Partitioning
- List Partitioning
- Composite Partitioning
ALTERING PARTITION TABLES
- To add a partition, we can add a new partition to the "high" end (the point after the last existing partition). To add a partition at the beginning or in the middle of a table, use the SPLIT PARTITION clause.
- For example to add a partition to employee table give the following command
alter table employee add partition p6 values less than (2008);
- To add a partition to a Hash Partition table give the following command.
Alter table employee add partition;
- Then Oracle adds a new partition whose name is system generated and it is created in the default tablespace.
- To add a partition by user define name and in your specified tablespace give the following command.
Alter table employee add partition p5 tablespace u5;
- To add a partition to a List partition table give the following command.
alter table employee add partition emp_name
values (‘JONES’,’SMITH’);
- Any value in the set of literal values that describe the partition(s) being added must not exist in any of the other partitions of the table.
