Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 26 Jul 2001 @ 16:36:24 GMT


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


Subj:   Re: Import causing all-amp operation
 
From:   Mary M. Conley

I was informed that this is talked about in the Teradata Physical Implementation Class.

Here's the explain that shows using character parms in the import and macro is all-amp access:

EXPLAIN
 USING
  I1    (CHAR(9)),
  D1    (CHAR(5)),
  I2    (CHAR(10)),
  DT2   (CHAR(8))
 EXEC MACRO1
   (:I1,
    :D1,
    :I2,
    :DT2);
 *** Help information returned 8 rows.
 *** Total elapsed time was 0.60 seconds.

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct testdb."pseudo table" for write on a RowHash to prevent global deadlock for testdb.TABLE1.  
  2)Next, we lock testdb.TABLE1 for write.  
  3)We do an all-AMPs DELETE from testdb.TABLE1 by way of an all-rows scan.  
  4)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->No rows are returned to the user as the result of statement 1.  


Here's the explain using numeric parms in the import & macro:

EXPLAIN
  USING
   I1   (INTEGER),
   D1   (INTEGER),
   I2   (INTEGER),
   DT2  (INTEGER)
  EXEC MACRO1
    (:I1,
     :D1,
     :I2,
     :DT2);
 *** Help information returned 8 rows.
 *** Total elapsed time was 0.67 seconds.

Explanation
--------------------------------------------------
 
  1)First, we do a single-AMP DELETE from testdb.TABLE1 by way of the primary index "testdb.TABLE1.I1 = :I1, testdb.TABLE1.D1 = :D1 , testdb.TABLE1.I2 = :I2" with a residual condition of ("((testdb.TABLE1.D1 (INTEGER, NAMED D1, FORMAT '9(5)'))= :D1) AND (testdb.TABLE1.DT2 = :DT2)").  
  ->No rows are returned to the user as the result of statement 1.  



     
  <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: 15 Jun 2023