Purging Data from Technical Tables

Introduction

Data volumes in Oracle Health Insurance applications typically grow when more and more data is processed. This is not only true for operational data but also for technical tables that support processing.

Examples of these technical tables are:

  • Tables for task processing

  • Tables for temporary enrollment and payment status data that is no longer needed

  • Batch job-related tables

Not all technical tables need purging: some clean-up automatically. Other tables may keep data during problem resolution (traceability) and statistics gathering however. An example of the latter category is enrollment data.

For this category, a purging process for the technical tables is available. This process must be triggered, it does not run automatically.

Enabling Data Purging for Technical Tables

The purging is implemented in the database as a set of Pl/SQL packages. This gives the best performance. The next sections describe those packages. Those packages have to be executed by the owner of the database objects.

Package CLA_DATA_PURGE_PKG

cla_data_purge_pkg.purge_data
  (p_purge_days in integer);

The procedure cla_data_purge_pkg.purge_data removes Enrollment and Payment Status data.

Package OHI_BATCH_PURGE_PKG

This procedure purges tables related to batch job execution.

ohi_batch_purge_pkg.purge_data
  (p_purge_days in integer);

Package OHI_DATA_PURGE_PKG

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

The procedure ohi_data_purge_pkg.purge_data calls both OHI_BATCH_PURGE_PKG and OHI_TASK_PURGE_PKG.

Package OHI_TASK_PURGE_PKG

This procedure purges tables related to task execution.

ohi_task_purge_pkg.purge_data
  (p_purge_days in integer);

Using DBMS_SCHEDULER for Frequently Purging Data

Oracle recommends frequent and automatic 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;

A CREATE JOB privilege is essential for creating this script.

This is just an example. The retention times may need to comply with internal rules and regulations. The DBMS_SCHEDULER package allows use of various other constructs, like creating programs separately from the schedule that executes these.

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

The benefits of cleaning data on a regular (for example, daily) basis ensure 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.