 |
 |
Archives of the TeradataForum
Message Posted: Thu, 21 Mar 2002 @ 17:17:38 GMT
| 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.
| |
| |
