Tuesday, 8 May 2012

Partitioned Tables: Types and Advantages

As the number of rows in your tables grows, the management and performance impacts will increase. Backups will take longer, recoveries will take longer, and queries that span an entire table will take longer. You can mitigate the administrative and performance issues for large tables by separating the rows of a single table into multiple parts.

Advantages of Partitioned Tables

1. The performance of queries against the tables may improve because Oracle may have to search only one partition (one part of the table) instead of the entire table to resolve a query.

2. The table may be easier to manage. Because the partitioned table's data is stored in multiple parts, it may be easier to load and delete data in the partitions than in the large table.

3. Backup and recovery operations may perform better. Because the partitions are smaller than the partitioned table, you may have more options for backing up and recovering the partitions than you would have for a single large table.

Types of Partition

1. Range Partition: Divides the table according to the specified range.

Example: Create a Table EMP

create table EMP (EmpID  VARCHAR2(32) primary key, EmpName  VARCHAR2(100));

Lets make partition of this table:

create table EMP (EmpID  VARCHAR2(32) primary key, EmpName  VARCHAR2(100))

partition by range (EmpName)

(partition PART1 values less than ('K') tablespace PART1_TS,
partition PART2 values less than (MAXVALUE) tablespace PART2_TS);

Explanation: The EMP table will be partitioned based on the values in the EmpName column:

partition by range (EmpName)

For any EmpName values less than ‘K’, the records will be stored in the partition named PART1. The PART1 partition will be stored in the PART1_TS tablespace. Any other EmpName will be stored in the PART2 partition.

Note that in the PART2 partition definition, the range clause is

partition PART2 values less than (MAXVALUE)

You do not need to specify a maximum value for the last partition; the maxvalue keyword tells Oracle to use the partition to store any data that could not be stored in the earlier partitions.

2. Hash Partition: A hash partition determines the physical placement of data by performing a hash function on the values of the partition key. In range partitioning, consecutive values of the partition key are usually stored in the same partition. In hash partitioning, consecutive values of the partition key are not generally stored in the same partition. Hash partitioning distributes a set of records over a greater set of partitions than range partitioning does, potentially decreasing the likelihood for I/O contention.

3. List Partition: In list partitioning, you tell Oracle all the possible values and designate the partitions into which the corresponding rows should be inserted.

4. SubPartitions: You can create subpartitions—that is, partitions of partitions. You can use subpartitions to combine all types of partitions: range partitions, list partitions, and hash partitions. For example, you can use hash partitions in combination with range partitions, creating hash partitions of the range partitions. For very large tables, this composite partitioning may be an effective way of separating the data into manageable and tunable divisions

No comments:

Post a Comment