Purging Data from Technical Tables
Introduction
Data volumes in Oracle Health Insurance applications are likely to grow fast. 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 in Claims (that completed Claims no longer require)
-
Batch job-related tables
Some technical data cleans in the process immediately after the use of the technical table. An
example, again for Claims, is the data in table CLA$SELECTED_CLAIMS
. This table
that largely exists for technical efficiency. Other tables may keep data during problem resolution (traceability) and statistics gathering.
An example of the latter category is enrollment data.
To limit the size of the technical tables that do not clean automatically, a data cleaning or purging process for the technical tables is available.
Enabling Data Purging for Technical Tables
Database handles purging technical tables entirely. Execute two PL/SQL packaged procedures to enable data purging for technical tables:
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 of less than 30 days for any of the input parameters results in an error.
The owner of the database objects, for example,
ohi_claims_owner
must execute the procedures.
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.