Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 19 Apr 2002 @ 12:10:03 GMT


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


Subj:   SQL Question for all you experts out there (part 2)
 
From:   Tony Howe

Here some more interesting stuff concerning my first SQL question. May be a little long, please bare with me on this one:

Here's Query #1:

SELECT    B1.AQBICLS, B1.ASEIXXX,B1.AFVCPRO,B1.AQBICLSC1,
          A1.AQBICLSF4
FROM      CPCLMA.QCLMA001 A1,
               (
                    SELECT    DISTINCT AQBICLS,ASEIXXX, AFVCPRO,AQBICLSC1
                    FROM      CPCLMB.QCLMB001
                    WHERE     ASEIXXX IN ('02024640948','01620099028')
                     AND AFVCPRO IN ('87620','87621','87622')
               ) B1
WHERE     A1.AQBICLS=B1.AQBICLS;

Explain for Query #1:

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct CPCLMB."pseudo table" for read on a RowHash to prevent global deadlock for CPCLMB.QCLMB001.  
  2)Next, we lock a distinct CPCLMA."pseudo table" for read on a RowHash to prevent global deadlock for CPCLMA.A1.  
  3)We lock CPCLMB.QCLMB001 for read, and we lock CPCLMA.A1 for read.  
  4)We do an all-AMPs RETRIEVE step from CPCLMB.QCLMB001 by way of index # 24 "CPCLMB.QCLMB001.AFVCPRO = '87622'" extracting row ids only with no residual conditions into Spool 2, which is built locally on the AMPs. The size of Spool 2 is estimated to be 13,806 rows. The estimated time for this step is 0.05 seconds.  
  5)We do an all-AMPs RETRIEVE step from CPCLMB.QCLMB001 by way of index # 24 "CPCLMB.QCLMB001.AFVCPRO = '87621'" extracting row ids only with no residual conditions into Spool 2, which is built locally on the AMPs. The size of Spool 2 is estimated to be 27,612 rows. The estimated time for this step is 0.05 seconds.  
  6)We do an all-AMPs RETRIEVE step from CPCLMB.QCLMB001 by way of index # 24 "CPCLMB.QCLMB001.AFVCPRO = '87620'" extracting row ids only with no residual conditions into Spool 2, which is built locally on the AMPs. Then we do a SORT to order Spool 2 by row id eliminating duplicate rows. The size of Spool 2 is estimated to be 41,417 rows. The estimated time for this step is 0.06 seconds.  
  7)We do an all-AMPs RETRIEVE step from CPCLMB.QCLMB001 by way of row ids from Spool 2 (Last Use) with a residual condition of ("(CPCLMB.QCLMB001.ASEIXXX = '02024640948') OR (CPCLMB.QCLMB001.ASEIXXX = '01620099028')") into Spool 1, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1 eliminating duplicate rows. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 1 is estimated with no confidence to be 7,870 rows. The estimated time for this step is 38.02 seconds.  
  8)We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan into Spool 4, which is redistributed by hash code to all AMPs. The size of Spool 4 is estimated with no confidence to be 7,870 rows. The estimated time for this step is 0.11 seconds.  
  9)We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an all-rows scan, which is joined to CPCLMA.A1. Spool 4 and CPCLMA.A1 are joined using a single partition hash join, with a join condition of ("CPCLMA.A1.AQBICLS = AQBICLS"). The input table CPCLMA.A1 will not be cached in memory. The result goes into Spool 3, which is built locally on the AMPs. The size of Spool 3 is estimated with no confidence to be 7,870 rows. The estimated time for this step is 7.31 seconds.  
  10)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 3 are sent back to the user as the result of statement 1. The total estimated time is 45.59 seconds.  


Results of Query #1: This is the correct answer!!

AQBICLS   ASEIXXX   AFVCPRO   AQBICLSC1  AQBICLSF4
719766317 01620099028    87622           999999537      999999537
778417390 02024640948    87622           999999501      999999501

Here's Query #2: the only thing different is DISTINCT has been removed.

