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

search-condition:
[ ( ] search-expression [ AND  search-expression ] [ ) ]

The join-clause can be expressed either with or without parentheses.
The ON clause can reference only two tables. One must be the current one, and the other can be any one table in the current join tree.
All join predicates must be equijoins; that is, the search_expression must indicate that the value in column_1 equals the value in column_2. No single-variable predicates, intracolumn comparisons, or non-equality joins are permitted in the ON clause.
To specify a multicolumn join, you include more than one predicate linking the two tables, and connect them with logical AND. Note that multicolumn indexes on base tables are not replicated in join indexes created using those base tables.
You cannot connect join predicates with logical OR.
The keyword NATURAL can replace the ON clause, when you are pairing columns from a single pair of tables by name.
Join index tables must be IQ base tables. They must not be temporary tables, remote tables, or proxy tables.

Example 1: Key join

Here is an example of how you create a join index for the key join between the sales_order table and the customer table. Remember that this is a key join, based on the foreign key ky_so_customer which relates the cust_id column of sales_order to the primary key id of the customer table. You can give the index any name you want. This example names it ky_so_customer_join to identify the foreign key on which the key join relies.

CREATE JOIN INDEX ky_so_customer_join 
FOR customer FULL OUTER JOIN sales_order

Example 2: ON clause join

The next example shows how you could create a join index for the same two tables using an ON clause. You could use this syntax whether or not the foreign key existed.

CREATE JOIN INDEX customer_sales_order_join 
FOR customer FULL OUTER JOIN sales_order
ON customer_id=sales_order.cust_id

Example 3: Natural join

To create a natural join, the joined columns must have the same name. If you created a natural join on the tables in previous examples, you would not get the expected results at all. Instead of joining the id column of customer to the cust_id column of sales_order, the following command would join the dissimilar id columns of the two tables:

CREATE JOIN INDEX customer_sales_order_join 
FOR customer NATURAL FULL OUTER JOIN sales_order

A natural join between the id columns of sales_order and sales_order_items makes more sense. In this case, the columns with the same name should contain matching values. The command to create a join index based on a natural join between these two tables is:

CREATE JOIN INDEX sales_order_so_items_join
FOR sales_order NATURAL FULL OUTER JOIN sales_order_items