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. The next sections describe those packages. Those packages have to be executed by the owner of the database objects.

Package CLA_DATA_PURGE_PKG

This procedure purges Enrollment Data, Payment Status data, Callout Executions, Draft Provider Pricing Clauses Selections and Fee Schedule Lines.

cla_data_purge_pkg.purge_data
  (p_purge_days in integer);

Package OHI_DATA_PURGE_PKG

The procedure calls both OHI_BATCH_PURGE_PKG and OHI_TASK_PURGE_PKG.

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

Package OHI_BATCH_PURGE_PKG

The procedure purges data from tables related to batch job execution.

ohi_batch_purge_pkg.purge_data
  (p_purge_days in integer);

Package OHI_TASK_PURGE_PKG

This procedure purges data from tables related to task execution.

ohi_task_purge_pkg.purge_data
  (p_purge_days in integer);

Package OHI_DFP_PURGE_PKG

This procedure purges tables related to data import.

purge_dataimport_data (
  p_keep_days in integer, (1)
  p_commit_size in integer := ohi_data_purge_pkg.g_con_commit_size (2)
);
1 p_keep_days

This mandatory parameter specifies the number of days of history that are retained.
Records created within the last p_keep_days are not purged but other records are purged. This means records which are between created_Date < = (sysdate - p_keep_days) are purged.
The value can not be less than 30
2 p_commit_size

This optional parameter specifies the number of records that is purged in a single transaction. Higher commit sizes make the process faster, but needs more undo space in the database.
The default value is 1000.

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.