Simple EXPLAIN Example - Analytics Database - Teradata Vantage

Database Introduction

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-09-27
dita:mapPath
gtm1628096154303.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
dsm1472253642401
lifecycle
latest
Product Category
Teradata Vantageā„¢

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.