As one of the founders of Informatica’s Smart Partitioning capability, I am constantly asked, “Why can’t we just use (insert DB vendor here) tools to accomplish the same thing?” What a great, simple, straightforward question…and what a nuanced answer! Instead of talking about how great our technology is or walk through all the features and functionality, I thought it would be best to answer the actual question, “Why can’t we do this on our own?” In this two part series, we will explore the manual process of implementing Oracle database partitioning and compression in complex OLTP applications.
The argument begins with what I refer to as “the missing link” - the translation layer between powerful yet blunt tools offered within the underlying database and the complex ERP and OLTP systems that use these databases. This layer that allows database tools to understand, and more importantly, respect the business application that is sitting on top of the database is the key functionality needed to fully leverage database tools such as partitioning and compression. Let’s look at the process and challenges of implementing out of the box database partitioning manually.
First, to manually implement database partitioning for complex ERP and OLTP applications, expect multiple cycles of trial and error and complete code rewrite. Without a layer to decouple the database structure from the application logic, there can be severe performance side effects. For example, partitioning alone can have many adverse side effects when working with OLTP applications. Why? The underlying database structure has changed and the application code was not built to understand the new structure. This means a deep analysis of which processes and queries will get worse and which ones will get better is in order. It also means a re-write of code, test, development, and business discussions to ensure that the goals of the organization will continue to be met.
Second, there is a lack of appropriate partitioning keys to introduce business context. Database partitioning on its own can be very powerful; however it is not designed to enable powerful business level Information Lifecycle Management (ILM) functionality. By introducing a layer that translates between database and application logic you open up the power of these robust tools offered by the database vendor. How does that work? It is the difference between taking a technical view of the world (table X needs to be partitioned) and a business context view of the world (I need all Inventory transactions to be partitioned by quarter so I can increase responsiveness to the field and reduce the impact of old transaction data). Choosing large tables to partition manually without taking in account the transactional integrity will not enable true ILM functionality (tiered storage, performance enhancements, eventual archiving, etc). This is the fundamental issue our clients have faced when trying to introduce a manual partitioning effort that often ends up abandoned due to the complexities around development efforts, negative side effects, high overhead and lack of value to the business.
Third, there is no out of the box management through automation. Oracle gives end users tools that are focused on a generic relational database. Informatica’s Smart Partitioning functionality bridges the gap between a generic tool and a solution that is needed to manage complex and high growth ERP applications. Automation opens up the potential to broadly deploy database partitioning across potentially hundreds of tables within complex applications – a term we refer to as Application-Aware Segmentation. The standard transaction data model in Accounts Receivable for Oracle E-Business Suite has over 30 tables in it. Delivering the ability to reduce the overhead associated with database management is an important point in delivering true value to IT and the business.
In summary, the native tools provided by database vendors are powerful base technology and tools that are necessary to effectively manage large applications. However, the ability to implement these tools without experience, context, or automation makes it challenging to deliver real value to the business. To take complete advantage of these innovations, you will need the “missing link” that is provided with a solution like Informatica Application ILM platform to bridge the gap between elegant applications and their complex data structures.
Please also refer to the white paper “Smart Partitioning to Improve Application Performance and Reduce Costs” for more information.