This example set uses the following table definitions.
CREATE TABLE customer ( c_custkey INTEGER, c_name CHARACTER(26), c_address VARCHAR(41), c_nationkey INTEGER, c_phone CHARACTER(16), c_acctbal DECIMAL(13,2), c_mktsegment CHARACTER(21), c_comment VARCHAR(127)) UNIQUE PRIMARY INDEX (c_custkey); CREATE TABLE orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER, o_orderstatus CHARACTER(1), o_totalprice DECIMAL(13,2) NOT NULL, o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL, o_orderpriority CHARACTER(21), o_clerk CHARACTER(16), o_shippriority INTEGER, o_commment VARCHAR(79)) UNIQUE PRIMARY INDEX (o_orderkey); 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 CHARACTER(1), l_linestatus CHARACTER(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);
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);
Result:
*** Index has been created. *** Total elapsed time was 15 seconds.
The following query is provided as an example to show how the newly created join index, order_join_line, can be used by the Optimizer. An EXPLAIN of the SELECT statement includes a RETRIEVE step from join index table order_join_line by way of an all-rows scan with a condition of ("NOT (order_join_line.o_orderdate IS NULL)").
EXPLAIN SELECT o_orderdate, o_custkey, l_partkey, l_quantity, l_extendedprice FROM lineitem, orders WHERE l_orderkey = o_orderkey;
Result:
Explanation --------------------------------------------------------------------------- 1) First, we lock ORDER_JOIN_LINE for read on a reserved RowHash to prevent global deadlock. 2) Next, we lock ORDER_JOIN_LINE for read. 3) We do an all-AMPs RETRIEVE step from tom.ORDER_JOIN_LINE by way of an all-rows scan with a condition of ("NOT (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 to be 1,000,000 rows. The estimated time for this step is 4 minutes and 27 seconds. -> The contents of Spool 1 are sent back to the user as the result of statement 1.
The following query is provided as an example to show how the join index might be used when an additional search condition is added on the join indexed rows. An EXPLAIN of the SELECT statement includes a RETRIEVE step from join index table order_join_line with a range constraint of ( "order_join_line.Field_1026 > 971101") with a residual condition of ("(NOT (order_join_line.o_orderdate IS NULL )) AND (order_join_line.Field_1026 > 971101)."
SELECT o_orderdate, o_custkey, l_partkey, l_quantity, l_extendedprice FROM lineitem, orders WHERE l_orderkey = o_orderkey AND o_orderdate > '1997-11-01';
The following query is provided as an example to show how the join index might be used when aggregation is performed on the join indexed rows. An EXPLAIN of the SELECT statement includes a SUM step to aggregate from join index table order_join_line with a range constraint of ("order_join_line.Field_1026 > 971101") with a residual condition of "(order_join_line.Field_1026 > 971101) AND (NOT (order_join_line.o_orderdate IS NULL ))") and the grouping identifier.
SELECT l_partkey, avg(l_quantity), AVG(l_extendedprice) FROM lineitem, orders WHERE l_orderkey = o_orderkey AND o_orderdate > '1997-11-01' GROUP BY l_partkey;
The following query is provided as an example to show how the join index might be used in a query when join indexed rows are used to join with another base table. An EXPLAIN of the SELECT statement includes a RETRIEVE step from join index table order_join_line with a condition of ("NOT (order_join_line.o_orderdate IS NULL)") and a SORT to order by row hash. This is followed by a JOIN step from customer using a merge join, with a join condition of ("o_custkey =customer.c_custkey").
SELECT o_orderdate, c_name, c_phone, l_partkey, l_quantity, l_extendedprice FROM lineitem, orders, customer WHERE l_orderkey = o_orderkey AND o_custkey = c_custkey;
The following query is provided as an example to show how the join index might be used in a query of a single table. An EXPLAIN of the SELECT statement includes a RETRIEVE step from join index table order_join_line with a condition of ("order_join_line.l_partkey = 1001").
SELECT l_orderkey, l_partkey, l_quantity, l_extendedprice FROM lineitem WHERE l_partkey = 1001;