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.

• Prevent unnecessary data scan
• Perform Minimum I/O
• Enable Data Archiving
• More performance
• More manageability

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

• Big size tables ( For example bigger than 10gb or more )
• If there are historical data in the table,  These types of tables are mostly in Datawarehouse systems, and since the data of the past months, such as the billing table I have explained above, Partitioning Tables reveal an incredible difference for performance at Reporting.
• Tables to be archived. When you partition tables, you can drop old partitions to decrease size of table. Drop old partition is more performance than delete these records.
• Used when a table’s data needs to be kept on different physical disks. For example; You table has 5 TB size, and it is growing day by day, then you can create new partitions on different mount point or different disk group if there is no enough space.

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/