 |
 |
Archives of the TeradataForum
Message Posted: Thu, 12 Feb 2004 @ 21:17:18 GMT
| Subj: | | Different explains from queryman versus Session Information in Teradata Manager |
| |
| From: | | McBride, Michael |
Can anyone tell me why I get different explains from the explain command in Queryman versus what actually is executing according to the SQL
capture / explain in the Sessions Information monitoring tool within Teradata Manager...the difference is that the executing query is using an ALL
ROWS scan in Step 5 whereas the preliminary explain (from QueryMan) indicated the utilization of an index #8 ( the slstrns_dt column has a
secondary index with stats)....
This is the query as shown by the Session Information Utility while executing:
UPDATE aeo_prodstagingdb.sales_trans_sumry
set slstrns_active_in = aeo_prodworkdb.TT_ACTFLGUPD_ss.active_flg
where
slstrns_stor_nu = aeo_prodworkdb.TT_ACTFLGUPD_ss.store_nu
and slstrns_dt = aeo_prodworkdb.TT_ACTFLGUPD_ss.transaction_dt
and slstrns_rgstr_nu = aeo_prodworkdb.TT_ACTFLGUPD_ss.register_nu
and slstrns_nu = aeo_prodworkdb.TT_ACTFLGUPD_ss.transaction_nu
and slstrns_seq_nu = aeo_prodworkdb.TT_ACTFLGUPD_ss.seq_nu;
The highlighted step below is shows the difference (step #5)
Explanation
-------------------------------------------------- | |
| | 1) | First, lock [DBId=0x0441]."pseudo table" for read on a row hash.
| |
| | 2) | Next, we lock [DBId=0x0413]."pseudo table" for write on a row hash.
| |
| | 3) | We lock [DBId=0x0441].[TBId=0x0002 0xED0D] for read and we lock [DBId=0x0413].[TBId=0x0003 0x0605] for write.
| |
| | 4) | We do an All-AMPs RETRIEVE step from [DBId=0x0441].[TBId=0x0002 0xED0D] by way of an all-rows scan into Spool 14810, which is duplicated on
all AMPs.
| |
| | 5) | We do an All-AMPs JOIN step from Spool 14810 (Last Use) by way of an all-rows scan, which is joined to table [TBId=0x0003 0x0605]. Spool
14810 and table [TBId=0x0003 0x0605] are joined using a nested join . The result goes into Spool 14811, which is built locally on the AMPs.
| |
| | 6) | We do an All-AMPs JOIN step from Spool 14811 (Last Use) by way of an all-rows scan, which is joined to table [TBId=0x0003 0x0605]. Spool
14811 and table [TBId=0x0003 0x0605] are joined using a row id join . The result goes into Spool 14809, which is built locally on the AMPs.
| |
| | 7) | We read table ids out of table [TBId=0x0003 0x0605] and delete the corresponding table headers and data.
| |
| | 8) | We do a MERGE into table [TBId=0x0003 0x0605] from Spool 14812.
| |
| | 9) | We send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
This is the explain request in QueryMan:
explain
UPDATE aeo_prodstagingdb.sales_trans_sumry
set slstrns_active_in = aeo_prodworkdb.TT_ACTFLGUPD_ss.active_flg
where
slstrns_stor_nu = aeo_prodworkdb.TT_ACTFLGUPD_ss.store_nu
and slstrns_dt = aeo_prodworkdb.TT_ACTFLGUPD_ss.transaction_dt
and slstrns_rgstr_nu = aeo_prodworkdb.TT_ACTFLGUPD_ss.register_nu
and slstrns_nu = aeo_prodworkdb.TT_ACTFLGUPD_ss.transaction_nu
and slstrns_seq_nu = aeo_prodworkdb.TT_ACTFLGUPD_ss.seq_nu;
And this is the resulting explain text:
Note that step 5 uses an index. Stats on the indexes are up-to-date:
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct aeo_prodworkdb."pseudo table" for read on a RowHash to prevent global deadlock for
aeo_prodworkdb.TT_ACTFLGUPD_ss.
| |
| | 2) | Next, we lock a distinct aeo_proddb."pseudo table" for write on a RowHash to prevent global deadlock for aeo_proddb.SALES_TRANS_SUMRY.
| |
| | 3) | We lock aeo_prodworkdb.TT_ACTFLGUPD_ss for read, and we lock aeo_proddb.SALES_TRANS_SUMRY for write.
| |
| | 4) | We do an all-AMPs RETRIEVE step from aeo_prodworkdb.TT_ACTFLGUPD_ss by way of an all-rows scan with no residual conditions into Spool 2,
which is duplicated on all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with low confidence to be
4,000 rows. The estimated time for this step is 0.04 seconds.
| |
| | 5) | We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to aeo_proddb.SALES_TRANS_SUMRY by way of a
traversal of index # 8 without accessing the base table extracting row ids only. Spool 2 and aeo_proddb.SALES_TRANS_SUMRY are joined using a
nested join, with a join condition of ("aeo_proddb.SALES_TRANS_SUMRY.slstrns_dt = transaction_dt"). The input table aeo_proddb.SALES_TRANS_SUMRY
will not be cached in memory. The result goes into Spool 3, which is built locally on the AMPs. Then we do a SORT to order Spool 3 by field Id
1. The size of Spool 3 is estimated with low confidence to be 25,880 rows. The estimated time for this step is 17 minutes and 53 seconds.
| |
| | 6) | We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an all-rows scan, which is joined to aeo_proddb.SALES_TRANS_SUMRY. Spool 3
and aeo_proddb.SALES_TRANS_SUMRY are joined using a row id join, with a join condition of ("(aeo_proddb.SALES_TRANS_SUMRY.slstrns_seq_nu = seq_nu)
AND ((aeo_proddb.SALES_TRANS_SUMRY.slstrns_nu = transaction_nu) AND ((aeo_proddb.SALES_TRANS_SUMRY.slstrns_rgstr_nu = register_nu) AND
(aeo_proddb.SALES_TRANS_SUMRY.slstrns_stor_nu = store_nu )))"). The input table aeo_proddb.SALES_TRANS_SUMRY will not be cached in memory. The
result goes into Spool 1, which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1. The size of
Spool 1 is estimated with low confidence to be 25,880 rows. The estimated time for this step is 17 minutes and 54 seconds.
| |
| | 7) | We do a MERGE DELETE to aeo_proddb.SALES_TRANS_SUMRY from Spool 1 (Last Use) via the row id. New updated rows are built and the result
goes into Spool 4, which is built locally on the AMPs. Then we do a SORT to order Spool 4 by row hash.
| |
| | 8) | We do a MERGE into aeo_proddb.SALES_TRANS_SUMRY from Spool 4 (Last Use).
| |
| | 9) | 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.
| |
Any insight into what is happening here would be helpful...
Michael E. McBride
Teradata Certified Master
Teradata Database Administrator
Data Architect and Data Warehouse Practitioner
American Eagle Outfitters
| |
