Create Database Indexes for Dynamic Fields

The database stores the dynamic fields together with the base objects that define them. There is no indexing of the dynamic field columns.

Some queries use or access some dynamic fields frequently. In such cases, it may be beneficial to create a database index for a dynamic field to ensure a more efficient access path to the data. Packaged procedure OHI_TABLE_DEFINITION_PKG.CREATE_DFU_INDEX is available in any Oracle Health Insurance application for that purpose.

Usage Instructions

  1. Access the Usages UI page in the Extensibility section of the Oracle Health Insurance application. Then, search for the Field Usage Name for which requires database index creation.

  2. The Table Name that defines the dynamic field is available in the top block of the page and the Field Usage Name is available in the lower or detail block.

  3. With the Table Name and Field Usage Name values at hand, a DBA can execute the following snippet of PLSQL code:

begin
  ohi_table_definition_pkg.create_dfu_index(’table_name’,'field_usage_name');
end;
/

For example, a dynamic field with Field Usage Name surrogateClaimId on table CLA_CLAIMS in Claims can be:

begin
  ohi_table_definition_pkg.create_dfu_index('CLA_CLAIMS’,'surrogateClaimId');
end;
/
Please note that the values for Table Name and Field Usage Name are case-sensitive.

Validate the output of the PLSQL routine. For example, if the combination of Table Name and Field Usage Name is not valid, the OHI_TABLE_DEFINITION_PKG.CREATE_DFU_INDEX procedure returns:

ORA-20001: No dyn field that matches the criteria

It may be necessary to refresh the database statistics (for the table) before see the benefits of the additional index.

Index Creation Guidelines

Use the packaged procedure OHI_TABLE_DEFINITION_PKG.CREATE_DFU_INDEX to create database indexes for dynamic fields of Free Field or Flex Code type. Searchable multi-value dynamic fields index differently. The packaged procedure determines an index name that adheres to Oracle’s index naming conventions and does not conflict with existing indexes.

Be cautious while adding indexes to tables. Although an index can improve query efficiency, adding indexes also means keeping the database up-to-date in case there are additions or changes to the data. Adding many indexes to a table may harm the overall performance of the system.