 |
 |
Archives of the TeradataForum
Message Posted: Tue, 16 Dec 2003 @ 13:45:07 GMT
| Subj: | | Re: Teradata query output |
| |
| From: | | Narayan Murthy |
Just check these below explains , and you will get your answer :
explain
sel t2.c1 from t2 dervtbl where dervtbl.c1=2; /* similar to your query */
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct CHK."pseudo table" for read on a RowHash to prevent global deadlock for CHK.dervtbl.
| |
| | 2) | Next, we lock CHK.dervtbl for read.
| |
| | 3) | We do an all-AMPs RETRIEVE step from CHK.dervtbl by way of an all-rows scan with a condition of ("CHK.dervtbl.c1 = 2") into Spool 2
(all_amps), which is duplicated on all AMPs. The size of Spool 2 is estimated with no confidence to be 160 rows. The estimated time for
this step is 0.15 seconds.
| |
| | 4) | We do an all-AMPs JOIN step from CHK.t2 by way of an all-rows scan with no residual conditions, which is joined to Spool 2 (Last
Use). CHK.t2 and Spool 2 are joined using a product join, with a join condition of ("(1=1)"). The result goes into Spool 1 (group_amps),
which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 160 rows. The estimated time for this step
is 0.21 seconds.
| |
| | 5) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.36 seconds.
| |
/* what you would have intended and landed up with the above query */
explain
sel dervtbl.c1 from t2 dervtbl where dervtbl.c1=2;
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct CHK."pseudo table" for read on a RowHash to prevent global deadlock for CHK.dervtbl.
| |
| | 2) | Next, we lock CHK.dervtbl for read.
| |
| | 3) | We do an all-AMPs RETRIEVE step from CHK.dervtbl by way of an all-rows scan with a condition of ("CHK.dervtbl.c1 = 2") into Spool 1
(group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 4 rows. The estimated time for
this step is 0.15 seconds.
| |
| | 4) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.15 seconds.
| |
Narayan
| |
