It's All About ORACLE: Various Partitioning Strategies

So now you know partitioning in oracle now the only thing that you need to know is little bit of syntax and that’s it, and you are a partitioning guru.

Oracle introduced partitioning with 8.0. With this version only, " Range Partitioning" was supported. I will come to details later about what that means. Then with Oracle 8i " Hash and Composite Partitioning" was also introduced and with 9i " List Partitioning", it was introduced with lots of other features with each upgrade. Each method of partitioning has its own advantages and disadvantages and the decision which one to use will depend on the data and type of application. Also one can MODIFY , RENAME, MOVE, ADD, DROP, TRUNCATE, SPLIT partitions. We will go through the details now.

When you create (or alter) a partitioned table, a row movement clause, either ENABLE ROW MOVEMENT or DISABLE ROW MOVEMENT can be specified. This clause either enables or disables the migration of a row to a new partition if its key is updated. The default is DISABLE ROW MOVEMENT.

RANGE Partitioning

This type of partitioning creates partitions based on the " Range of Column" values. Each partition is defined by a " Partition Bound" (non inclusive ) that basically limits the scope of partition. Most commonly used values for " Range Partition" is the Date field in a table. Lets say we have a table SAMPLE_ORDERS and it has a field ORDER_DATE. Also, lets say we have 5 years of history in this table. Then, we can create partitions by date for, lets say, every quarter.

So Every Quarter Data becomes a partition in the SAMPLE_ORDER table. The first partition will be the one with the lowest bound and the last one will be the Partition with the highest bound. So if we have a query that want to look at the Data of first quarter of 1999 then instead of going through the complete data it will directly go to the Partition of first quarter 1999.

This is example of the syntax needed for creating a RANGE PARTITION.

CREATE TABLE SAMPLE_ORDERS

(ORDER_NUMBER NUMBER,

ORDER_DATE DATE,

CUST_NUM NUMBER,

TOTAL_PRICE NUMBER,

TOTAL_TAX NUMBER,

TOTAL_SHIPPING NUMBER)

PARTITION BY RANGE(ORDER_DATE)

(

PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),

PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),

PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),

PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),

PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)

);

the above example basically created 8 partitions on the SAMPLE_ORDERS Table all these partitions correspond to one quarter. Partition SO99Q1 will contain the orders for only first quarter of 1999.
MAXVALUE is provided as a catch-all for values that exceed all ranges specified. Note that Oracle sorts NULLs greater than all other values, except MAXVALUE.

Partition on a numeric value range:

CREATE TABLE emp (
   empno NUMBER(4), 
   ename VARCHAR2(30), 
   sal   NUMBER
) 
PARTITION BY RANGE(empno) (
  partition e1 values less than (1000)     tablespace ts1, 
  partition e2 values less than (2000)     tablespace ts2, 
  partition e3 values less than (MAXVALUE) tablespace ts3
);

Partition on a VARCHAR2 string:

CREATE TABLE emp
( id        NUMBER(5)    PRIMARY KEY,
  name      VARCHAR2(50) NOT NULL,
  phone     VARCHAR2(15),
  email     VARCHAR2(100) )
PARTITION BY RANGE ( name )
     ( PARTITION p1 VALUES LESS THAN ('L')      TABLESPACE ts1,
       PARTITION p2 VALUES LESS THAN (MAXVALUE) TABLESPACE ts2 )

Time based range partitioning:

CREATE TABLE t1 (id NUMBER, c1 DATE)
PARTITION BY RANGE (c1)
  (PARTITION t1p1 VALUES LESS THAN (TO_DATE('2007-11-01', 'YYYY-MM-DD')),
   PARTITION t1p2 VALUES LESS THAN (TO_DATE('2007-12-01', 'YYYY-MM-DD')),
   PARTITION t1p3 VALUES LESS THAN (TO_DATE('2008-01-01', 'YYYY-MM-DD')),
   PARTITION t1p4 VALUES LESS THAN (MAXVALUE)
  );

2. HASH Partitioning

Hash partitioning is a partitioning technique where a hash key is used to distribute rows evenly across the different partitions (sub-tables). This is typically used where ranges aren't appropriate, i.e. employee number, productID, etc.

