Description:
Every data should be stored in the database because the world can’t run without storing the data’s in the database. Machinery world should save data in a database for future reference and process so that the database will be filled up of data. The records will be more and more, and the size of the data might be Terabytes 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
select sum(salary) from sales where year=1991;
select empName,sum(salary) from sales where year=1992
Group by empName;
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 improved since oracle will scan only a single partition instead of the entire table.
CREATING PARTITION TABLES
To create a partition table gives the following statement.
create table employee(empid number(4),
empName 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, the employee table is created with 5 partitions. Partition p1 will contain rows of the year 2007, and it will be stored in tablespace u1.
Partition p2 will contain rows of the year 2008, and it will be stored in tablespace u2.
Similarly, p3 and p4.
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 the 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 the rest of the partitions will still be available.
In 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 the 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 empName
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.
Summary:
Table partitioning is about optimizing “medium selectivity queries.” The Oracle database has optimization techniques for high selectivity queries, with the use of indexes. If we need to process all data in a big table, we have to live with the fact it will take a while, but the engine.
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.comor use the comment section below to ask your questions.
