PARTITIONing tables in Oracle Database

PARTITION is a small portion of large table.Partitioning a table will improve the performance, manageability and availability.

When do we need to partition the table?

It is good when

• Tables having large volume of data
• Tables having historical data and you need to only access latest data.A simple example would be is a table in which you need to load current month data and rest of data is just storage In this case partitions will be beneficial to manage tables. 
• You supposed to store table data in different tablespace

Partition Types

There are several partition techniques,but in this blog we will see below partition types and how they work.

1. RANGE Partitions
2. LIST Partitions
3. HASH Partitions
4. INTERVAL Partitions

1.RANGE Partition

In this technique partitions will be defined based on range of partition key values.Here we will mention range value so that row values under this range will be inserted in that particular partition.

Example:

Partitioning on Number type partition key

SQL>CREATE TABLE P_EMP_RANGE 
(
   EMPNO NUMBER(10),
   ENAME VARCHAR2(80),
   SAL   NUMBER
)
PARTITION BY RANGE(EMPNO) 
(
  PARTITION P1 VALUES LESS THAN (1000),
  PARTITION P2 VALUES LESS THAN (2000),
  PARTITION P3 VALUES LESS THAN (MAXVALUE)
); 

MAXVALUE will be provided to capture rest of rows which are not fitting to any partition.

We can also mention table space name in the partition definition as shown.

 PARTITION P1 VALUES LESS THAN (1000)     TABLESPACE DATA1,
 PARTITION P2 VALUES LESS THAN (2000)     TABLESPACE DATA2, 

Insert data into RANGE partitioned table.

SQL>INSERT INTO P_EMP_RANGE VALUES (123,’EMP1’,23000);

This row will be inserted to p1 partition since partition key empno value 123 which is less than to p1 maxvalue 1000.

SQL>INSERT INTO P_EMP_RANGE VALUES (1000,’EMP2’,23000);
SQL>INSERT INTO P_EMP_RANGE VALUES (1001,’EMP3’,23000);

Those 2 rows will be inserted into next partition p2 as those are satisfying p2 condition.

SQL>INSERT INTO P_EMP_RANGE VALUES (3001,'EMP4',21000);

This one will be inserted into p3 partition.

Partitioning on String type partition key

SQL>CREATE TABLE P_EMP_RANGE_STR
( 
    EMPID        NUMBER(10),  
    EMPNAME      VARCHAR2(50),  
    EMAIL        VARCHAR2(100)
)
PARTITION BY RANGE ( EMPNAME )
( 
       PARTITION P1 VALUES LESS THAN ('L'),
       PARTITION P2 VALUES LESS THAN (MAXVALUE)
);

empname which starts after letter ‘L” will be stored in partition p2 and rest will be in p1.

SQL>INSERT INTO P_EMP_RANGE_STR VALUES (123,'ABC','EMAIL1'); --will be inserted into p1
SQL>INSERT INTO P_EMP_RANGE_STR VALUES (123,'LABC','EMAIL2'); --will be inserted into p1
SQL>INSERT INTO P_EMP_RANGE_STR VALUES (123,'MABC','EMAIL3'); --will be inserted into p2
SQL>INSERT INTO P_EMP_RANGE_STR VALUES (123,'lABC','EMAIL4'); --will be inserted into p1
SQL>INSERT INTO P_EMP_RANGE_STR VALUES (123,'mABC','EMAIL5'); --will be inserted into p2

Partitioning on Date type partition key

SQL>CREATE TABLE P_TAB_RANGE_TIME 
(
   SERIAL_NUM NUMBER, 
   JOIN_DT DATE
)
PARTITION BY RANGE (JOIN_DT)
(
   PARTITION P1 VALUES LESS THAN (TO_DATE('2007-01-01', 'YYYY-MM-DD')),
   PARTITION P2 VALUES LESS THAN (TO_DATE('2008-01-01', 'YYYY-MM-DD')),
   PARTITION P3 VALUES LESS THAN (TO_DATE('2009-01-01', 'YYYY-MM-DD')),
   PARTITION P4 VALUES LESS THAN (MAXVALUE)
);

SQL>INSERT INTO P_TAB_RANGE_TIME VALUES (12,TO_DATE(‘2006-11-01’, 'YYYY-MM-DD'));--will be inserted into p1
SQL>INSERT INTO P_TAB_RANGE_TIME VALUES (13,TO_DATE(‘2007-01-01’, 'YYYY-MM-DD'));--will be inserted into p2
SQL>INSERT INTO P_TAB_RANGE_TIME VALUES (14,TO_DATE(‘2008-11-01’, 'YYYY-MM-DD'));--will be inserted into p3

