Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 19 Oct 2001 @ 15:27:16 GMT

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

Subj:   Re: Insert select with sample
From:   Paul Johnson

Well, I have absolutely no idea ("no clue" to those in the colonies) as to which sampling algorithm it uses. I was assured by an NCR person recently that "sample" scans the entire table. My own experience seems to suggest otherwise:

The SQL "explain sel * from cis.customer sample 10" returns:

  1)First, we lock PROD_PERSON.CUSTOMER for access With NoWait option.  
  2)Next, we do an all-AMPs SAMPLING step from PROD_PERSON.CUSTOMER by way of an all-rows scan with no residual conditions into Spool 1, which is built locally on the AMPs. Samples are specified as number of rows..  
  3)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.  

It takes < 10 seconds to run whereas a count(*) on the same 8m row table takes around 30 seconds. Bringing the entire input table down into spool would appear to be a bit excessive and misses the point of using sample entirely.

Paul Johnson.

  <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: 28 Jun 2020