Archives of the TeradataForum

Message Posted: Fri, 27 Jul 2001 @ 07:03:41 GMT


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

Subj:		Re: Import causing all-amp operation

From:		Dieter N�th

inline

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

That's right, i'm delivering that training this week ;-) But it deals with CHAR fields and INTEGERs for access. Then there must be a full table scan on the table, because there are a lot of different strings containing e.g. 3:

'3', '003', ' 3', '3.0' ....

so it's impossible to use the row hash. But here's an INT column accessed by a CHAR value and it's possible to cast it to an INT and then calculate the row hash...

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.

This is strange because explaining the same macro on my system returns an single-amp step:

RELEASE V2R.04.00.01.17
VERSION 04.00.01.45

CREATE SET TABLE TERADATA_EDUCATION.table1 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      I1 INTEGER FORMAT '9(9)' NOT NULL,
      D1 DECIMAL(11,6) FORMAT '9(5)' NOT NULL,
      I2 INTEGER FORMAT '9(10)' NOT NULL,
      DT1 DATE FORMAT 'YYYYMMDD',
      TM1 INTEGER FORMAT '9(6)',
      DT2 DATE FORMAT 'YYYYMMDD',
      TM2 INTEGER FORMAT '9(6)')
PRIMARY INDEX ( I1 ,D1 ,I2 );

REPLACE MACRO MACRO1
    (
      I1  (CHAR(09)),
      D1  (CHAR(05)),
      I2  (CHAR(10)),
      DT2 (CHAR(08))
    )
    AS
    (
     DELETE FROM TABLE1
       WHERE I1   EQ :I1
         AND D1   EQ :D1
         AND I2   EQ :I2
         AND DT2  EQ :DT2;
    );

EXPLAIN
 USING
  I1    (CHAR(9)),
  D1    (CHAR(5)),
  I2    (CHAR(10)),
  DT2   (CHAR(8))
 EXEC MACRO1
   (:I1,
    :D1,
    :I2,
    :DT2);

  1) First, we do a single-AMP DELETE from TERADATA_EDUCATION.TABLE1 by
     way of the primary index "TERADATA_EDUCATION.TABLE1.I1 = :I1
     (FLOAT, FORMAT '-9.99999999999999E-999')LATIN,
     TERADATA_EDUCATION.TABLE1.D1 = :D1 (FLOAT, FORMAT
     '-9.99999999999999E-999')LATIN, TERADATA_EDUCATION.TABLE1.I2 =
     :I2 (FLOAT, FORMAT '-9.99999999999999E-999')LATIN" with a residual
     condition of ("((TERADATA_EDUCATION.TABLE1.I1 )= (:I1 (FLOAT,
     FORMAT '-9.99999999999999E-999')LATIN)) AND
     (((TERADATA_EDUCATION.TABLE1.D1 )= (:D1 (FLOAT, FORMAT
     '-9.99999999999999E-999')LATIN)) AND
     (((TERADATA_EDUCATION.TABLE1.I2 )= (:I2 (FLOAT, FORMAT
     '-9.99999999999999E-999')LATIN)) AND
     (TERADATA_EDUCATION.TABLE1.DT2 = (:DT2 (DATE, FORMAT
     'YYYYMMDD')LATIN))))").
  -> No rows are returned to the user as the result of statement 1.

best regards

Dieter


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

Attachments

Library

Quick Reference