 |
 |
Archives of the TeradataForum
Message Posted: Fri, 26 Oct 2001 @ 18:30:56 GMT
| Subj: | | Execution Plans for ANSI vs non_ANSI formatted SQL |
| |
| From: | | Anomy Anom |
<-- Anonymously Posted: Friday, October 26, 2001 14:02 -->
I've been experimenting with SQL to see what differences may occur on execution plans for SQL written to the ANSI join format vs. the old
list the tables and then list join predicates as where clauses. On my V2R3.0.3 machine the following queries generate different plans as
shown. Is this reasonable? Should they generate the same plan? What does anyone else think?
explain locking row for access
select a.databaseid, tableid, fieldid
from
DBC.TVM a
join
dbc.tvfields b
on (b.tableid = a.tvmid)
where
b.tableid = '000000090000'xb ;
*** Help information has been returned.
*** Time was 1 sec. 10/26/01 01:57:06 PM
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock DBC.a for access.
| |
| | 2) | Next, we do a single-AMP JOIN step from DBC.b by way of the primary index "DBC.b.TableId = '000000090000'XB" with no residual
conditions, which is joined to DBC.a by way of unique index # 4 "DBC.a.TVMId = DBC.b.TableId" extracting row ids only. DBC.b and DBC.a are
joined using a nested join, with a join condition of "(1=1)"). The result goes into Spool 2, which is redistributed by hash code to all
AMPs. Then we do a SORT to order Spool 2 by field Id 1. The size of Spool 2 is estimated with low confidence to be 8 rows. The estimated
time for this step is 0.34 seconds.
| |
| | 3) | We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to DBC.a. Spool 2 and DBC.a are
joined using a row id join, with a join condition of ("(1=1)"). The result goes into Spool 1, which is built locally on the AMPs. The size
of Spool 1 is estimated with low confidence to be 8 rows. The estimated time for this step is 0.47 seconds.
| |
| | 4) | 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.81 seconds.
| |
explain locking row for access
select a.databaseid, tableid, fieldid
from
DBC.TVM a,
dbc.tvfields b
where
b.tableid = a.tvmid
and b.tableid = '000000090000'xb;
*** Help information has been returned.
*** Time was 1 sec. 10/26/01 01:57:07 PM
Explanation
-------------------------------------------------- | | 1) First, we do a two-AMP JOIN step from DBC.a by way of unique index # 4 "DBC.a.TVMId = '000000090000'XB" with no residual conditions,
which is joined to DBC.b by way of the primary index "DBC.b.TableId = DBC.a.TVMId" with an additional condition of "DBC.b.TableId =
'000000090000'XB"). DBC.a and DBC.b are joined using a nested join, with a join condition of ("(1=1)"). The result goes into Spool 1,
which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 8 to 58,400 rows. The estimated time for
this step is 0.22 to 5.58 seconds.
| | -> | The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.22 seconds.
| |
| |
