首先第一個(gè)是隨機(jī)抽取6個(gè)

    select * from  (select * from tablename order by order by dbms_random.value) where  rownum＜7

    這個(gè)方法的原理我認(rèn)為應(yīng)該是把表中的數(shù)據(jù)全部查詢出來按照隨機(jī)數(shù)進(jìn)行排列后在從查詢出來的數(shù)據(jù)中查詢中6條記錄，這個(gè)方法我在使用的過程中發(fā)現(xiàn)，如果記錄一多的話查詢的速度有一點(diǎn)點(diǎn)的慢

第二個(gè)是利用oracle的sample()或sample block方法

選擇10%的記錄
select * from t1 sample(10)
選擇0.1%的記錄
select * from t1 sample(0.1)

根據(jù)數(shù)據(jù)塊選擇1%的記錄
select * from t1 sample block(1)

使用數(shù)據(jù)塊選擇與使用記錄行選擇的區(qū)別：使用數(shù)據(jù)塊選擇表示樣本的采集是基于數(shù)據(jù)塊采集的，也就是說樣本如果一個(gè)數(shù)據(jù)塊被采集為樣本，則數(shù)據(jù)塊里的記錄全部都是樣本

樣本統(tǒng)計(jì)是基于統(tǒng)計(jì)學(xué)采集的，是有概率問題，不一定完全準(zhǔn)確，如你要取50%的記錄，但實(shí)際可能返回給你49%的記錄集，也可能返回給你51%的記錄集

例如

如果表T1有數(shù)據(jù)塊B1,B2
B1有記錄R1,R2,R3,R4,R5
B2有記錄R6,R7,R8,R9,R10

如果使用如下SQL選擇50%的數(shù)據(jù)
select * from t1 sample block(50)

則返回的結(jié)果可能是數(shù)據(jù)塊B1的記錄
R1,R2,R3,R4,R5
也可能是數(shù)據(jù)塊B2的記錄
R6,R7,R8,R9,R10
也可能不返回記錄集

如果使用如下SQL選擇50%的數(shù)據(jù)
select * from t1 sample (50)

則返回的結(jié)果可能是
R2,R3,R5,R8,R9
也可能是如下的樣子
R1,R3,R4,R8

應(yīng)用示例：
隨機(jī)從表中取中1條記錄，選取記錄的概率是1%
select * from t1 sample(1) where rownum=1

隨機(jī)從表中取中10條記錄，選取記錄的概率是0.1%
select * from t1 sample(0.1) where rownum<=10

注：當(dāng)選取的概率越低，訪問表的記錄數(shù)將越多

ORACLE參考手冊中的相關(guān)說明：

sample_clause
The sample_clause lets you instruct Oracle to select from a random sample of rows from the table, rather than from the entire table.

BLOCK
BLOCK instructs Oracle to perform random block sampling instead of random row sampling.

sample_percent
sample_percent is a number specifying the percentage of the total row or block count to be included in the sample. The value must be in the range .000001 to (but not including) 100.

Restrictions on Sampling During Queries
You can specify SAMPLE only in a query that selects from a single table. Joins are not supported. However, you can achieve the same results by using a CREATE TABLE ... AS SELECT query to materialize a sample of an underlying table and then rewrite the original query to refer to the newly created table sample. If you wish, you can write additional queries to materialize samples for other tables.

When you specify SAMPLE, Oracle automatically uses cost-based optimization. Rule-based optimization is not supported with this clause.

--------------------------------------------------------------------------------
Caution:
The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results.

--------------------------------------------------------------------------------
 
譯:
Sample選項(xiàng)
使用sample選項(xiàng)的意思是指定Oracle從表中隨機(jī)選擇記錄樣本，這樣比從整個(gè)表中選擇更高效.

block選項(xiàng)
加上 BLOCK選項(xiàng)時(shí)表示隨機(jī)取數(shù)據(jù)塊，而不是隨機(jī)取記錄行.

sample_percent選項(xiàng)
sample_percent是指定總記錄行或數(shù)據(jù)塊為數(shù)據(jù)樣本的百分比數(shù)值，這個(gè)值只能在0.000001到100之間，且不能等于100

限制
只能在單表查詢的SQL中指定sample選項(xiàng)，不支持有連接的查詢。但是，你可以使用CREATE TABLE ... AS SELECT查詢的語法完成同樣的效果，然后再采用新建的樣本表重新編寫查詢SQL。

當(dāng)你指定用sample時(shí)，不支持基于規(guī)則(rule)的優(yōu)化法則，ORACLE自動(dòng)使用基本成本(cost)的優(yōu)化法則