Site icon IT Tutorial

Partitioning in Oracle Database

Hi,

I will explain you a beautiful technology such as Partitioning that exists in almost all Database management systems In this article.

 

Partitioning in Oracle database means basically “divide and conquer”. Oracle Partitioning technology enhances performance, manageability, and availability of critical applications and enable Data Archiving.

Oracle Partitioning allows tables, indexes, and index-organized tables to divide into many portions or smaller pieces and enabling these database objects to be managed them as One partition. Each portion or partition has its own name, and may have its own storage characteristics optionally.

 

 

Size of data in the Companies is increasing at an incredible rate day by day. It was discussed these data at Megabytes level in the past , but Nowadays Size of data is expressed at Terabyte and Petabytes. As the data grows tremendously, it is emerging in technologies that can be more easily managed these data.

Partitioning technology is one of them, and Oracle released this technology with Oracle 8 version. Actually Almost every database management system (Oracle, MS SQL Server, MySQL, Sybase, Teradata, DB2) has this feature to manage very large data more easily.

For example, There is billing table in your database that includes billions bill records and it is growing day by day. When you query for a bill of any day, database will scan all data and find this record from all records of the table. But If you partition this table to Range partitioning (Monthly or Daily), Oracle will scan only related partition and find record from it. This will enhance your database IO performance Considerably.  Useful features that partitioning enables are as follows.

 

 

 

Well, in which cases partitioning is done or which tables are candidate for partitioning ?

 

Partitioning Key

To partition any table, it should has proper column or columns to select these for partitioning key. Each row will assign to a single partition via this partition key and it will store on this partition. Oracle directs insert, update, and delete operations automatically to appropriate partition via partitioning key.

 

Partitioning feature is enable on Enterprise edition, but if you use it in Enterprise edition,  then you need to have extra Partitioning license except Enterprise license.

 

I will explain Partitioning Types in Oracle Database in the next article. You can access it with the following link.

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

 

 

 

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

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

Exit mobile version