Table Partitioning in Oracle

Partitioning is nothing but the concept of "Divide & Rule"   or   "Categorize them to handle easily".

In order to handle data in huge tables in a easy and quick way,
Oracle has an effective concept " Partitioning Tables"  and  " Partitioned Indexes"

Lets start knowing about what it is all about, how it works and benefits out of it.


Say.. "Hai" to Partitioning  


Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition.

Each Partition has its own name. To retrieve the data, it can be retrieved by calling a table or by calling its partition unit. 


Pictorial view of Partitioned Table and Non Partitioned Table




When actually we can partition a table ?

Huge tables which has data more than 2 GB size can go for partitioning.

Table which has years of data, but mostly we deal with recent months data and old data in a table are just to read.

Table which has data can be categorized into types like Department wise, Country wise, etc.,  So that we can deal with those particular category data alone by naming them as partition.


Types of Partitioning :-

The three types of  partitioning are List, Range and Hash.





Below are the lines to support in explaining the above pictorial view of different partitioning types. 


List Partitioning :-

List partitioning segregates data by its data  which falls into different categories. 

For example:-
The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.

A query to retrieve data only from Florida can deal with "East Sales Region" Partition alone.

Example code to create list partition of table 

CREATE TABLE q1_sales_by_region
      (deptno number, 
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));

Range Partitioning :-

Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition.  

Example Dates between 2000 to 2012, 2012 to 2014 , 2012 to 2016 and greater than 2016.  

Example: Revenue less than 1 million, less than 2 million, less than 4 million and Default ( other values will fall in default partition always )


Example Code :-

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
    TABLESPACE tsa
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
    TABLESPACE tsb
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
    TABLESPACE tsc
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
    TABLESPACE tsd
 );

Hash Partitioning:-

No criteria involved. Just spread the data among partitions in equal size. 

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. 

The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.

Example code :- 

CREATE TABLE scubagear
     (id NUMBER,
      name VARCHAR2 (60))
   PARTITION BY HASH (id)
   PARTITIONS 4 
   STORE IN (gear1, gear2, gear3, gear4);

Composite Partitioning:-

We can have composite partitioning with the combination of  different partitions at different levels. 

Region wise(List) .. within region, year wise. (Range). within year  equal distribution ( hash ) partition..







Benefits of Partitioning :- 

There are three key benefits and they are 

Performance :- By limiting the amount of data to be examined or operated on, and by providing data distribution for parallel execution, partitioning provides a number of performance benefits. It is like taking data from 1 million records rather than 1 billion records is always quicker

Manageability:- With partitioning, maintenance operations can be focused on particular portions of tables. For example, a database administrator could back up a single partition of a table, rather than backing up the entire table

Availability :- if one partition of a partitioned table is unavailable, then all of the other partitions of the table remain online and available. The application can continue to execute queries and transactions against the available partitions for the table.



Hope this helps.. if yes, then please share this post and comment your feedback.