Executing the Sample Table Partitioning Scripts

You need to partition the master and child tables in such a way that referential integrity is not hampered. Partitioning is mandatory for ILM implementation. This helps to segregate the data into multiple table partitions. There are many partitioning strategies which can be used to partition tables. However, we recommend you to partition the TFM and Billing related tables using the following partition type and keys:

Table Name Partition Type Partition Key Sub-Partition Key
CI_​TXN_​DETAIL Range CURR_​SYS_​PRCS_​DT -
CI_​ROLLBACK_​TXN_​DETAIL Range CURR_​SYS_​PRCS_​DT -
CI_​TXN_​DETAIL_​EXCP Range CURR_​SYS_​PRCS_​DT -
CI_​TXN_​DTL_​PRITM Range CURR_​SYS_​PRCS_​DT -
CI_​TXN_​CALC Range ILM_​DT -
CI_​TXN_​CALC_​LN Range ILM_​DT -
CI_​TXN_​CALC_​LN_​CHAR Range ILM_​DT -
CI_​TXN_​SQ Range ILM_​DT -
CI_​BILL Range ILM_​DT BILL_​ID
CI_​BILL_​CHAR Reference ILM_​DT BILL_​ID
CI_​BILL_​EXCP Reference ILM_​DT BILL_​ID
CI_​BILL_​MSG_​PRM Reference ILM_​DT BILL_​ID
CI_​BILL_​ROUTING Reference ILM_​DT BILL_​ID
CI_​BILL_​SA Reference ILM_​DT BILL_​ID
CI_​BILL_​ACH Reference ILM_​DT BILL_​ID
CI_​BILL_​MSGS Reference ILM_​DT BILL_​ID
CI_​BILL_​LOG Reference ILM_​DT BILL_​ID
CI_​BSEG Range ILM_​DT BSEG_​ID
CI_​BSEG_​CALC Reference ILM_​DT BSEG_​ID
CI_​BSEG_​CALC_​LN Reference ILM_​DT BSEG_​ID
CI_​BSEG_​CL_​CHAR Reference ILM_​DT BSEG_​ID
CI_​BSEG_​EXCP Reference ILM_​DT BSEG_​ID
CI_​BSEG_​EXT Reference ILM_​DT BSEG_​ID
CI_​BSEG_​ITEM Reference ILM_​DT BSEG_​ID
CI_​BSEG_​MSG Reference ILM_​DT BSEG_​ID
CI_​BSEG_​READ Reference ILM_​DT BSEG_​ID
CI_​BSEG_​SQ Reference ILM_​DT BSEG_​ID
CI_​BILL_​CHG Range ILM_​DT BILLABLE_​CHG_​ID
CI_​BCHG_​READ Reference ILM_​DT BILLABLE_​CHG_​ID
CI_​BCHG_​SQ Reference ILM_​DT BILLABLE_​CHG_​ID
CI_​BILL_​CHG_​CHAR Reference ILM_​DT BILLABLE_​CHG_​ID
CI_​B_​CHG_​LINE Reference ILM_​DT BILLABLE_​CHG_​ID
CI_​B_​LN_​CHAR Reference ILM_​DT BILLABLE_​CHG_​ID
CI_​ADJ Range ILM_​DT ADJ_​ID
CI_​ADJ_​APREQ Reference ILM_​DT ADJ_​ID
CI_​ADJ_​CALC_​LN Reference ILM_​DTADJ_​ID ILM_​DTADJ_​ID
CI_​ADJ_​CHAR Reference ILM_​DT ADJ_​ID
CI_​ADJ_​CL_​CHAR Reference ILM_​DT ADJ_​ID

An out of box table partitioning scripts for ILM implementation are provided by Oracle Revenue Management and Billing. For more information about release-specific partitioning script, refer to Oracle Revenue Management and Billing Information Lifecycle Management (ILM) Implementation Guide.

Once you execute the sample table partitioning scripts, the tables would be partitioned as shown in the following figure:



The range interval of each partition for a table could vary as per the client's volume. For example, the Transaction Feed Management (TFM) related tables can have one partition per day, whereas the Billing related tables can have one partition per month. This range interval is used for partitioning tables in the sample table partitioning scripts.

If you want to change the range interval (i.e. to daily, monthly or quarterly), you need to accordingly modify the sample table partitioning scripts. You need to ensure that the range interval is same for all tables highlighted in the same color (as shown in the above figure). For example, you must use the same range interval for the CI_​TXN_​DETAIL, CI_​ROLLBACK_​TXN_​DETAIL, CI_​TXN_​DETAIL_​EXCP, and CI_​TXN_​DTL_​PRITM tables. However, this range interval may vary from the range interval which is defined for CI_​TXN_​CALC, CI_​TXN_​CALC_​LN, CI_​TXN_​CALC_​LN_​CHAR, and CI_​TXN_​SQ tables or for the CI_​BILL, CI_​BSEG, CI_​BILL_​CHG, and CI_​ADJ tables.

The partitioning key for any table where the Owner flag is set to Base should not be changed in any case as it will violate the support from ORMB team.