Consider a scenario where you have huge number of records(say 1 million) in a database table and you are required to select 100,000 records from that table based on a condition given in where clause. In most cases it will take more time to retrieve the records from that huge table as the DBMS should select a subset from the huge super set. One simple solution is to use SQL query hints, forcing indexes etc in the select query. But the query running time changed based on certain DB parameters. Another sophisticated solution will be to partition DB table. In oracle a DB table can be partitioned based on a column.
There are 4 types of table partition such as
Range partition - Partition tables based on columns which has usually less number of unique values.
Hash Partition - Here the user should specify the column based on which the table is to partitioned and the number of partitions to be created, the DBMS generates hash key for each value in the specified column to be partitioned and distributes it across the partitions
List Partition - This is an extension of Hash partition, but the user can specify what are all the set of column values should come in each partition created.
Composite Partition - This is a combination of range and hash partition (i.e) the table is partitioned using range partition and internally the partitions can be sub partitioned based on hash
Lets take a deep dive in to Range partition in this section.
Now lets create an employee table and partition it using date of joining. The table description is below.
(
EMP_ID NUMBER NOT NULL,
EMP_NAME VARCHAR2(256 BYTE),
SALARY NUMBER,
CITY VARCHAR2(256 BYTE),
DEPT_ID NUMBER,
DOJ DATE
)
PARTITION BY RANGE (DOJ)
(
PARTITION RANGE_1980 VALUES LESS THAN ('01-JAN-1980'),
PARTITION RANGE_1985 VALUES LESS THAN ('01-JAN-1985'),
PARTITION RANGE_1990 VALUES LESS THAN ('01-JAN-1990'),
PARTITION RANGE_1995 VALUES LESS THAN ('01-JAN-1995'),
PARTITION RANGE_2000 VALUES LESS THAN ('01-JAN-2000'),
PARTITION RANGE_2005 VALUES LESS THAN ('01-JAN-2005'),
PARTITION RANGE_MAX VALUES LESS THAN ('01-JAN-2010'),
PARTITION DEFAULT_PARTITION VALUES LESS THAN (MAXVALUE)
)
The above table created will be partitioned based on employees date of joining. It creates 8 partitions including the default partition. The partition RANGE_1980 will contain records of employees who have joined earlier than '01-JAN-1980'. Similarly the other partitions will contain employee records with joining date less than the given partition range and greater than the previous partition range. For example, RANGE_2000 will contain employee records with DOJ greater than '01-JAN-1995' and less than '01-JAN-2000'. Default Partition will contain employee records whose DOJ do not fall in to the other partitions.
Now when you query for employee records who have joined earlier than '01-JAN-1980', the oracle DBMS will internally search in the partition RANGE_1980.
Alternatively you can increase the query running time by directly querying records from a particular partition using the following query
We can get the list of partitions for a given partitioned table by using the oracle meta data tables as follows
Consider a scenario where you update the DOJ column for an employee from '01-JAN-1979' to '01-JUN-1980'.
Now logically the record should be moved from RANGE_1980 to RANGE_1985.
But in reality the update query will fail with the following Oracle error.
ORA-14402: updating partition key column would cause a partition change
How to resolve the above error! It is as simple as using the following alter statement
Now the update statement will work fine. Alternatively you can achieve the same result by adding the clause
ENABLE ROW MOVEMENT at the end of the create table DDL statement.
Use table partitioning to partition huge tables and increase the query retrieval time.