Purging Data from Technical Tables

Introduction

Data volumes in Oracle Health Insurance Components applications are likely to grow fast. This is not only true for operational data, but also for 'technical tables' that are used to support processing. Examples of these technical tables are:

• Tables for task processing

• Tables for temporary enrollment and payment status data in OHI Claims (that is no longer required after Claims are finalized)

• Batch job related tables

Some technical data is cleaned in process, immediately after it is used. An example, again for Oracle Health Insurance Claims, is the data in table CLA$SELECTED_CLAIMS, a table that largely exists for technical efficiency. Data in other tables may be retained a while for problem resolution (traceability) and statistics gathering. An example of the latter category is enrollment data.

To limit the size of the technical tables that are not automatically cleaned, a data cleaning or purging process for the technical tables is available.

Enabling Data Purging for Technical Tables

Purging technical tables is handled entirely in the database. To enable data purging for technical tables, two PL/SQL packaged procedures need to be executed:

cla_data_purge_pkg.purge_data
  (p_purge_days in integer);

Procedure cla_data_purge_pkg.purge_data removes Enrollment and Payment Status data.

ohi_data_purge_pkg.purge_data
  (p_purge_days_task  in integer
  ,p_purge_days_batch in integer
  );

Procedure ohi_data_purge_pkg.purge_data removes Tasks data and Batch Process data.

The input parameters define the retention period for the data expressed in days. The minimum retention period is 30 days. A value smaller than 30 for any of the input parameters results in an error.

The procedures must be executed as the owner of the database objects, e.g. ohi_claims_owner.

Using DBMS_SCHEDULER for frequently purging data

Oracle recommends frequent and automated purging of technical data, for example through the use of a DBMS_SCHEDULER job. As an example, the following SQL script creates a job that executes the data purging routines every day at 04:00 and uses a retention period of 180 days:

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'purge_technical_data_daily',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN
                          cla_data_purge_pkg.purge_data(p_purge_days => 180);
                          ohi_data_purge_pkg.purge_data(p_purge_days_task => 180,
                                                        p_purge_days_batch => 180
                                                       );
                        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Purge OHI Claims Technical every day at 04:00');
END;

To create this script, the CREATE JOB privilege is required.

Note: that this is just an example. The retention times may need to comply with internal rules and regulations and the DBMS_SCHEDULER package allows various other constructs to be used, like creating programs separately from the schedule that is used for executing these.

Alternatively, Oracle Enterprise Manager can be used to create and manage database jobs.

The benefits of cleaning data on a regular (e.g. daily) basis ensures that the purge processes do not take a long time to complete. Note that the first-time execution may take a long time to complete.