Teradata 之top n與sample n

Teradata取n條樣本資料的方法有兩種：

select top x * from table;
select * from table sample n;

那麼二者有什麼區別呢？說明如下：

TOP N
如果有Order By關鍵字首先要對資料庫的資料進行排序，然後獲取N條資料或者抽樣比率為N；
如果沒有Order By關鍵字，要做一次STAT FUNCTION的全AMP收集，然後選擇某一個或者幾個AMP 來抽取資料。

Sample N
首先要對資料庫的資料進行全表掃描，然後獲取N資料；
採用的是Sampling的形式。

[@more@]

效能比較：
當資料量比較小的時候，TOP N的速度要比Sample的速度快；
當資料量比較大的時候，Sample N的速度要比TOP的速度快。

測試：

Explain select top 10 * from PD_PORTAL.TOPIC_COMP_DETAIL

1) First, we lock a distinct PD_PORTAL."pseudo table" for read on a
RowHash to prevent global deadlock for PD_PORTAL.TOPIC_COMP_DETAIL.
2) Next, we lock PD_PORTAL.TOPIC_COMP_DETAIL for read.
3) We do an all-AMPs STAT FUNCTION step from
PD_PORTAL.TOPIC_COMP_DETAIL by way of an all-rows scan with no
residual conditions into Spool 5, which is redistributed by hash
code to all 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 10 rows. One AMP is randomly selected to
retrieve 10 rows. If this step retrieves less than 10 rows, then
execute step 4. The size is estimated with low confidence to be
10 rows (27,460 bytes).
4) We do an all-AMPs STAT FUNCTION step from
PD_PORTAL.TOPIC_COMP_DETAIL by way of an all-rows scan with no
residual conditions into Spool 5 (Last Use), which is
redistributed by hash code to all 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 10 rows. The size is
estimated with low confidence to be 10 rows (27,460 bytes).
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.

Explain select * from PD_PORTAL.TOPIC_COMP_DETAIL sample 10

1) First, we lock a distinct PD_PORTAL."pseudo table" for read on a
RowHash to prevent global deadlock for PD_PORTAL.TOPIC_COMP_DETAIL.
2) Next, we lock PD_PORTAL.TOPIC_COMP_DETAIL for read.
3) We do an all-AMPs SAMPLING step from PD_PORTAL.TOPIC_COMP_DETAIL
by way of an all-rows scan with no residual conditions into Spool
1 (group_amps), which is built locally on the AMPs. Samples are
specified as a 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.