Archives of the TeradataForum

Message Posted: Fri, 22 Sep 2006 @ 18:50:48 GMT

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

I have a question about the TOP N function. It appears to not behave inconsistently at times.

I have been using a query "SELECT TOP 1 * FROM tablename " to determine if a large table is empty or not. I expect a very fast result based on earlier forum posts. This is true most of the time. Sometimes, it only works quickly when I specify a columname instead of the asterisk.

Example

We are V2R6

Table demographics (really big table):

Tablename (transauditrevfactbase) a non-indexed column is (AIC)

current stats collected

1.7 billion rows

has a NUPI and a NUSI

max data width = 442 bytes

     SELECT TOP 1 * FROM transauditrevfactbase

       1) First, we lock a distinct edwacctngtest."pseudo table" for read on
          a RowHash to prevent global deadlock for
          edwacctngtest.TransAuditRevFactBase.
       2) Next, we lock edwacctngtest.TransAuditRevFactBase for read.

     ==>
       3) We do an all-AMPs RETRIEVE step from
          edwacctngtest.TransAuditRevFactBase by way of an all-rows scan
          with no residual conditions into Spool 2 (all_amps), which is
          built locally on the AMPs.  The input table will not be cached in
          memory, but it is eligible for synchronized scanning.  The result
          spool file will not be cached in memory.  The size of Spool 2 is
          estimated with high confidence to be 1,721,084,577 rows.  The
          estimated time for this step is 10 minutes and 25 seconds.
     ==>

       4) We do an all-AMPs STAT FUNCTION step from Spool 2 by way of an
          all-rows scan into Spool 5, which is built locally on the AMPs.
          The result rows are put into Spool 1 (group_amps), which is built
          locally on the AMPs.  This step is used to retrieve the TOP 1 rows.

          Single AMP optimization is used. If this step retrieves less than
          1 rows, then execute step 5.
       5) We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by
          way of an all-rows scan into Spool 5 (Last Use), which is built
          locally on the AMPs.  The result rows are put into Spool 1
          (group_amps), which is built locally on the AMPs.  This step is
          used to retrieve the TOP 1 rows.
       6) 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.

USE THE SAME TABLE, SAME SYSTEM, SAME DAY, LOW SYSTEM LOAD .....

     SELECT TOP 1 AIC FROM transauditrevfactbase

       1) First, we lock a distinct edwacctngtest."pseudo table" for read on
          a RowHash to prevent global deadlock for
          edwacctngtest.TransAuditRevFactBase.
       2) Next, we lock edwacctngtest.TransAuditRevFactBase for read.

     ==>  NOTICE THE ABSENCE OF THE ALL AMP RETRIEVAL AND SPOOL


       3) We do an all-AMPs STAT FUNCTION step from
          edwacctngtest.TransAuditRevFactBase by way of an all-rows scan
          with no residual conditions into Spool 5, which is built locally
          on the AMPs.  The result rows are put into Spool 1 (group_amps),
          which is built locally on the AMPs.  This step is used to retrieve
          the TOP 1 rows.  Single AMP optimization is used. If this step
          retrieves less than 1 rows, then execute step 4.
       4) We do an all-AMPs STAT FUNCTION step from
          edwacctngtest.TransAuditRevFactBase by way of an all-rows scan
          with no residual conditions into Spool 5 (Last Use), which is
          built locally on the AMPs.  The result rows are put into Spool 1
          (group_amps), which is built locally on the AMPs.  This step is
          used to retrieve the TOP 1 rows.
       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.

Can anyone shed some light on this behavior? I have other tables that are larger and wider and the TOP 1 * works as in the second example(no all amps retrieval step). I also get the first results on a smaller, development version (31 million row) of this table. I also checked tables with NUPI vs UPI and get same results.(some work, some don't, both index types).

Randall Barbour

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