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

Attachments

Library

Quick Reference