JSON Search Index Maintenance

Oracle Health Insurance applications use JSON search indexes, which require regular maintenance to ensure optimal performance. The following table lists the JSON search indexes used in Oracle Health Insurance for each application component:

Table 1. JSON Search Index Maintenance
Oracle Health Insurance Application JSON Search Index

Oracle Health Insurance Authorizations

OHI_REF_SHEET_LINE_IDX1

Oracle Health Insurance Value-Based Payments

OHI_REF_SHEET_LINE_IDX1

Claims

OHI_REF_SHEET_LINE_IDX1

Policies

OHI_REF_SHEET_LINE_IDX1

Oracle Health Insurance Product Definition

OHI_REF_SHEET_LINE_IDX1

Oracle Text Index Re-Creation

To maintain and re-create the Oracle Text indexes on the OHI_REFERENCE_SHEET_LINES and OIG_EXCHANGES tables, use the following packages:

  • OIG_TEXT_INDEX_MAINTENANCE_PKG

  • OHI_REFERENCE_SHEET_LINES_INDEX_MAINTENANCE_PKG

These packages support optimal performance by:

  1. Dropping the existing Oracle Text indexes (OIG_EXCHANGE_IDX1, and OHI_REF_SHEET_LINE_IDX1).

  2. Recreating the indexes and enabling the auto-optimize option to ensure ongoing maintenance. This includes with daily optimization of fragmented tokens and weekly full optimization.

Perform this operation during off-peak hours or in a scheduled maintenance window.

Example

The index recreation package OHI_REFERENCE_SHEET_LINES_INDEX_MAINTENANCE_PKG must be invoked as an OHI<APP>_OWNER. The below package creates an index with a default synchronization frequency of fifteen minutes for OIG_EXCHANGE_IDX1 and a one-minute synchronization frequency for OHI_REF_SHEET_LINE_IDX1.

Also, this call enables the auto-optimize option to reference sheet line indexes. Fragmented tokens are automatically optimized daily, while complete optimization occurs weekly.

begin
  oig_text_index_maintenance_pkg.create_index(15);
end;
begin
  ohi_reference_sheet_lines_index_maintenance_pkg.create_index(1);
end;

OHI_REFERENCE_SHEET_LINE Maintenance

The DBMS_SCHEDULER job needs to be configured to run weekly according to the customer’s scheduled maintenance window. This scheduler job must accommodate the newly added rows in the OHI_REFERENCE_SHEET_LINE table to follow the attribute clustering linear order defined in this package below.

begin
  ohi_reference_sheet_lines_index_maintenance_pkg.create_index(1);
end;