[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: the use of custom partitioning with the E-Business Suite is fully supported. In addition, several Apps modules take advantage of partitioning right out of the box.
What Does "Fully Supported" Mean?
If custom 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 custom partitioning. Oracle Development will issue patches or workarounds for all reported issues with standard Apps product code.
What's Custom Partitioning?
Custom partitioning applies when an existing standard Apps product table is not partitioned and the table is redefined as a partitioned table by:
- Using the range, list, hash, or composite partitioning method; or,
- The partition scheme and/or partitioning method of an existing standard product table which is already partitioned (as part of the standard product) is altered from that which is included in the base product.
Examples of Custom Partitioning
For example, 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.
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...
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.
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:
- Partitioning and Purging Best Practices for Oracle E-Business Suite
(PPT, 493K, by Ahmed Alomari, OpenWorld 2005)
- Database Partitioning for Oracle E-Business Suite (Metalink Note 554539.1)
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
- Database Partitioning for Oracle E-Business Suite (Metalink Note 554539.1)
- E-Business Suite High Scalability & Availability, Performance & Benchmarks (OTN)
- Oracle Partitioning (OTN)
- Partitioning and Purging Best Practices for Oracle E-Business Suite
Comments (10)
Excellent entry, as always. Thanks.
Posted by Kevin Krause | September 7, 2006 8:05 AM
Posted on September 7, 2006 08:05
Good post on partitioning, but that said, what is in plan for 12i regards partitioning? Especially around core modules such as GL,AP,AR, etc?
Posted by Chris Balodis | September 7, 2006 10:40 AM
Posted on September 7, 2006 10:40
Thank you for the feedback Kevin. In R12, you can employ partitioning on the financials tables including GL, AP, and AR as per your requirements. As mentioned in the writeup, it is difficult to deliver a standard partitioning scheme, which will work for every customer as the functional implementation differs between customers.
Posted by Ahmed Alomari | September 8, 2006 12:51 AM
Posted on September 8, 2006 00:51
Steve,
This is an excellent article on partitioning in apps.
I have been an active reading of yours and others on this blog site. Given the inspiration, I have started my own "Oracle APPS Technical" blog to help the learners of Oracle Apps.
My blog is...
http://appstechnical.blogspot.com
or
http://oracle.anilpassi.com ( to keep menu's per module)
Is there somehow my blog can be considered for listing on the listing in external list of blogs?
Thanks,
Anil
Posted by Anil Passi | September 8, 2006 4:29 AM
Posted on September 8, 2006 04:29
Anil, thanks for the feedback and welcome to the Apps blogging community.I've added your blog to my blogroll and have notified the OTN Blogging Editors of your site, for consideration for the master Oracle Blogs index.Regards,Steven
Posted by Steven Chan | September 8, 2006 6:56 AM
Posted on September 8, 2006 06:56
dear Ahmed alomari,
i wish to see posts for you soon on steven's blog about preformance tuning new hints and tips.
thanks
fadi hasweh
Posted by Fadi Hasweh | September 10, 2006 9:12 AM
Posted on September 10, 2006 09:12
Jenny,You've been informed correctly: in general, if you wish to create new table partitions or change partitioning keys, you need to licence the database partitioning option separately. E-Business Suite Release 11i licencing does not generally include the database partitioning option. I would recommend checking with your IT Procurement department or Oracle account manager if you'd like confirmation on what's included in your current E-Business Suite contract.Regards,Steven
Posted by Steven Chan | September 12, 2006 10:40 AM
Posted on September 12, 2006 10:40
Steve,
Excellent article as usual.
I want to partition some of GL tables like GL_BALANCES. I was told by Oracle we have to have partition license if we want to use "custom partitioning". That includes create new partitions and change current partition keys. Can you verify this for me please? Also, is the11i license includes partition license?
Thanks,
Jenny
Posted by Jenny Yan | September 12, 2006 11:12 AM
Posted on September 12, 2006 11:12
hi,
is it possible to do partition(range,hash,list) on base tables in advance collection module in oracle apps 11i.
because our reports are generated from the base tables.its performance is low and now i am using global temporary table,
i m planning to do partition on base table.. so suggest me for increase the report performance..
kindly help me out.
thanks,
kanna
Posted by kannapiran | December 18, 2008 9:59 AM
Posted on December 18, 2008 09:59
Hi, Kanna,
I don't have sufficient experience with the Advanced Collection Module to comment on whether partitioning the base table would improve performance for you.
It should be technically feasible to partition any large EBS table, including the one that you're considering here. Your performance will depend on your specific setup. If you've already licenced the Partitioning option, then it can't hurt to give it a try and benchmark your before/after results. As with all customizations, I'd recommend testing carefully before rolling this out in production.
Regards,
Steven
Posted by Steven Chan | December 29, 2008 1:50 PM
Posted on December 29, 2008 13:50