Different types of Oracle Partitioning Range, Hash, List, Interval

advertisements

_____________________________________________________________________________________________________________________

Introduction

            Partitioning is a technique which allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Oracle provides different varieties of partitioning strategies applicable to different kinds of business requirement. Partitioning is entirely transparent and can be applied to almost any kind of applications

What is Partitioning Key

Partitioning key is comprised of one or more columns that determine the partition where each row will be stored. Oracle automatically directs insert, update and delete operations to the appropriate partition through the use of partitioning key.

When to partition table: suggestions

·         Tables more than 2GB in size can be considered for candidates for partition

·         Historical data tables where recent months data is frequently used

·         Huge table needs to be distributed across the different types of storage devices

How benefited in Indexes

·         Avoid rebuilding the entire index when data is removed

·         Can perform maintenance on part of data without invalidating the entire index

Types of partitions: Different strategies

1.      Range

·         This is the commonly used partition technique and is often used with date as the partitioning key.

·         In Range partition the partitions are identified on the range of values of the partitioning key.

·         Each partition has a VALUES LESS THAN clause, which specifies a non-inclusive upper bound for the partitions. Any values of the partitioning key equal to or higher than this key are added to the next higher partition.

·         MAXVALUE literal can be defied for the highest partition.

2.      Hash

·         Oracle uses hashing algorithm on the partitioning key for the data mapping to the partitions.

·         Hash algorithm evenly distributes the row among partitions, so the partitions would be approximately same size.

·         Hash is Ideal for distributing the data across devices.

·         It is more suits for the non historical data or has no obvious partitioning key

3.      List

·         More suits for a list of discrete values for the partitioning key

·         Can group and organize unordered and unrelated set of data in a natural way

·         DEFAULT partition enables you to avoid specifying all possible values for a list-partitioned table by using a default partition, so that all rows that do not map to any other partition do not generate any error.

List, Range and Hash Partitioning

 4.      Interval

·         Introduced in Oracle 11g

·         Interval partitions are extensions to range partition

·         Technology provides automation for equi-sized range partitions.

·         Syntax Keyword: INTERVAL (NUMTOYMINTERVAL(1,'month') numtoyminterval function converts a number to an INTERVAL YEAR TO MONTH literal(‘YEAR’ or ‘MONTH’)

·         Using SET INTERVAL option of ALTER TABLE command can extend a range partitioned table to interval partitioned table

5.      REF

·         Based on the Primay-foreign key relationship

·         No need for the partitining key to be stored in detail table by specifying “PARTITION BY REFERENCE” keyword, as the detail table inherits the partitioning strategy from the master table.

6.      Virtual column based

·         Introduced in Oracle 11g

·         Virtual columns are made up of functions like to_char, substr, length etc

·         Expressions that use one or more columns of table considered as the virtual column partition key.

·         Virtual columns are stored as metadata only

Example. CREATE TABLE users (

                        id           NUMBER,

                        username     VARCHAR2(20),

                        first_letter VARCHAR2(1)

                        GENERATED ALWAYS AS      (

UPPER(SUBSTR(TRIM(username), 1, 1))) VIRTUAL)

PARTITION BY LIST (first_letter)

(

                        PARTITION part_a_g VALUES ('A','B','C','D','E','F','G'),

PARTITION part_h_n VALUES ('H','I','J','K','L','M','N'),

PARTITION part_o_u VALUES ('O','P','Q','R','S','T','U'),

                        PARTITION part_v_z VALUES ('V','W','X','Y','Z'));

What is Composite Partitioning?

·         Combination of above mentioned basic data distribution methods – Range, Hash, List

·         A table is partitioned by one distribution method and then each partition is further subdivided into sub partitions using a second data distribution method.

·         Supports historical operations

·         This provides higher degree of partition pruning and finer granularity of data placement through sub partitioning. 

Composite Partitioning

 Benefits of partitioning

·         Performance improvement

·         Increases availability

·         Better manageability

·         Cost-efficient use of storage

·         Faster backup – Partitions can be backed up, instead of backing up the entire table.

·         Import /export backups can be done at the Partition Level

·         Stable – Oracle partitioning is stable technology since Oracle 8

·         Faster query performance – SQL performance may improve the execution speed by several orders of magnitude ( over 100x faster)

·         Partition wise maintenance is independent of the other partitions

·         Reduced recovery time

·         Partition - Smaller and more manageable pieces of data

Note:- Partitions can improve or decrease the performance on how you use it which  means the lack of planning make your SQL performance slow.

Some facts on partitions – Practical scenarios

How to partition existing non partitioned table

i.            Exp/imp utility

a.       Take export of non partitioned table

b.      Drop the non partitioned table

c.       Recreate the table with partitions

d.      Import the table with ignore=y. ignore=y option skips the table creation error and continue with import

ii.            Duplicate table

a.       Create a duplicate of non-partitioned(with different name and same structure) table with partitions

b.      Insert the data into partitioned table from non-partitioned table

c.       Drop non-partitioned table

d.      Rename the duplicate table to original table name

iii.            Partition exchange

ALTER TABLE EXCHANGE PARTITION can convert a partition into a non-partitioned table and vice versa

a.       Create duplicate table of non-partitioned table with required partitions

b.      Alter table EXCHANGE partition <partition_name>
with <non_partition_table_name>;

Example.

Suppose we have a non-partitioned order table with order as name. These are the steps to create a partitioned table

1.      CREATE TABLE order_part
(ordnum NUMBER(7,2))
PARTITION BY RANGE(ordnum)
(partition order_p1 VALUES LESS THAN (2000),
partition order_p2 VALUES LESS THAN (4000));

2.      Create table part1 as select * from order where ordnum<2000;

3.      Create table part2 as select * from order where ordnum between 2000 and 3999;

4.      Alter table part1 exchange partition order_p1 with table part1;

5.      Alter table part2 exchange partition order_p1 with table part2;

Partition methods in Detail with Examples 

_____________________________________________________________________________________________________________________

Website Stats