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;
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;