"Hash Partitioning" does not have any logical meaning to the partitions as do the range partitioning. Lets take one example.

CREATE TABLE SAMPLE_ORDERS

(ORDER_NUMBER NUMBER,

ORDER_DATE DATE,

CUST_NUM NUMBER,

TOTAL_PRICE NUMBER,

TOTAL_TAX NUMBER,

TOTAL_SHIPPING NUMBER,

ORDER_ZIP_CODE)

PARTITION BY HASH (ORDER_ZIP_CODE)

(PARTITION P1_ZIP TABLESPACE TS01,

PARTITION P2_ZIP TABLESPACE TS02,

PARTITION P3_ZIP TABLESPACE TS03,

PARTITION P4_ZIP TABLESPACE TS04)

ENABLE ROW MOVEMENT;

The above example creates four hash partitions based on the zip codes from where the orders were placed.

create table emp2 (
   empno number(4), 
   ename varchar2(30), 
   sal   number
) 
partition by hash(empno) (
  partition e1 tablespace emp1, 
  partition e2 tablespace emp2, 
  partition e3 tablespace emp3,
  partition e4 tablespace emp4
);

create table emp2 (
   empno number(4), 
   ename varchar2(30), 
   sal   number
) 
PARTITION BY HASH(empno)
PARTITIONS 3
STORE IN (empts1, empts2, empts3);

3. List Partitioning ( Only with 9i)

Under this type of partitioning the records in a table are partitioned based on the List of values for a table with say communities column as a defining key the partitions can be made based on that say in a table we have communities like ‘Government’ , ‘Asian’ , ‘Employees’ , ‘American’, ‘European’ then a List Partition can be created for individual or a group of communities lets say ‘American-partition’ will have all the records having the community as ‘American’

Lets take one example. In fact, we will modify the same example.

CREATE TABLE SAMPLE_ORDERS

(ORDER_NUMBER NUMBER,

ORDER_DATE DATE,

CUST_NUM NUMBER,

TOTAL_PRICE NUMBER,

TOTAL_TAX NUMBER,

TOTAL_SHIPPING NUMBER,

SHIP_TO_ZIP_CODE,

SHIP_TO_STATE)

PARTITION BY LIST (SHIP_TO_STATE)

(PARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01,

PARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02,

PARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03,

PARTITION SHIP_TO_MASACHUSETTES VALUES (‘MA’) TABLESPACE TS04,

PARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)

ENABLE ROW MOVEMENT;

The above example creates List partition based on the SHIP_TO_STATE each partition allocated to different table spaces.

CREATE TABLE myemp_work (
       emp#   NUMBER PRIMARY KEY,
       ename    VARCHAR2(30),
       salary   NUMBER(8,2),
       deptno   NUMBER)
  PARTITION BY LIST (deptno) (   -- Add list partitioning
       PARTITION p10 VALUES (10), 
       PARTITION p20 VALUES (20), 
       PARTITION p30 VALUES (30,40));

4. Composite Range-Hash Partitioning

This is basically a combination of range and hash partitions. So basically, the first step is that the data is divided using the range partition and then each range partitioned data is further subdivided into a hash partition using hash key values. All sub partitions, together, represent a logical subset of the data.

Lets modify the above example again:

CREATE TABLE SAMPLE_ORDERS

(ORDER_NUMBER NUMBER,

ORDER_DATE DATE,

CUST_NUM NUMBER,

CUST_NAME VARCAHR2,

TOTAL_PRICE NUMBER,

TOTAL_TAX NUMBER,

TOTAL_SHIPPING NUMBER,

SHIP_TO_ZIP_CODE,

SHIP_TO_STATE)

TABLESPACE USERS

PARTITION BY RANGE (ORDER_DATE)

SUBPARTITION BY HASH(CUST_NAME)

SUBPARTITION TEMPLATE(

(SUBPARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01,

SUBPARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02,

SUBPARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03,

SUBPARTITION SHIP_TO_NORTHEAST VALUES (‘MA’, ‘NY’, ‘NJ’) TABLESPACE TS04,

SUBPARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)