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.

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.