Partitioning and Purging Best Practices for Oracle E-Business Suite

For my last post prior to a Thanksgiving holiday, here's a treat for DBAs looking to squeeze some additional performance out of existing database hardware. 

Partitioning Example GL Balances Table:

Ahmed Alomari and Mohsin Sameen put together an excellent OpenWorld 2006 session on optimizing the performance of E-Business Suite environments via partitioning and purging.  Starting with a quick survey of how partitioning has changed from the Oracle 8 database release all the way up to Oracle 10g, Ahmed and Mohsin then dive into:
  • Overview of partitioning benefits and some Apps modules that take advantage of this out-of-the-box, and custom partitioning
  • Technical advantages, such as backup, index rebuild, and Cost Based Optimization (CBO ) implications
  • Integration with Information Lifecycle Management (ILM) for data storage on lower-cost storage devices
  • Techniques and relative merits of range, list, hash, and composite partitioning
  • In-depth discussion of index partitioning methods, including the new Global Hash Partitioned (prefixed) capabilities in 10g
  • Useful data dictionary views for partitioning
  • Before and after comparisons, and actual customer case studies with real statistics
  • Review of purging and archiving programs shipped out-of-the-box with the E-Business Suite
  • Samples of typical customer purging runs
  • Review of the latest Purge Portal released with the E-Business Suite 11.5.10
  • Sneak previews of Release 12 enhancements to the Purge Portal, like this (I can't resist a screenshot):
Release 12 Purge Portal Screenshot:

The above is intended to outline our general product direction.  It is intended for information purposes only, and may not be incorporated into any contract.   It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decision.  The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle. 


Very usefull. I currently looking at develoing a archiving and purging plan for the Oracle E-business suite.

Posted by Dwight on July 22, 2009 at 04:32 AM PDT #

Hi Steven.
We have a doubt.

Every mounth we execute rebuild and move in many tables in our environment (12.0.6 RDBMS The impact is long time to execute these tasks.

My quaetion is:
Is it really necessary to do this?

I read some doc:
Index Rebuild, the Need vs the Implications [ID 989093.1]

And now I think that is not necessary to do this.

Thank you
Tiago Doné

Posted by Tiago Doné on July 08, 2011 at 05:28 AM PDT #


One of our EBS database architects replied with:

"As a general rule, I would agree with AskTom. That doesn't mean there aren't exceptions, and it comes down to the small number of transaction tables. And why are they doing it? Purging data is one thing, but index rebuilds on their own doesn't seem particularly cost effective."


Posted by Steven Chan on July 14, 2011 at 02:59 AM PDT #

not able to open up related links. Are these links still working?

•Partitioning and Purging Best Practices for Oracle E-Business Suite (PPT, 2.6 MB)
•Using Database Partitioning with the E-Business Suite
•Apps Technology OpenWorld 2006 Presentations Now Available


Posted by Nilesh on November 02, 2011 at 02:38 PM PDT #

Hi, Nilesh,

You've come across an article that was published in 2006. Unfortunately, the OpenWorld 2006 archives have been taken offline and are no longer available.

As of today, our latest database partitioning recommendations can be found here:

Whitepaper Update: Database Partitioning for E-Business Suite (Oracle E-Business Suite Technology)


Posted by Steven Chan on November 03, 2011 at 06:12 AM PDT #

Hi Steven,

Sorry for replying to an old thread, but I am struggling to find the information anywhere, and I couldn't see it mentioned in the paper in the link provided.

Basically, I just wanted to know whether a separate partitioning license is required if we are using modules that have partitioned tables, but we haven't partitioned any tables ourselves? Or whether a restricted use license is granted in this case?


Posted by guest on July 29, 2012 at 05:23 PM PDT #

Hi, Neil,

If you use only the seeded partitions supplied with the E-Business Suite, no extra Partitioning Option licence is required. If you create your own partitions or modify the seeded ones, that will require a separate licence.


Posted by Steven Chan on July 30, 2012 at 09:42 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed


« November 2015