JSON Search Index Maintenance

Oracle Health Insurance applications make use of JSON Search Indexes. These indices require regular maintenance. The following table lists the JSON Search Indexes as per the Oracle Health Insurance application:

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 Insurance Gateway	OIG_EXCHANGE_IDX1
Oracle Health Insurance Product Definition	OHI_REF_SHEET_LINE_IDX1

Oracle Text Index Re-Creation

The internal index maintenance structure has undergone changes from Oracle 18C to Oracle 19C. This is affecting query performance. Oracle Text Index re-indexing is required on OHI_REFERENCE_SHEET_LINES and OIG_EXCHANGES tables of Oracle Health Insurance applications that have JSON Index syntax in the Oracle 12C format.

For existing users who have transactional data in these tables, a database package OIG_TEXT_INDEX_MAINTENANCE_PKG and OHI_REFERENCE_SHEET_LINES_INDEX_MAINTENANCE_PKG is provided. The packages drop the existing OIG_EXCHANGE_IDX1 and OHI_REF_SHEET_LINE_IDX1 Oracle Text Indices and creates the same in Oracle 19c format. This package also enables the auto-optimize option to reference sheet line indexes. It prevents the use of a separate scheduler job to optimize the index. Automatic optimization of fragmented tokens occurs every day and a full optimization occurs weekly.

This operation needs to be performed during off-peak hours or when there is a maintenance window.

Example

The index recreation package OHI_REFERENCE_SHEET_LINES_INDEX_MAINTENANCE_PKG must be called as an OHI_<APP>_OWNER. The below package create the index with a default fifteen-minute sync frequency for OIG_EXCHANGE_IDX1 and one-minute sync frequency for OHI_REF_SHEET_LINE_IDX1. The call enables the auto-optimize option to reference sheet line indexes. This prevents the use of a separate scheduler job for optimizing the index. Automatic optimization of fragmented tokens occurs every day and a full 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

Users can schedule a DBMS_SCHEDULER job to make the below call for the OHI_REFERENCE_SHEET_LINE table as per their maintenance window for enabling attribute clustering.

Begin
ohi_reference_sheet_lines_index_maintenance_pkg.create_index(1);
end;