Partitioning in Oracle E-Business Suite
                                                 Last update (2011-04-06 13:10:57)
                                                                                                                   Date added (2010-09-10 10:26:14)

Summary
Partitioning can bring significant benefits in the areas of performance and manageability. Databases has the trend to constantly grow. When the yearly growth rate starts creating performance and space manageability problems then you have to find permanent solutions. One is Partitioning. Creating partitions can be beneficial for future Archiving

Partitioning is supported with Oracle E-Business Suite, which has some tables partitioned by default. These include major transaction tables used in the Release 12 Centralized Accounting Engine (XLA). These particular tables are partitioned by APPLICATION_ID, since different sub ledgers such as Payables, Receivables and Fixes Assets are all clients of the XLA system. Modifying pre-partitioned indexes and tables is not supported.

Which tables are candidates for partitioning
Depending of the modules where your data keeps growing you can partition the underlying related tables. For example.
General Ledger
GL_JE_LINES
GL_BALANCES
GL_DAILY_BALANCES

Account Payables 
AP_INVOICE_DISTRIBUTIONS_ALL
AP_INVOICES_ALL
AP_CHECKS_ALL

Account Receivables
AR_CASH_RECEIPT_HISTORY_ALL
AR_CASH_RECEIPTS_ALL
AR_PAYMENT_SCHEDULES_ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
RA_CUSTOMER_TRX_ALL
Tip:Before start partitioning you must calculate the size of the candidate tables. Small tables with performance problem must take care with other solutions, not partitioning.

Which column to use for partitioning
This is the most important and critical decision. Choosing the right column it will help you to succeed in terms of performance and feature manageability. If not the partitioning is almost useless. To partition with efficiency in E-Business Suite you need previous experience and lot of testing. One proven solution for some systems is the following:

partition GL_JE_LINES based on period_name (monthly)
partition AP_CHECKS_ALL based on check_date (yearly)
partition AP_INVOICE_DISTRIBUTIONS_ALL based on accounting_date (monthly)
partition AP_INVOICES_ALL based on invoice_date (monthly)
partition AR_CASH_RECEIPT_HISTORY_ALL based on gl_date (monthly)
partition AR_CASH_RECEIPTS_ALL based on receipt_date (monthly)
partition AR_PAYMENT_SCHEDULES_ALL based on gl_date (monthly)
partition RA_CUST_TRX_LINE_GL_DIST_ALL based on gl_date (monthly)
partition RA_CUSTOMER_TRX_ALL based on trx_date (monthly)

Tip:Do not try to partition tables for this modules based on ID columns. For example do not partition GL_JE_LINES based on JE_HEADER_ID. Oracle Applications basically work with periods not IDs. Partitioning based on monthly and yearly periods it will help you to Archive in the future!

Steps for partitioning one table
1. Shutdown the Apps tier, in order to copy data from the original table to a new one partitioned, 
    the users must not use the original table.
2. Create the new partitioned table with the same column structure as the original and with the partitions.
3. Insert data from the original table to the partitioned. Use parallel DML.
4. Rename the indexes of the original table
5. Create indexes to the partition table with the same columns as the original indexes.
6. Save the source code of the original table triggers
7. Rename the triggers of the original table to OLD
8. Do the table renaming. Rename original table to OLD and the partitioned table to original.
9. Drop the synonyms for the OLD table and recreate to point to the new partitioned
10. Grant the appropriate privileges to new partitioned table.
11. Create the triggers to the partitioned table
12. Calculate new statistics for the table, indexes and partitions.
13. Run Adadmin to Compile the Apps Schema, recreating the tables makes many invalid objects.
14. Start the Apps tier
Here is a quick example with the steps to partition table AP_INVOICE_DISTRIBUTIONS_ALL or
partition table GL_JE_LINES
Reviews
Filters
Search