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 |
|
Oracle Health Insurance Value-Based Payments |
|
Claims |
|
Policies |
|
Oracle Insurance Gateway |
|
Oracle Health Insurance Product Definition |
|
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;