Partitioning Types ( Range , List, Hash, Interval .. ) in Oracle Database

Hi,

I will explain Partitioning Types in Oracle Database in this article.

 

Read previous article if you don’t know What is the Partitioning.

Partitioning in Oracle Database

 

You can use following different types of Partitioning in Oracle database.

• Range partitioning (introduced in Oracle 8)
• List partitioning (introduced in Oracle 9i)
• Hash partitioning (introduced in Oracle 8i)
• Interval partitioning (introduced in Oracle 11g)
• Composite partitioning (introduced in Oracle 8i)
• System partitioning (introduced in Oracle 11g)
• Reference partitioning (introduced in Oracle 11g)

 

 

[the_ad id=”5639″

 

1- Range Partitioning:  In this method, Tables is partitioned according to the specific date and number range. Each partition has an upper and lower bound, and the data is stored in this range on partitions. It is one of the most frequently used partitioning methods.

Range partitioning table can be created like following.

 

SQL> Create table billing(customer_id number,
 name varchar(50),
 lastname varchar2(50),
 bill_date date
 )
PARTITION BY RANGE(bill_date)(
 PARTITION billing201901 VALUES LESS THAN(TO_DATE('01/01/2019','MM/DD/YYYY')),
 PARTITION billing201902 VALUES LESS THAN(TO_DATE('01/02/2019','MM/DD/YYYY')),
 PARTITION billing201903 VALUES LESS THAN(TO_DATE('01/03/2019','MM/DD/YYYY')),
 PARTITION billing201904 VALUES LESS THAN(TO_DATE('01/04/2019','MM/DD/YYYY')),
 PARTITION billing201905 VALUES LESS THAN(TO_DATE('01/05/2019','MM/DD/YYYY')),
 PARTITION billing201906 VALUES LESS THAN(TO_DATE('01/06/2019','MM/DD/YYYY')));

Range partitiong is done for Monthly in this example. You can do it for Daily, Weekly or Yearly

 

2- List Partitioning: In this method, Tables is partitioned according to the listed values. If the table’s relevant columns contain data for a particular list, then the List partitioning method is used in these tables. The best example of this is the sales table where the sales of the company, which has a branch in all the cities, are kept.  List partitioning is one of the most used methods.

List partitioning table can be created like following.

 

SQL> Create table sales(sehir_id number,
 city varchar(50),
 product_id number,
 price number
 )
PARTITION BY LIST(city)(
 PARTITION salesTurkey VALUES('Istanbul','Izmir','Ankara'),
 PARTITION salesUSA VALUES ('Newyork','Washington','Miami'),
 PARTITION salesUrfa VALUES('Şanlıurfa','Diyarbakır','GaziAntep'),
 PARTITION salesSamErzSiv VALUES('Samsun','Erzurum','Sivas'),
 PARTITION salesOthers VALUES(DEFAULT)
 );

 

3- Hash Partitioning: Hash partitioning can be used if the columns of a table are not suitable for Range or List partitioning.

Hash partitioning divides partitions using a hashing algorithm that partitioning key is applied that you identify.

For example, the Identity Number column ( like Social Security number ) in the Citizenship table is not suitable for any of the above methods, then you can use Hash partitioning for this table. In this method, Oracle directs the new data according to an internal algorithm.

 

Hash partitioning table can be created like following.

 

SQL> Create table Citizen(SSN_ID number,
 name varchar(50),
 lastname varchar2(50)
 )
PARTITION BY HASH(SSN_ID)
 partitions 4;

[the_ad id=”5639″

 

 

4- Interval Partitioning: This partitioning method is a new method used with Range partitioning that released in Oracle 11g. In the range partitioning method, new data was insert to the appropriate partition, but it gave the ORA-14400 error if it could not find the proper partition. This problem is solved automatically with Interval Partititoning. Interval Partitioning and Range Partitioning are used as follows.

Interval partitioning table can be created like following. You can modify tablespace name and partitions name according to your need.

SQL> Create table billing(customer_id number,
 name varchar(50),
 lastname varchar2(50),
 bill_date date
 )
PARTITION BY RANGE(bill_date)
 INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
 STORE IN (BILLING_TBS)
 (
 PARTITION Billing201901 VALUES LESS THAN(TO_DATE('01/01/2019','MM/DD/YYYY'))
 );

 

5- Composite Partitioning: This partitioning method is the combination of other partitioning methods.

5.a- Range -List Partitioning: Table will partition with Range and List partition same time like following.

Create table billing(musteri_id number,
 name varchar(50),
 lastname varchar2(50),
 city varchar2(50),
 bill_date date
 )
PARTITION BY RANGE(bill_date)
 SUBPARTITION BY LIST(city)
 SUBPARTITION TEMPLATE(
 SUBPARTITION SalesAnkIstIzm VALUES('Ankara', 'Istanbul','Izmir'),
 SUBPARTITION SalesBurKonAdna VALUES ('Bursa','Konya','Adana'),
 SUBPARTITION SalesUrfDyrGzant VALUES('Şanlıurfa','Diyarbakır','GaziAntep'),
 SUBPARTITION SalesSamErzSiv VALUES('Samsun','Erzurum','Sivas')
 )
 (
 PARTITION Billing201901 VALUES LESS THAN(TO_DATE('01/01/2019','MM/DD/YYYY')),
 PARTITION Billing201902 VALUES LESS THAN(TO_DATE('01/02/2019','MM/DD/YYYY')),
 PARTITION Billing201903 VALUES LESS THAN(TO_DATE('01/03/2019','MM/DD/YYYY')),
 PARTITION Billing201904 VALUES LESS THAN(TO_DATE('01/04/2019','MM/DD/YYYY')),
 PARTITION Billing201905 VALUES LESS THAN(TO_DATE('01/05/2019','MM/DD/YYYY')),
 PARTITION Billing201906 VALUES LESS THAN(TO_DATE('01/06/2019','MM/DD/YYYY')));

5.b- Range-Hash Partitioning: Table will partition with Range and Hash partition same time like following.

SQL> Create table billing(musteri_id number,
 name varchar(50),
 lastname varchar2(50),
 city varchar2(50),
 bill_date date
 )
PARTITION BY RANGE(bill_date)
 SUBPARTITION BY HASH(musteri_id)
 SUBPARTITION TEMPLATE(
 SUBPARTITION PART1 Tablespace DATA01,
 SUBPARTITION PART2 Tablespace DATA02,
 SUBPARTITION PART3 Tablespace DATA03)
 (
 PARTITION billing201301 VALUES LESS THAN(TO_DATE('01/01/2013','MM/DD/YYYY')),
 PARTITION billing201302 VALUES LESS THAN(TO_DATE('01/02/2013','MM/DD/YYYY')),
 PARTITION billing201303 VALUES LESS THAN(TO_DATE('01/03/2013','MM/DD/YYYY')),
 PARTITION billing201304 VALUES LESS THAN(TO_DATE('01/04/2013','MM/DD/YYYY')),
 PARTITION billing201305 VALUES LESS THAN(TO_DATE('01/05/2013','MM/DD/YYYY')),
 PARTITION billing201306 VALUES LESS THAN(TO_DATE('01/06/2013','MM/DD/YYYY')));




[the_ad id="5639"

Composite partitioning can be done by combining Composite partitioning with the following methods, except for the methods described above. I have described the most used above, except for the following.

• Range-range partitioning
• List-range partitioning
• List-hash partitioning
• List-list partitioning
• Interval-range partitioning
• Interval-list partitioning
• Interval-hash partitioning
• Hash-hash partitioning

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/