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 recommends setting up a scheduled job in the database to optimize the index regularly using the OPTIMIZE_INDEX Procedure (see the Oracle Database documentation for details).

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 having 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.

NOTE

This operation needs to be performed during off-peak hours or preferably during 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 calls 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.

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;