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