TABLE PARTITIONING

Dividing the rows of a single table into multiple parts is called Partitioning of a table.
Partitioning  is useful for a large tables only(Tables greater than 2 GB should always
be considered as candidates for partitioning).

Goals Behind Partitioning

• The performance of queries against the tables can improve.
• The management of the table became easier.
• The backup and recovery operation can be performed better.
• It is easier to load and delete data in partitions than in the large table.

Type of  Table Partition

1. Range Partition Table
2. List Partition Table
3. Hash Partition Table

1. Range Partition Table:-

• The table is divided in ranges(data ranges).
• The Range partition works on filters like greater than ,less than and between operator.
• Used when there are logical ranges of data.

Step to create Range Partition Table:-

• Table cretion

    CREATE TABLE EMP_DETAILS
      (
       EMPNO       NUMBER (4),

       ENAME       VARCHAR2 (10),

      JOB              VARCHAR2 (9),

      HIREDATE   DATE,

      DEPTNO     NUMBER (2)
     )

   PARTITION BY RANGE(DEPTNO)
     (
      PARTITION  PTNDEPTNO10 VALUES LESS THAN (20),
      PARTITION PTNDEPTNO20 VALUES LESS THAN  (30),
      PARTITION PTNDEPTNOMAX  VALUES LESS THAN(MAXVALUE)
    );

• Now insert below data into the EMP_DETAILS table

BEGIN

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7369, 'SMITH', 'CLERK', TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 20);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7499, 'ALLEN', 'SALESMAN', TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30);

Insert into EMP_DETAILS(EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7521, 'WARD', 'SALESMAN', TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7566, 'JONES', 'MANAGER', TO_DATE('04/02/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 20);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values(7654, 'MARTIN', 'SALESMAN', TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7698, 'BLAKE', 'MANAGER', TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7782, 'CLARK', 'MANAGER', TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10);

Insert into EMP_DETAILS(EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7788, 'SCOTT', 'ANALYST', TO_DATE('04/19/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 20);

Insert into EMP_DETAILS(EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values(7839, 'KING', 'PRESIDENT', TO_DATE('11/17/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10);

Insert into EMP_DETAILS(EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values(7844, 'TURNER', 'SALESMAN', TO_DATE('09/08/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30);

Insert into EMP_DETAILS(EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7876, 'ADAMS', 'CLERK', TO_DATE('05/23/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 20);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values(7900, 'JAMES', 'CLERK', TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30);

Insert into EMP_DETAILS(EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values(7902, 'FORD', 'ANALYST', TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 20);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7934, 'MILLER', 'CLERK', TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10);

COMMIT;
END;

• Now check how many partition created for this EMP_DETAILS table

         SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS
         FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMP_DETAILS';

• Now Fetch the date from your partition table

          SELECT * FROM EMP_DETAILS PARTITION(PTNDEPTNO10);

2. List Partition Table:-

• Rows map to Partition by a specific list.
• Used to list together unrelated data into partitions.

Step to create List Partition Table:-

• Table creation 

  CREATE TABLE EMP_DETAILS_LIST
     (
     EMPNO      NUMBER (4),
     ENAME      VARCHAR2 (10 BYTE),
     JOB        VARCHAR2 (9 BYTE),
     HIREDATE   DATE,
     DEPTNO     NUMBER (2)
    )
  PARTITION BY LIST(JOB)
   (
   PARTITION MGR_PRE01 VALUES ('MANAGER','PRESIDENT','ANALYST'),
   PARTITION SAL_CLE VALUES ('CLERK','SALESMAN')
  );

• Now insert above set of data into the EMP_DETAILS_LIST table by changing the table name.

• Now Fetch the date from your partition table

         SELECT * FROM  EMP_DETAILS_LIST PARTITION(SAL_CLE);

3. Hash Partition Table:-

1. Some time it may not possible to define the range or list in partition in such case hash partition is useful. 
2. Used to spread data evenly over partitions 
3. All the Hash partition hold same number of data.

Step to create Hash Partition Table:-

• Table creation 

      CREATE TABLE EMP_DETAILS_HASH
          (
           EMPNO      NUMBER (4),
           ENAME      VARCHAR2 (10 BYTE),
           JOB        VARCHAR2 (9 BYTE),
           HIREDATE   DATE,
           DEPTNO     NUMBER (2)
          )
     PARTITION BY HASH(ENAME)
     PARTITIONS 5;

• Now insert above set of data into the EMP_DETAILS_HASH table by changing the table name.
• Now check how many partition created for this EMP_DETAILS table

          SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS

          FROM USER_TAB_PARTITIONS 

          WHERE TABLE_NAME='EMP_DETAILS_HASH';

• Now check how many partition created for this EMP_DETAILS tableNow Fetch the date from your partition table

        SELECT * FROM EMP_DETAILS_HASH PARTITION(SYS_P29);



I am trying to add a new partition in existing table:-

1. create a PARTITIONED table
2. load the NON-PARTITIONED data into the PARTITIONED table
3. drop the NON-PARTITIONED table
4. rename PARTITIONED table to whatever is appropriate

or:

1. export table and data
2. drop table
3. create new partitioned table the way you want it

4. import table data into new partitioned table (this should also create all the necessary indexes and constraints from your original table)

Dropping a table Partition:-

ALTER TABLE Table_Name DROP PARTITION Partition_Name;

CREATING INDEXES UPON PARTITIONS:-

Once a partition a table created then we need to create an index upon that table.

The index may be partitioned according to the same range of values(which we used for partition)

The LOCAL key word tells oracle to create index for each partitions for table.

The GLOBAL keyword tells to oracle to create a non partition index .

Steps to create the index

CREATE INDEX index_name ON table_name(clolumn_name)

LOCAL(PARTITION partittion_name1,...,partittion_name);