Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 26 Oct 2001 @ 18:30:56 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


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



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016