[May 28, 2014 Update: Clarified support implications of custom partitions]

[Nov 13, 2009 Update: Removed outdated reference to alterations to preseeded partitioned objects.]

[May 27, 2008 Update:  Added link to latest official documentation for DB partitioning for Apps 11i]

A frequently-asked question is, “Can I use the database partitioning feature in my E-Business Suite environment?”  The answer to this question is yes:  you may introduce custom partitioning with the E-Business Suite.  In addition, several Apps modules take advantage of partitioning right out of the box.

Database Partitioning Methods:



What’s Custom Partitioning?

Custom partitioning applies when:

  1. A customer partitions an existing standard Apps product table is not partitioned using various methods, including the range, list, hash, or composite partitioning methods; or,
  2. A customer modifies the partition scheme and/or partitioning method of an existing standard product table which is already partitioned by Oracle.
Database Partitioning Methods 2:


Examples of Custom Partitioning

If you choose to partition the table OE_ORDER_LINES_ALL which is currently not partitioned in the standard product, then this is an example of custom partitioning. 

What are the support implications of partitioning?

If
Oracle-provided partitioning causes a particular E-Business Suite flow
or transaction to fail and the failure is caused by standard Apps
product code, it is considered a product defect, as Oracle Applications
is committed to being transparent to its seeded partitions.  Oracle
Development will issue patches or workarounds for all reported issues
with standard Apps product code.

Standard Apps Partitioning “Out of the Box”

Oracle Applications utilizes partitioning in the standard product including the following modules:

  • Advanced Planning and Scheduling
  • Payables (Trial Balances)
  • Projects Resources
  • Workflow
  • Directory Services
  • Daily Business Intelligence
  • HR (Employee Directory)
  • Engineering
  • and other products…
In cases where an Apps table has a natural and logical partition key and the majority of the runtime access path (of the standard product) is based on this natural partition key, Oracle Applications will investigate delivering the table as a partitioned table as part of the standard installation.  

Partitioning Based on Functional Implementation

Many E-Business Suite tables do not have a natural partitioning key which would apply to all customers, simply because the data distribution and access path is highly dependent on each customer’s functional implementation.  However, you are free to partition the tables in a logical manner based on your own requirements.

For example, many of our Oracle Financials customers partition the table GL_BALANCES by either period_name or set_of_books_id, depending on their implementation:
  • Some customers have a large number of books for which partitioning by set_of_books_id makes sense
  • Other customers use a single or a few set of books for which period_name makes sense. 
Since the majority of the GL runtime SQL includes the filters period_name or set_of_books_id, these columns are logical choices for the partition key.

Using Partitioning to Boost Performance

The use of custom partitioning can improve the performance and manageability of your E-Business Suite environment, and many customers are already seeing the numerous benefits of custom partitioning.  Choosing the optimal partitioning method and partition key requires careful and thorough analysis of your system including the access paths of the relevant tables.  For examples and guidelines, refer to the presentation and forma ldocumentation:
If you’re interested in more details about partioning and purging Apps environments, Ahmed Alomari is reprising this popular session at OpenWorld 2006, also.

Testing & Licencing

It is important that you thoroughly test the affected Apps modules after implementing custom partitioning, to ensure that your objectives of employing custom partitioning — including performance and manageability — have been achieved.

Just one final thing:  if you implement custom partitioning, you must license the database partitioning
option.  Your Oracle account representative is always your best source for
licensing details. 

References
Related Articles