select*from (select*from largetable orderby dbms_random.random) where rownum <=20000;
【2】方法二:通过dbms_random.value
select*from (select*from largetable orderby dbms_random.value) where rownum <=20000;
【3】方法三:通过采样表扫描
select*from (select*from largetable sample(10)) where rownum <=20000;
下面我们通过实践来比较这3种方法的效率,首先我们创建一个包含有10W条记录的表用于实验:
createtable LARGETABLE ( ID NUMBERnotnullprimarykey, BIRTHDAY DATE notnull )
接下来我们插入10W条数据
createorreplaceprocedure random_insert as i number; startDate date := sysdate; begin for i in1 .. 100000 loop insertinto largetable values (i, startDate +1); end loop commit; end;
http://www.blogjava.net/pengpenglin/archive/2009/03/19/206796.html【1】方法一:通过dbms_random.random select * from (select * from largetable order by dbms_random.random) where rownum 20000;【2】方