7.6.1.1.3 Maintaining Partitions

Partition maintenance procedures remove old data from the database so that the database does not continue to grow until space is insufficient. Daily, weekly, or monthly maintenance is necessary for tables that have daily, weekly, and monthly partitions, respectively.

Partition maintenance performs the following tasks:

  1. Copies information related to open orders from the oldest partitions to temp tables (EXECUTION, ORDR, ORDR_EVENT, ORDR_STATE_CHANGE TRADE and TRADE_EXECUTION_EVENT)
  2. Drops the oldest partitions for all partition types.
  3. Inserts the saved data into what is now the oldest partition (applicable to tables with open orders).
  4. Creates new partitions.
  5. Recompiles the views that scenarios use.

Managing Daily Partitioning Alternative

The Data Retention Manager also enables you to build five daily partitions on a weekly basis.

To build partitions, follow these steps:
  1. 1. Execute the run_drm_utility.sh shell script
  2. Set the drm_weekly_proc_flg parameter to Y. For more information, refer to Data Retention Manager Processing Parameters.
This procedure eliminates the must perform frequent index maintenance; Oracle recommends doing this for large market tables. This approach builds the daily partitions for the next week. When creating the five daily partitions on a weekly basis, the Data Retention Manager should be executed prior to the end of the current week, to create partitions for the next week.

Note:

You must set the WEEKLY_ADD_FL parameter in the KDD_DR_MAINT_OPRTN table to Y so that the procedure works correctly. For more information about this parameter, refer to BUSINESS.KDD_DR_MAINT_OPRTN Table.

Partition Structures

The structures of business data partitions and market data partitions differ in the following ways:
  • Business data partitions are pre-defined so that weekdays (Monday through Friday) are business days, and Saturday and Sunday are weekly off-days. Business data tables use all partitioning types.
  • You can use the Calendar Manager Utility to configure a business calendar as desired. For more information about this utility, refer to Managing Calendar Manager Utility.
  • Market data partitions hold a single day of data. The partitions use the PYYYYMMDD convention, where YYYYMMDD is the date of the partition.

Recommended Partition Maintenance

You should run partition maintenance as appropriate for your solution set. Oracle recommends that you run partition maintenance for AML on a daily basis (after setting the business date through the Calendar Manager Utility, and prior to the daily execution of batch processing), and Trading Compliance at least once a week.

Oracle recommends that you use the P (Partition) option when running the Data Retention Manager, as it drops older partitions and adds appropriate partitions in a single run of the utility. When performing monthly maintenance, you can add or drop a partition independently, as the following procedures describe.

Managing Alternative Monthly Partition

As part of an alternative method of monthly partition maintenance, you can either add or drop a monthly database partition. Refer to Data Retention Manager Processing Parameters when following these steps.
  • Adding a Monthly Database Partition:To add a monthly partition, run the utility’s shell script: 
    run_drm_utility.sh AM M BUSINESS <object> N
    where AM is the drm_operation parameter that implies adding a monthly partition.
  • Dropping a Monthly Database Partition: To drop a monthly partition, run the utility’s shell script:
     run_drm_utility.sh DM M BUSINESS <object> N
    where, DM is the drm_operation parameter that implies dropping a partition.