Specifying Relationships When Creating a Join Index

For non-key joins, the order in which you specify tables when you create the join index determines the hierarchy of the join relationship between the tables.

The CREATE JOIN INDEX statement supports two ways to specify the join hierarchy:

List each table starting with the lowest one in the hierarchy, and spell out the join relationship between each pair of tables. The last table in the list will be the top table in the hierarchy. For example, in the Hierarchy of a join relationship figure, F is the top table, E is below it, and C is at the bottom of the hierarchy. You could specify the join hierarchy for these three tables as follows:
```
C FULL OUTER JOIN E FULL OUTER JOIN F
```
Use parentheses to control the order in which the join relationships are evaluated. Parentheses control evaluation order just as they do in mathematics, that is, innermost pairs are evaluated first. With this method you start with the top table in the outermost set of parentheses, then any intermediate levels, and include the lowest two levels in the innermost parentheses. Using this method, you would specify the same three tables as follows:
```
(F FULL OUTER JOIN (C FULL OUTER JOIN E))
```
Note that the lowest level table appears first in the innermost parentheses, just as it does in the first method.

Note:

While you can join these three tables in the way described here, in order to create the complete hierarchy shown in the Hierarchy of a join relationship figure you would need to use key joins.

When you create a join index, a message in the log identifies the top table in the join. For example,

[20691]: Join Index 'join_on_tabletable' created from the following join relations:
 [20694]:      Table Name             Relationship
 [20697]: ------------------------------------------------------------------
 [20696]: 1. join_on_table_a joined to 'join_on_table_b' One >> Many
 [20692]: The ultimate/top table is join_on_table_b
 [20697]: ------------------------------------------------------------------