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:
![](../images/C1_Banking_ILM_Archival_Partitioned_Table.jpg)
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.