 |
 |
Archives of the TeradataForum
Message Posted: Fri, 19 Oct 2001 @ 15:27:16 GMT
| 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:
Explanation
-------------------------------------------------- | |
| | 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.
| |
