The DataBee Create/Edit Table-To-Table Rule Form
This form is used to create and edit DataBee Table-To-Table rules which are designed to extract data from a target table based on the rows already extracted for a source table. In the example screen shot above, the form is editing an existing Table-To-Table rule.
Table-To-Table rules are only used in extraction sets and always appear in stage two of the extraction sequence. Table-To-Table rules are the primary method the Set Extractor application uses to ensure referential correctness between tables. In the Table-To-Table shown in the example above, the DTB_INVOICE_LINE table is the source table and the DTB_CUSTOMER table is the target. In spoken words, the above rule is stating:
In other words, this rule enforces database logic which states that it is not possible to have a row in the DTB_INVOICE_LINE table which does not have an equivalent supporting row in the DTB_CUSTOMER table. If rows are extracted for the DTB_INVOICE_LINE table, then the appropriate rows must also be extracted for the DTB_CUSTOMER table. Duplicates are always automatically removed, so if the DTB_CUSTOMER record was previously extracted then it will not be extracted again.
In order to create a new Table-To-Table Rule, the source and target table must be known as well as the columns on which the two tables are to be joined. Many of the tools located on the DataBee Set Designer application Rule Tools tab such as the Who-Loads-What, Chain Finder and Column Finder are designed to identify candidate tables. There are buttons within each of those tools which can automatically launch the Create Table-To-Table Rule form with the chosen source and target tables appropriately configured. Alternately, the Create Table-To-Table Rule form can be launched manually using the button on the New Extraction Rule form. The discussion below describes the manual process of creating a new Table-To-Table rule when starting with an empty form.
The panel on the far left side of the form contains a list of all of the tables in the schema belonging to the Rule Controller which was selected in the Extraction Rules tab of the Set Designer application prior to the launch of the Create Table-To-Table Rule form. This panel is used to select the table which will be the source in table to table relationship. In other words, this table is the one that has the extracted rows.
The panel on the right hand side also displays, by default, the tables in the schema of the active Rule Controller. The table selected in this panel is the target in the table to table relationship. In other words, this table is the one that requires rows to be extracted for it to support the previously extracted rows in the source table. If multiple Rule Controllers are present, then the All Schemas button located below the right hand target table panel can be pressed and the tables known to all Rule Controllers in the extraction set will be displayed. This enables cross schema Table-To-Table rules to be created.
Note that right clicking with the mouse button on any table name will bring up a menu of options which can display the columns for that table, the indexes or the foreign keys with which that table is involved. The Options button below each table panel can be used to include the source row count, extracted row count and plan information as columns in the table display.
Once the source and target tables have been selected, the join condition between the two tables can be configured. This is done by selecting the appropriate column names on each side of the Table Join Columns tab located in the middle center of the form. If there is more than one column involved in the relationship between the two tables, press the Add button to generate another placeholder.
Important Note: The 
icon located in the header of columns in the Join Panel indicates whether the chosen columns are indexed. This icon will be green if the columns are indexed and red if they are not. If the target table is large, it is very important that the columns involved in the target side (the right hand side) of the relationship are indexed. If the columns are not indexed, then Oracle will resort to full table scans to perform the extraction operations for the Table-To-Table rule and it will be very slow. If the target table is not large, then the existence of the index on the target columns in the join relationship is not so important. The entire table will be held in the SGA and full table scans on it will usually be acceptably fast. It does not matter very much if the columns used in the source side (the left hand side) of the join condition are indexed as the DataBee software will adjust its extraction algorythm appropriately. Should it be necessary to double check the indexed states of all join conditions, there is a tool called the Unindexed Rule Target Checker located on the Extraction Rules tab of the Set Designer application which will scan all Table-To-Table rules in the system looking for unindexed rule target tables. If the indexes are not present on the target table join columns, then one can be created with a Command rule as part of stage one of the extraction set and later dropped in stage three after the Table-To-Table rules have completed.
The Options tab located behind the Table Join Columns tab in the center of the Create Table-To-Table Rule form is used to configure performance enhancing hints and also the method of extracting data from self referential tables.
In the above example, the rule specific hint RULE has been entered. This means the select statement on this rule only will use a hint incorporate a hint of /*+ RULE */ when it is run. If the Use Global T2T Hint option is selected, then the current rule will use the same hint (if any) as all of the other Table-To-Table rules. This hint is entered on the Misc. Setup tab of the Set Designer application.
The Connect by Prior and Use Standard Join options are used on Table-To-Table rules which enforce self referential relationships and are disabled on rules for which the source and target tables are different. Normally a CONNECT BY PRIOR algorithm is used to extract rows from self referential relationships - this method is generally very fast. However, due to data loop issues, a CONNECT BY PRIOR extraction mode can sometimes be impossible and it becomes necessary to use a standard join. If an ORA-01436 error is received, it will be necessary to activate the Standard Join option. It might also be use to request the Handling ORA-01436 CONNECT BY PRIOR Errors when Extracting technical note from the DataBee support team.