The following statement defines a join index on these tables. Subsequent examples demonstrate the effect of this join index on how the Optimizer processes 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.