Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 21 Mar 2002 @ 17:17:38 GMT


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


Subj:   Sample query
 
From:   Anomy Anom

<-- Anonymously Posted: Thursday, March 21, 2002 11:24 -->

Hi,

I am trying to get a Sample 1000 from a huge table and am running out of spool space. I read in the Teradata manual that sample 1000 does not need a full table scan.

Below is the query and the explain. Could someone please explain why this query does a full table scan and if there is any method to bypass it to get a sample of records from the table.

Thanks.

explain
lock DB1.Tbl1 for access
select * from DB1.Tbl1
sample 1000;
Explanation
--------------------------------------------------
 
  1)First, we lock DB1.Tbl1 for access.  
  2)Next, we do an all-AMPs RETRIEVE step from DB1.Tbl1 by way of an all-rows scan with no residual conditions into Spool 2, 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 601,197,063 rows. The estimated time for this step is 6 hours and 14 minutes.  
  3)We do an all-AMPs SAMPLING step from Spool 2 (Last Use) by way of an all-rows scan into Spool 1, which is built locally on the AMPs. Samples are specified as number of rows..  
  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.  



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023