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);
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.