Oracle Database Partitions Types in 10g..

Hi Guys, Today will discuss about Oracle Database Partitions...


Partitioning is an approach or an option which is supplied by oracle  which splits the tables into small chunks.

Main Purpose :-

> To enhance the SQL performance by scanning a small set of rows instead of  complete table.

> To reduce the time in backups.

>  Index Range Scan Become more Inefficient.

> In 11g You Can Transport the Tablespace with the Single Partitions.


When to Partitioned a Table?

1. Oracle recommends When table size is reached up-to 2gb with high I/O is always considered to be partitioned.

2. Tables which are considering historical data where only current data is maintained and the old data will be Archived on Storage.


Some Basic and Mostly used Partitions Types up-to 10g :- 


   Range Partitions : If your Business data is on Range Basis it will support By Ranges Ex:- Dates

 >  Below i have created a Range-partitioned table which distribute the data Range-Wise
    on monthly basis.

>  In this Range-Partition table i gave 3 partitions (p1,p2,p3) which distributes the data on the range of months
    in the same tablespace.

>  This partition has created on the join_date column.

Example of Range Partition :-

SQL> create table emp_part
    (
    empno number(5),
    ename varchar2(5),
    job varchar2(5),
    join_date date
    )
    partition by range (join_date)
    (
   partition p1 values less than (TO_DATE ('01-JAN-2017','DD-MON-YYYY')) tablespace ts4,
   partition p2 values less than (TO_DATE ('01-FEB-2017','DD-MON-YYYY')) tablespace ts4,
   partition p3 values less than (TO_DATE ('01-MAR-2017','DD-MON-YYYY')) tablespace ts4
   );

Table created.

                                                          =====  XXX =====

 List Partitions : The main advantage of List Partition is you can Group and Organize Un-ordered set of  data in a natural way. We can distribute the data in many ways by using List Partitions for example if your Business data is on Region,Designation or Country basis we can easily distributes this type of  data by making List Partitions.

> Below Example i have created Partition-table on LIST basis for job Designations.

SQL> create table emp_part
    (
    empno number(4),
    ename varchar2(10),
    job varchar2(9),
    mgr number(4),
    hiredate date,
    sal number(7,2),
    comm number(7,2),
    deptno number(2)
    )
    partition by list (job)
    (
    partition p1 values ('CLERK'),
    partition p2 values ('SALESMAN'),
    partition p3 values ('MANAGER'),
    partition p4 values ('ANALYST'),
    partition p5 values ('PRESIDENT'));

SQL> SELECT OWNER,TABLE_NAME,PARTITIONING_TYPE FROM ALL_PART_TABLES WHERE OWNER='SAM';

OWNER                  TABLE_NAME       PARTITIONING_TYPE

------------------------------ ------------------------------ ----------------------

SAM                            EMP_PART                             LIST

                                                    ==== XXX ====

  Hash Partitioning : is a technique in which oracle maps data to a partitions Based on a Hashing algorithm.  This hashing algorithm evenly distributes Rows among partitions by giving same size.
       It is used with many distinct values (not identical) where there are no searches on ranges.

Syntax :-

 CREATE TABLE EMP_HASH
(
EMPNO NUMBER(5),
ENAME VARCHAR2(5),
LOCATION VARCHAR2(5))
PARTITION BY HASH (LOCATION)
(
PARTITION PH1 TABLESPACE USERS,
PARTITION PH2 TABLESPACE USERS
);

Table created.



Happy Learning...