This example shows results from joining tables with the following table definitions:
CREATE TABLE customer (c_custkey INTEGER, c_name CHAR(26), c_address VARCHAR(41), c_nationkey INTEGER, c_phone CHAR(16), c_acctbal DECIMAL(13,2), c_mktsegment CHAR(21), c_comment VARCHAR(127)) UNIQUE PRIMARY INDEX( c_custkey );
Result:
*** Table has been created. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------
CREATE TABLE orders (o_orderkey INTEGER NOT NULL, o_custkey INTEGER, o_orderstatus CHAR(1), o_totalprice DECIMAL(13,2) NOT NULL, o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL, o_orderpriority CHAR(21), o_clerk CHAR(16), o_shippriority INTEGER, o_commment VARCHAR(79)) UNIQUE PRIMARY INDEX(o_orderkey);
Result:
*** Table has been created. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------
CREATE TABLE lineitem (l_orderkey INTEGER NOT NULL, l_partkey INTEGER NOT NULL, l_suppkey INTEGER, l_linenumber INTEGER, l_quantity INTEGER NOT NULL, l_extendedprice DECIMAL(13,2) NOT NULL, l_discount DECIMAL(13,2), l_tax DECIMAL(13,2), l_returnflag CHAR(1), l_linestatus CHAR(1), l_shipdate DATE FORMAT 'yyyy-mm-dd', l_commitdate DATE FORMAT 'yyyy-mm-dd', l_receiptdate DATE FORMAT 'yyyy-mm-dd', l_shipinstruct VARCHAR(25), l_shipmode VARCHAR(10), l_comment VARCHAR(44)) PRIMARY INDEX( l_orderkey );
Result:
*** Table has been created. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------
collect stats orders index (o_orderkey) values (0,0,1,10,1,1000000,1000000) ;
Result:
*** Update completed. One row changed. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------
collect stats lineitem index (l_orderkey) values (0,0,1,10,1,500000,1000000 );
Result:
*** Update completed. One row changed. *** Total elapsed time was 1 second.
The following statement defines a join index on these tables:
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);
Result:
*** Index has been created. *** Total elapsed time was 1 second
The following EXPLAIN shows that the Optimizer used 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;
Result:
*** Help information returned. 14 rows. *** Total elapsed time was 1 second. Explanation ----------------------------------------------------------------------- 1) First, we lock EXPLAINSAMPLE.ORDER_JOIN_LINE for read on a reserved RowHash to prevent a global deadlock. 2) Next, we do an all-AMPs RETRIEVE step from EXPLAINSAMPLE.ORDER_JOIN_LINE by way of an all-rows scan with a condition of ("NOT (EXPLAINSAMPLE.ORDER_JOIN_LINE.o_orderdate IS NULL)") into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 36 rows. The estimated time for this step is 0.01 seconds. 3) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.01 seconds.
The following statement drops the join index named order_join_line:
drop join index order_join_line;
Result:
*** Index has been dropped. *** Total elapsed time was 1 second.