Create Database Indexes for Dynamic Fields

Dynamic fields are stored in the database together with the base objects on which these are defined. Dynamic field columns are not indexed. Some dynamic fields may be used in queries and / or will be accessed 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

First, access the “Usages” UI page in the Extensibility section of the Oracle Health Insurance application and search for the Field Usage Name for which the database index needs to be created. The Table Name on which the dynamic field is defined is available in the top block of the page and the Field Usage Name is available in the lower or detail block.

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, in Oracle Health Insurance Claims, for a dynamic field with Field Usage Name ‘surrogateClaimId’ on table ‘CLA_CLAIMS’ this would be:

begin
  ohi_table_definition_pkg.create_dfu_index('CLA_CLAIMS’,'surrogateClaimId');
end;
/

Note:Please note that the values for Table Name and Field Usage Name are case sensitive

Validate the output of the PLSQL routine, e.g. 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 you will see the benefits of the additional index.

Index Creation Guidelines

Packaged procedure OHI_TABLE_DEFINITION_PKG.CREATE_DFU_INDEX can be used to create database indexes for dynamic fields of type Free Field or type Flex Code. Searchable multi-value dynamic fields will be indexed in a different way. The packaged procedure determines an index name that adheres to Oracle’s index naming conventions and that does not conflict with existing indexes.

Adding indexes to tables should be done with caution. Although an index can improve query efficiency, adding indexes also means that these must be kept up to date by the database in case data is added or changed. Adding many indexes to a table could have an adverse effect on the overall performance of the system.