Archives of the TeradataForum

Message Posted: Fri, 12 Dec 2003 @ 09:21:51 GMT

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

Hello,

I found an interesting little problem with V2R5.0 and derived tables and wanted to know if somebody else experienced similar problems.

The following query returns as many rows as there are records in testtable:

SELECT 'Testname'
FROM   (SELECT COUNT(*) as y FROM testtable) f;

Of course only one row should be returned as is the case with V2R4. The explain shows that there is no aggregation step We can fix that query by adding a group by and forcing the optimizer to evaluate the derived table first:

SELECT 'Testname'
FROM   (SELECT 1 as x, COUNT(*) as y FROM testtable GROUP BY 1) f;

However since we're using derived tables frequently I would like to know if there are more problems with the new functionality of having them integrated in the overall execution plan.

Obviously the above example is very simple and we have a workaround.

We'll open an incident with NCR.

Roland

For the interested ones, here are the explains:

Query 1:

  3) We do an all-AMPs RETRIEVE step from
     testtable by way of an all-rows scan with no
     residual conditions into Spool 1 (group_amps), which is built
     locally on the AMPs.  The size of Spool 1 is estimated with low
     confidence to be 15 rows.  The estimated time for this step is
     0.56 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.56 seconds.

Query 2:

  3) We do an all-AMPs SUM step to aggregate from
     testtable by way of an all-rows scan with no
     residual conditions.  Aggregate Intermediate Results are computed
     globally, then placed in Spool 3.  The size of Spool 3 is
     estimated with high confidence to be 1 row.  The estimated time
     for this step is 0.72 seconds.
  4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is built locally
     on the AMPs.  The size of Spool 1 is estimated with high
     confidence to be 1 row.  The estimated time for this step is 0.67
     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.

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