Join Index Definition
The following statement defines a join index on these tables. Subsequent examples demonstrate the effect of this join index on how the Optimizer processes various queries.
CREATE JOIN INDEX order_join_line
AS SELECT (l_orderkey, o_orderdate, o_custkey,
o_totalprice),
(l_partkey, l_quantity, l_extendedprice, l_shipdate)
FROM lineitem LEFT JOIN orders ON l_orderkey = o_orderkey
ORDER BY o_orderdate
PRIMARY INDEX (l_orderkey);
*** Index has been created.
*** Total elapsed time was 15 seconds.