Query issue

Database
Enthusiast

Query issue

can someone breifly explain whats this query will do

 

SELECT * FROM AGMT_PROD C WHERE (SELECT MIN (C.AGMT_ID)) BETWEEN 0 AND 1

 

The explain says 

 

1) First, we lock a distinct DB_T_UAT_CORE."pseudo table" for read on
a RowHash to prevent global deadlock for DB_T_UAT_CORE.C.
2) Next, we lock DB_T_UAT_CORE.C for read.
3) We do an all-AMPs SUM step to aggregate from DB_T_UAT_CORE.C by
way of an all-rows scan with no residual conditions
, grouping by field1 ( DB_T_UAT_CORE.C.ROWID). Aggregate
Intermediate Results are computed globally, then placed in Spool 4.
The size of Spool 4 is estimated with high confidence to be 24,981
rows (774,411 bytes). The estimated time for this step is 0.07
seconds.
4) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of
an all-rows scan into Spool 1 (all_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with high
confidence to be 24,981 rows (949,278 bytes). The estimated time
for this step is 0.03 seconds.
5) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of
an all-rows scan with a condition of ("(Field_2 <= 1) AND (Field_2
>= 0)") into Spool 6 (all_amps), which is redistributed by the
hash code of (DB_T_UAT_CORE.C.Field_2) to all AMPs. Then we do a
SORT to order Spool 6 by row hash. The size of Spool 6 is
estimated with high confidence to be 24,981 rows (949,278 bytes).
The estimated time for this step is 0.49 seconds.
6) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of a
RowHash match scan, which is joined to DB_T_UAT_CORE.C by way of a
RowHash match scan with no residual conditions. Spool 6 and
DB_T_UAT_CORE.C are joined using a merge join, with a join
condition of ("DB_T_UAT_CORE.C.ROWID = Field_3"). The result goes
into Spool 2 (group_amps), which is built locally on the AMPs.
The size of Spool 2 is estimated with low confidence to be 24,981
rows (5,470,839 bytes). The estimated time for this step is 0.07
seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 2 are sent back to the user as the result of
statement 1. The total estimated time is 0.65 seconds.

 

 

 

1 REPLY
Teradata Employee

Re: Query issue

With no FROM clause in the subquery, the query is interpreted as a correlated subquery - the MIN is calculated for each row. The plan shows this calculation of the MIN with an implicit GROUP BY ROWID followed by a join back to the table.

 

The goal of this query is not clear since no expression is specified to relate the MIN calculation to the value in the row. It would be good to get an explanation of the goal.