Retrofitting Partitioning into an Existing Application: 1. Introduction

This post is the first in a series about the partitioning of database objects.

• Introduction
• What kind of partitioning can you do?
• Scripting and Archiving
• Examples from Real Life
1. General Ledger reporting: Typical example of partitioning for data warehouse-style queries
2. Payroll: Avoiding the need for read-consistency in a typical transaction processing system.
3. Workflow: Separate active and inactive rows, and partial indexing.
• Conclusion

Introduction

Over the years I have seen and read many presentations and articles on the subject of partitioning database tables and indexes. Most explain how partitioning works. Many explain the options for the developer and discuss how to design your application to be able to make effective use of partitioning.

However, my experience comes from working with packaged applications or applications that are already in production where all the design decisions have been taken. Often, I am faced with performance or scalability problems, and sometimes I have to consider whether partitioning is an effective option.

In this series of posts, I am going to look at the thought process behind deciding whether you can retrofit partitioning into an existing application. The task often falls to the DBA but also requires input from application developers and administrators.  I realise that I am going to say many of the same things that you can find in other articles, but I will be approaching them from a slightly different point of view.

The motivation is always the same: improved performance with, if possible, reduced overheads.

• The fastest way to do anything is not to do it all.  
• In general, Oracle inserts data into the first available space.  Any piece of data could be anywhere in a table.  However, partitioning creates a relationship between the physical location of a piece of data and the logical value of that data. This dictates into which partition data is inserted.
• Thus, the optimizer can discard partitions from a query, without the overhead of scanning them, where it can determine that no data of interest resides.  This is called partition elimination or partition pruning.  
• If you aren't achieving elimination, then there is probably no benefit to the partitioning.  In fact, it might increase your overheads as you probe every partition.

The following diagram was taken from the Oracle documentation.  The table has been partitioned into monthly partitions.  If I am looking for March data, then I don't need to inspect the January and February partitions.  However, if the table had not been partitioned I would have to scan the whole segment.  If the query was using a locally partitioned index, then I would only probe the partition for March.

Whose job is it?

• Designing partitioning into an application during development is a job for the application architect/developers.
• Retrofitting partitioning into an existing (or a packaged 3rd party) application usually falls to the DBA.

In my opinion, in order to be successful, the developers and the DBAs need to work together.  Bear in mind also:

• Partitioning is a licenced option available on Enterprise Edition only.  That means you have to pay for it.  So, if you are not getting an improvement in performance (or a reduction in resource consumption) then you have to question whether it is worth it.
• Check your application vendor's support policy.  Sometimes vendors do not support customer partitioning at all (e.g. Oracle's own E-Business Suite).  Or, they may permit it, but it remains the customer's responsibility to support it (e.g. PeopleSoft - yes, also owned by Oracle).
• There is also an ongoing cost of ownership.  You have to look after your partitioning.  If you partition by date or something that changes over time (like employee ID), then periodically you will add new partitions, and then also possibly compress and/or remove old partitions.  If you rebuild or change a table or index, you have to remember that it is partitioned when you create the DDL.