簡単なEXPLAINの例 - Advanced SQL Engine - Teradata Database

以下に示すEXPLAINの例は、次の表定義を有する表を結合した場合の結果です。

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 );

 *** 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);

 *** 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 );

 *** Table has been created.
 *** Total elapsed time was 1 second.
+---------+---------+---------+---------+---------+---------+---------
collect stats orders index (o_orderkey) values (0,0,1,10,1,1000000,1000000)
;

 *** 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
);

 *** Update completed. One row changed.
 *** Total elapsed time was 1 second.

次の文は、上記の表に結合索引を定義します。

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 1 second

以下のEXPLAINからは、新たに作成された結合索引order_join_lineが最適化ルーチンによって使用されたということがわかります。

EXPLAIN
SELECT o_orderdate, o_custkey, l_partkey, l_quantity,
l_extendedprice
FROM lineitem , orders
WHERE l_orderkey = o_orderkey;

 *** 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.

次の文は、結合索引order_join_lineを削除します。

drop join index order_join_line;

 *** Index has been dropped.
 *** Total elapsed time was 1 second.