A large volume of records in a table can generate performance issues. Not only insert operations can take longer if the table has too many rows, but also select, update, and delete operations.
Whenever you can keep your table purged you should do it, but sometimes even by deleting old records every day, your table will reach a large number of rows, and it’s important to figure out how to avoid performance issues.
Oracle offers the “partitioning” feature to split a table into n table partitions, in order to limit the records in a physical table.
This feature can provide tremendous benefit to a wide variety of applications by improving performance, manageability, and availability. It is not unusual for a partitioning to improve the performance of certain queries or maintenance operations by order of magnitude. Moreover, partitioning can greatly simplify common administration tasks. You must define a partition key, which is comprised of one or more columns that determine the partition where each row will be stored.
Oracle automatically directs insert, update, and delete operations to the appropriate partition through the use of the partitioning key. You can also partition an index.
One of the disadvantages of Oracle Partitioning is that it is available only in Enterprise edition, and even having Enterprise, you can’t use partitioning for free since it is a licensed feature. However, if you have the Standard edition, or if you can’t afford partition despite having Enterprise, you can improvise a manual partitioning. Of course, it won’t be as useful for every situation as the Oracle partitioning can be, but it might help you in many ways.
For instance, suppose you have a log table where your application inserts 500.000 records a day, and you need to query this data for the last year.
In consequence, your table will have more than 180 million rows. Beyond the record length and the table size, the table will have too many rows, and sooner or later, you will probably face performance issues. If you know that no record will be updated four weeks after it is inserted in the table, you could create another table where you should keep those records older than one month. The key to the solution is to develop a procedure that at least once a day moves those older records from the primary table to the secondary. The procedure should handle a cursor so that you delete and insert a small number of rows with each commit.
This helps you consume less undo and assures a homogeneous behavior of your procedure regardless of the number of rows to move.
You can use this procedure to delete deprecated rows from the secondary table also.
In order to identify the rows to move, of course, you need a date column to tell you when the record was inserted.
If the table doesn’t have one, you can add this column which can be populated with a trigger.
With this configuration, you will finally have the primary table which will store the rows inserted in the last month, and the secondary table with older records.
The procedure will automatically keep the number of records bounded in both tables, especially in the primary table which is the table where the application inserts and updates records.
Finally, what do we do with those reports that need to access new and older rows?
The best solution is to modify them so that they read the primary and secondary tables.
You could also create a view containing both tables and a synonym, to avoid changing the reports, although this is not the most refined solution.
As I said before, this “manual partitioning” won’t replace the Oracle partitioning feature, but it can help you on certain occasions to keep a critical table with fewer rows.
References:“Partitioning Concepts”. 2017. Docs.Oracle.Com. Accessed July 31 2017. https://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm.
