H.1 SQL Hints

Oracle Insurance Allocation Manager for Enterprise Profitability allows users to exploit SQL Hints to tune the performance of allocation rules. SQL Hints provide a mechanism to instruct the optimizer to choose a desired query execution plan based on specified hint criteria. The Oracle Insurance Allocation Manager for Enterprise Profitability allocation engine reads user-defined hints that are stored in the FSI_SQL_HINTS_OPTIONS table.

Profitability Management supports the following types of SQL hint:

• DML_HINT
• PERCENT_DIST_HINT
• TREE_FILTER_HINT
• FILTER_ON_HINT

Note the following:

• Hints must be defined by users; there are no default hints.
• There is currently no user interface for the definition of hints. Your DBA can assist you in defining, testing, and optimizing SQL Hints. SQL Hints needs to be manually inserted into the FSI_SQL_HINTS_OPTIONS table.
• The FSI_SQL_HINTS_OPTIONS table is indexed by the following:

  The system identifier (SYS_ID_NUM) of the rule to which the hint applies.

  By the allocation engine's PROCESS_ENGINE_CD (zero).

  Currently, only the Oracle Insurance Allocation Manager for Enterprise Profitability engine supports SQL Hints; additional analytical application engines may support SQL Hints in future releases.

• You may determine an allocation's SYS_ID_NUM by performing a mouse-over on the allocation rule's name in the Allocation Specification summary screen.
• Hints are limited to 250 characters.
• DML_HINT and FILTER_ON_HINT apply to all types of allocations.
• TREE_FILTER_HINT applies only to allocation rules that include a Hierarchy Filter.
• PERCENT_DIST_HINT applies only to Dynamic Driver allocations.