The following EXPLAIN report shows how the Optimizer may use the newly created join index, order_join_line.
EXPLAIN SELECT o_orderdate, o_custkey, l_partkey, l_quantity, l_extendedprice FROM lineitem, orders WHERE l_orderkey = o_orderkey;
Explanation
---------------------------------------------------------------------------
1) First, we lock LOUISB.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.order_join_line by way of an all-rows scan with a condition
of ("NOT (LOUISB.order_join_line.o_orderdate IS NULL)") into Spool 1,
which is built locally on the AMPs. The input table is not
cached in memory, but it is eligible for synchronized scanning.
The result spool file is not cached in memory. The size of
Spool 1 is estimated to be 1,000,000 rows. The estimated time for
this step is 4 minutes and 27 seconds.
3) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.