Filter
Top Products
Using join indexes
162
• 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 Figure 4-1 you would need to use
key joins. See “Types of join hierarchies” for more information.
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]: ------------------------------------------------------------------
Issuing the CREATE JOIN INDEX statement
To create a join index, issue the CREATE JOIN INDEX statement. Here is a
summary of the syntax for this command:
CREATE JOIN INDEX
join-index-name
FOR
join-clause
The parameters of this command are:
join-clause
:
[ ( ]
join-expression
join-type
join-expression
[ ON
search-condition
] [ ) ]
join-expression
:
{
table-name
|
join-clause
}
join-type
:
[ NATURAL ] FULL [ OUTER ] JOIN