2.LIST Partitioning

In this technique we will explicitly mention the list of discrete values for partition key so that rows with that value will be inserted into particular partition. 

i.e we map the rows to partitions with the discrete values. 

Example:

SQL>CREATE TABLE P_EMP_LIST 
(
       EMPID   NUMBER,
       ENAME    VARCHAR2(80),
       SALARY   NUMBER(8,2),
       DEPTNO   NUMBER
)
  PARTITION BY LIST (DEPTNO) 
(  
       PARTITION P_10 VALUES (10), 
       PARTITION P_20 VALUES (20), 
       PARTITION P_30 VALUES (30,40)
);

SQL>INSERT INTO P_EMP_LIST VALUES (12,’ABC’,2098,10); —Will be inserted into p_10
SQL>INSERT INTO P_EMP_LIST VALUES (13,’XYZ’,1098,20); —Will be inserted into p_20
SQL>INSERT INTO P_EMP_LIST VALUES (14,’XYZABC’,3081,30); —Will be inserted into p_30
SQL>INSERT INTO P_EMP_LIST VALUES (15,'XYZABCIJK',3081,40); —Will be inserted into p_30

3.HASH Partitioning

In this technique hash key is used to evenly distribute the rows into different partitions.

It is useful when there is no option to implement RANGE or LIST. For instance, if you supposed to partition the table based on any ID column or system generated value columns.

Example:

SQL>CREATE TABLE P_EMP_HASH (
   EMPID NUMBER(10), 
   ENAME VARCHAR2(80), 
   SAL   NUMBER
) 
PARTITION BY HASH(EMPID) 
(
  PARTITION E1, 
  PARTITION E2, 
  PARTITION E3
); 
---OR
SQL>CREATE TABLE P_EMP_HASH
(
   EMPID NUMBER(10), 
   ENAME VARCHAR2(80), 
   SAL   NUMBER
) 
PARTITION BY HASH(EMPID)
PARTITIONS 3;

Automatically HASHING algorithm created below 3 partitions when I execute second sql,

SYS_P21

SYS_P22

SYS_P23

4.INTERVAL Partitioning

This is an additional feature to the RANGE partition in which database creates a new partition if inserted value exceeds all the partitions range values.

Note: In this technique partitioning must be NUMBER or DATE type.

Example:

Partitioning on Number type key column

SQL>CREATE TABLE P_EMP_INTRVL
(
    EMPID NUMBER(10),
    EMPNAME VARCHAR2(30),
    SALARY NUMBER(6)
)
PARTITION BY RANGE(SALARY)
INTERVAL  (2000)
(
    PARTITION P1 VALUES LESS THAN (2000),
    PARTITION P2 VALUES LESS THAN (4000),
    PARTITION P3 VALUES LESS THAN (6000)
)
ENABLE ROW MOVEMENT;

When you try to insert record which has salary value more than 6000 then it will create new partition with the name SYS_Pxx then inserts row values.

Partitioning on Date type key column

SQL>CREATE TABLE P_EMP_DATE_INTRVL
(
    EMPID NUMBER(10),
    EMPNAME VARCHAR2(30),
    HIRE_DT DATE,
    SALARY NUMBER(6)
)
PARTITION BY RANGE(HIRE_DT)
INTERVAL  (NUMTOYMINTERVAL(1,'MONTH'))
(
    PARTITION P1 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD'))
)
ENABLE ROW MOVEMENT;

Note: Here we used Oracle/PLSQL NUMTOYMINTERVAL function that converts number to an INTERVAL YEAR to MONTH literal..
and there is another function NUMTODSINTERVAL that converts a number to an INTERVAL DAY to SECOND literal.

example:

SELECT

NUMTOYMINTERVAL(2,'YEAR'), -- Result is +02-00

NUMTOYMINTERVAL(11,'MONTH'), -- Result is +00-11 

NUMTODSINTERVAL(2, 'DAY'), -- Result is +02 00:00:00.000000 

NUMTODSINTERVAL(2, 'HOUR'), -- Result is +00 02:00:00.000000 

NUMTODSINTERVAL(59, 'MINUTE'), -- Result is +00 00:59:00.000000

NUMTODSINTERVAL(59, 'SECOND') -- Result is +00 00:00:59.000000

FROM DUAL;