Archives of the TeradataForum

Message Posted: Fri, 26 Apr 2002 @ 11:18:33 GMT


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

Subj:		Explanation Required Please!

From:		Trevor Price

Hi,

Can anyone explain this to me , the first script according to the explain should take roughly 52 mins, however, it was cancelled after 9 hours.

The second had the same estimated running time , but only took 4 mins.

DDL and Stats shown below.

We're running a 2 node 4700 , V2R3.

Regards

Trevor Price
Senior Software Engineer,
Eircom,
Dublin 2

1.  CREATE VOLATILE TABLE CW1458D2
 (
  AREA_CODE           CHAR(6)
 ,CALLED_NO           CHAR(10)
 ,CALLED_EXCH         CHAR(4))

ON COMMIT PRESERVE ROWS;

INSERT INTO CW1458D2
SELECT C1.AREA_CODE
      ,C1.CALLED_NO
      ,L1.EXCH_CODE
FROM CDW_PROD_BV.CALLED_INFO_V    C1 JOIN
     CDW_PROD_BV.LINE_INFO_V   L1
ON C1.STD = L1.STD
AND C1.TELNO = L1.TELNO
;

2.  CREATE VOLATILE TABLE CW1458D2
 (
  AREA_CODE           CHAR(6)
 ,CALLED_NO           CHAR(10)
 ,CALLED_EXCH         CHAR(4))

PRIMARY INDEX(AREA_CODE, CALLED_NO);

INSERT INTO CW1458D2
SELECT C1.AREA_CODE
      ,C1.CALLED_NO
      ,L1.EXCH_CODE
FROM CDW_PROD_BV.CALLED_INFO_V    C1 JOIN
     CDW_PROD_BV.LINE_INFO_V   L1
ON C1.STD = L1.STD
AND C1.TELNO = L1.TELNO
;

`Explanation --------------------------------------------------`
	`1)`	`First, we lock a distinct CDW_PROD_LINE."pseudo table" for read on a RowHash to prevent global deadlock for CDW_PROD_LINE.LINE_INFO.`
	`2)`	`Next, we lock a distinct CDW_PROD_LINE."pseudo table" for read on a RowHash to prevent global deadlock for CDW_PROD_LINE.CALLED_INFO.`
	`3)`	`We lock CDW_PROD_LINE.LINE_INFO for read, and we lock CDW_PROD_LINE.CALLED_INFO for read.`
	`4)`	We do an all-AMPs JOIN step from CDW_PROD_LINE.LINE_INFO by way of a RowHash match scan with no residual conditions, which is joined to CDW_PROD_LINE.CALLED_INFO. CDW_PROD_LINE.LINE_INFO and CDW_PROD_LINE.CALLED_INFO are joined using a merge join, with a join condition of ("(CDW_PROD_LINE.CALLED_INFO.TELNO = CDW_PROD_LINE.LINE_INFO.TELNO) AND (CDW_PROD_LINE.CALLED_INFO.STD = CDW_PROD_LINE.LINE_INFO.STD)"). The result goes into Spool 1, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 1 by row hash. The size of Spool 1 is estimated with low confidence to be 4,265,804 rows. The estimated time for this step is 52 minutes and 37 seconds.
	`5)`	`We do a MERGE into UD1025.CW1458D2 from Spool 1 (Last Use).`
	`6)`	`We spoil the parser's dictionary cache for the table.`
	`7)`	`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.`

     CREATE SET TABLE CDW_PROD_LINE.CALLED_INFO ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           ACC_NO INTEGER,
           STD CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
           TELNO CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC,
           AREA_CODE CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC,
           CALLED_NO CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)
     UNIQUE PRIMARY INDEX ( STD ,TELNO )
     INDEX ( AREA_CODE )
     INDEX ( CALLED_NO );

     02/04/19        23:06:15                         164    AREA_CODE
     02/04/19        23:07:56                   2,890,961    CALLED_NO
     02/04/24        10:22:55                   4,265,804    STD,TELNO

     CREATE SET TABLE CDW_PROD_LINE.LINE_INFO ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           ACC_NO INTEGER,
           ACC_DIST CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           CUST_TYP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
           LINE_TYP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
           SOC_CAT CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
           EX_DIRECTORY_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
           CUST_LINE_STAT CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
           INST_LINE_CNT INTEGER COMPRESS 0 ,
           EXCH_NAME CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '
                  ',
           EXCH_CODE CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '
           LINE_DIST CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
           STD CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
           TELNO CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC,
           PROV_DATE DATE FORMAT 'yyyymmdd')

     UNIQUE PRIMARY INDEX ( STD ,TELNO );

     02/04/20        00:43:33                   1,856,434    ACC_NO
     02/04/20        00:43:57                           3    CUST_TYP
     02/04/20        00:44:30                           4    LINE_TYP
     02/04/20        00:39:38                      16,871    PROV_DATE
     02/04/20        00:38:52                   4,265,804    STD,TELNO


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

Attachments

Library

Quick Reference