Performance Optimization Setting for RRF Module

The Process engine and Rule engine has been enhanced to take advantage of ORACLE's fast insertion into table and partition swap mechanism.

Based on the new enhancement, Rule and Process Execution supports two additional execution modes (apart from the Merge execution mode where Oracle MERFGE query is used). They are:

Select (select insert query is used) - In this execution mode, all records are moved to a temporary table with the updated records and then moved back to the original table. This improves the performance since INSERT is faster than MERGE. In this execution mode, the actual updated record count cannot be known since all records are moved back from the temporary table to the original.
Partition (partition swap query is used) - This is somewhat similar to Select execution mode. This also moves all the records to a temporary table with the updated records. However, while moving back, the whole temporary table will be moved as a partition of the original table using the Oracle Partition Swap mechanism. In this mode the record count cannot be known as you are swapping the partitions.

The execution mode can be set in the QRY_OPT_EXEC_MODE parameter of the CONFIGURATION table as well as V_EXECUTION_MODE parameter in the AAI_OBJ_QUERY_OPTIMIZATION table. The parameter value can be set as SELECT, MERGE or PARTITION. The optimization table is newly introduced. Both the tables reside in the Configuration Schema.

The Configuration table setting is for global level (applies to all rules and processes execution) and the Optimization setting is for rule/process level.

Note:

The Optimization table setting has preference over the Configuration table setting. That is, if


                                        V_EXECUTION_MODE

in AAI_OBJ_QUERY_OPTIMIZATION table is set, that will be considered. If it is not set, then the execution mode will be as per the value given in the


                                        QRY_OPT_EXEC_MODE

parameter in the Configuration table. By default, its value will be MERGE.

The columns and the values to be given in the AAI_OBJ_QUERY_OPTIMIZATION table are indicated as follows:

Column Name	Description	Value
V_OBJ_CODE	Rule/Process/Run Code	Rule(PR2_RULE_B.V_RULE_NAME) Process(PR2_PROCESS_B.V_PROCESS_NAME) Run (PR2_RUN_B.V_RUN_NAME)
V_INFODOM_CODE	Infodom Code	Infodom
V_OBJ_TYPE	Rule/Process/Run Type	Rule(RL) Process(PT) Run (RN)
V_EXECUTION_MODE	Type of query used while executing.	MERGE- Merge statement will be used SELECT- Select Insert will be used PARTITION- Partition swap will be used
F_USE_PARTITION	If partition is used as a filter	Y/N
F_USE_ROWID	If ROWID is used other than primary key in MERGE. This is used only for MERGE query execution.	Y/N
V_MERGE_HINT	Used for MERGE or INSERT hint.
V_SELECT_HINT	Used for SELECT hint
V_PRE_SCRIPT	Used for alter statements executed before rule execution
V_POST_SCRIPT	Used for alter statements executed after rule execution.