The following example shows the creation of a join index defined with an n-way join result and then shows how the Optimizer uses the join index to process a query on the base tables for which it is defined.
Join Index Definition
The following statement defines a join index with a three-table join using both natural and outer joins.
CREATE JOIN INDEX cust_order_join_line AS SELECT (l_orderkey, o_orderdate, c_nationkey, o_totalprice), (l_partkey, l_quantity, l_extendedprice, l_shipdate) FROM (lineitem LEFT JOIN orders ON l_orderkey = o_orderkey) INNER JOIN customer ON o_custkey = c_custkey PRIMARY INDEX (l_orderkey); *** Index has been created. *** Total elapsed time was 20 seconds.
EXPLAIN for Query with Complicated Predicates
The following EXPLAIN shows how the Optimizer might use the join index for a query that accesses all three of the base tables defined in the index.
EXPLAIN SELECT l_orderkey, o_orderdate, o_totalprice, l_partkey,
l_quantity, l_extendedprice, l_shipdate
FROM lineitem, orders, customer
WHERE l_orderkey = o_orderkey
AND o_custkey = c_custkey
AND c_nationkey = 10;
*** Help information returned. 16 rows.
*** Total elapsed time was 1 second.
Explanation
--------------------------------------------------------------------------
1) First, we lock LOUISB.cust_order_join_line for read on a
reserved Row Hash to prevent a global deadlock.
2) Next, we do an all-AMPs RETRIEVE step from join index table
LOUISB.cust_order_join_line by way of an all-rows scan with a
condition of ("LOUISB.cust_order_join_line.c_nationkey = 10") into
Spool 1, which is built locally on the AMPs. The input table will
not be cached in memory, but it is eligible for synchronized
scanning. The result spool file will not be cached in memory.
The size of Spool 1 is estimated to be 200 rows. The estimated time
for this step is 3 minutes and 57 seconds.
3) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.