|
|
Archives of the TeradataForum
Message Posted: Thu, 26 Jul 2001 @ 16:36:24 GMT
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.
| |
| |