Once the stage one processing operations have completed, the Table-To-Table rules in stage two of the extraction process will begin to execute. Table-To-Table rules are used by the Set Extractor application to extract the dependent rows from a target table for each row already extracted from a source table.
The Unindexed Rule Target Checker tool detects and displays the Table-To-Table rules on which the join columns of the target table used in the rule are not indexed. This tool is useful because Table-To-Table rules which reference unindexed target table columns can be very slow if the target table has many rows. The example below will discuss what happens during the execution of a Table-To-Table rule and provide and insight as to why the index on the join columns of the target table is so important.
Fundamentally, the mechanics of the extraction process are straightforward although the actual implementation is considerably more complex. Lets consider what happens during the execution of a Table-To-Table rule. If a row has previously been extracted for a table, then that ROWID for that row will be known. If the table with the extracted row is also used as the source in a Table-To-Table rule, then that rule must activate to extract referentially related rows from the target table. A statement similar to the following pseudo-code will be used to extract the referentially related rows from the target table.
SELECT TGT.ROWID from <TargetTable> TGT, <SourceTable> SRC where SRC.<JoinColumn> = TGT.<JoinColumn> and SRC.ROWID="ExtractedSourceTableRowid";
Notice how the statement works. The row in the source table is identified by ROWID - this is the fastest possible access. However the rows which are returned from the target table must be looked up using the join condition. If the join columns in the target table are not indexed, then the Oracle database must use full table scans to find the matching rows. If the target table is large then the extraction operation will be very slow.
The New Table-To-Table rule form provides a visual indicator of the index state of the specified join columns. The example screen shot below shows part of a Table-To-Table rule.
A View of the Join Condition Tab on A Table-To-Table Rule
In the above example, the green icon on the source column indicates that that column is indexed in the source table. The red icon on target join condition indicates that that column is not indexed in the target table and that the rule will be slow to execute if the target table is large. Note that it does not matter very much if the source join columns in the rule are indexed - the target join columns are the important ones. For large tables, the icon on the right hand side should always be green.
The Unindexed Rule Target Checker Tool Form
The Unindexed Rule Target Checker Tool is quite easy to use and the button to launch it can be found on the Rule Tools tab of the Set Designer application. Shortly after the tool starts, the panel at the left will automatically fill with a list of each Table-To-Table rule involved in the extraction set which has unindexed target join columns. In the example above, only one rule (tt-0021) references unindexed columns in the target table.
After the candidate rules have been displayed, the rule can be double clicked with the mouse to display the rule configuration. The Info+Code button beside each rule can also be clicked to provide more information. An example screen shot is shown below.
More information on Unindexed Rule Targets
The provided information can then be used to make decisions regarding the suitability of the rules. If required, the rules can be dropped, or Command Rules can be used to create temporary indexes on the tables.