SELECT   B1.AQBICLS, B1.ASEIXXX,B1.AFVCPRO,B1.AQBICLSC1,
          A1.AQBICLSF4
FROM      CPCLMA.QCLMA001 A1,
               (
                    SELECT      AQBICLS,ASEIXXX, AFVCPRO,AQBICLSC1
                    FROM      CPCLMB.QCLMB001
                    WHERE     ASEIXXX IN ('02024640948','01620099028')
                     AND AFVCPRO IN ('87620','87621','87622')
               ) B1
WHERE     A1.AQBICLS=B1.AQBICLS;

Explain for Query #2:

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct CPCLMB."pseudo table" for read on a RowHash to prevent global deadlock for CPCLMB.QCLMB001.  
  2)Next, we lock a distinct CPCLMA."pseudo table" for read on a RowHash to prevent global deadlock for CPCLMA.A1.  
  3)We lock CPCLMB.QCLMB001 for read, and we lock CPCLMA.A1 for read.  
  4)We do an all-AMPs RETRIEVE step from CPCLMB.QCLMB001 by way of index # 24 "CPCLMB.QCLMB001.AFVCPRO = '87622'" extracting row ids only with no residual conditions into Spool 2, which is built locally on the AMPs. The size of Spool 2 is estimated to be 13,806 rows. The estimated time for this step is 0.05 seconds.  
  5)We do an all-AMPs RETRIEVE step from CPCLMB.QCLMB001 by way of index # 24 "CPCLMB.QCLMB001.AFVCPRO = '87621'" extracting row ids only with no residual conditions into Spool 2, which is built locally on the AMPs. The size of Spool 2 is estimated to be 27,612 rows. The estimated time for this step is 0.05 seconds.  
  6)We do an all-AMPs RETRIEVE step from CPCLMB.QCLMB001 by way of index # 24 "CPCLMB.QCLMB001.AFVCPRO = '87620'" extracting row ids only with no residual conditions into Spool 2, which is built locally on the AMPs. Then we do a SORT to order Spool 2 by row id eliminating duplicate rows. The size of Spool 2 is estimated to be 41,417 rows. The estimated time for this step is 0.06 seconds.  
  7)We do an all-AMPs RETRIEVE step from CPCLMB.QCLMB001 by way of row ids from Spool 2 (Last Use) with a residual condition of ("(CPCLMB.QCLMB001.ASEIXXX = '02024640948') OR (CPCLMB.QCLMB001.ASEIXXX = '01620099028')") into Spool 1, which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 1 is estimated with no confidence to be 7,870 rows. The estimated time for this step is 37.96 seconds.  
  8)We do an all-AMPs JOIN step from Spool 1 (Last Use) by way of a RowHash match scan, which is joined to CPCLMA.A1. Spool 1 and CPCLMA.A1 are joined using a merge join, with a join condition of ("CPCLMA.A1.AQBICLS = AQBICLS"). The input table CPCLMA.A1 will not be cached in memory. The result goes into Spool 3, which is built locally on the AMPs. The size of Spool 3 is estimated with no confidence to be 7,870 rows. The estimated time for this step is 7.31 seconds.  
  9)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 3 are sent back to the user as the result of statement 1. The total estimated time is 45.42 seconds.  


Results of Query #2: this gives the wrong two rows, bad answer!!

AQBICLS   ASEIXXX   AFVCPRO   AQBICLSC1  AQBICLSF4
719766317 01620099028    99214           719766317      999999537
778417390 02024640948    S0612           778417390      999999501

Other Useful Information concerning above queries:

Result of query:  SELECT   AQBICLS,ASEIXXX  ,AFVCPRO, AQBICLSC1
     FROM CPCLMB.QCLMB001
WHERE AQBICLS IN (719766317,778417390) ORDER BY 1

AQBICLS   ASEIXXX   AFVCPRO   AQBICLSC1
719766317 01620099028    87622           999999537   ** good row from query 1
719766317 01620099028    99214           719766317
778417390 02024640948    87622           999999501   ** good row from query 1
778417390 02024640948    S0612           778417390

Thanks, Tony.



     
  <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