Tuesday, January 14, 2014

all about partitioning

What is partition?

This is a wonderful concept provided by oracle to break larger tables/indexes/index organized tables etc.  into much smaller,manageable pieces.

When to partition?

1)Generally when the table grows more than 2 GB data are suitable candidate for partitioning
2)When table contains historical data and application is interest in a subset of data only.
3)When you want to spread the data of table to spread across the different storage device (obviously having different access speed)

4)Index partitioning is done mainly in view of manageability.
5)It provides performance, manageability and availability in case of index organized table as well.

Why partition ?
1)Performance [dramatic in case of ]
2)Manageability  [more DBA thing]
2)Availability  [more DBA thing]

Partition strategies offered by oracle

1)Range Partitioning
2)List Partitioning
3)Hash Partitioning

derived partitioning strategies
4)Interval Partitioning
5)Reference Partitioning
6)System Partitioning
7)Virtual column based Partitioning

     All these things has to be decided at the database design phase. Sometime we are not sure about the data access pattern, size of the database and off-course the tiered device available. Oracle has solution to this problem as well. We can use partition exchange mechanism to achieve this goal. It requires downtime to complete the process . But when we are very much concern about the downtime and really really don't want to ask for measurable downtime oracle provide another wonderful mechanism .
    There is oracle supplied dbms_redefinition package available in database that can be used to change the partition of table/index/index organized table online. We will do a small demonstration on changing the partition strategy of partitioned table.

Step 1: Ask DBA to provide necessary privilege on dbms_redefinition package.

grant execute on dbms_redefinition ;
create synonym dbms_redefinition for sys.dbms_redefinition.