 |
 |
Archives of the TeradataForum
Message Posted: Fri, 19 Apr 2002 @ 12:10:03 GMT
| 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.
| |
