oracle 随机样本,oracle 随机取部分记录

1、建议测试表及数据

--创建测试表

create table t1(id integer,nm varchar2(10),val integer);

--建立测试数据

insert into t1(id,nm,val) values(1,'test1',10);

insert into t1(id,nm,val) values(2,'test2',100);

insert into t1(id,nm,val) values(3,'test3',1000);

insert into t1(id,nm,val) values(4,'test4',10000);

insert into t1(id,nm,val) values(5,'test5',20);

insert into t1(id,nm,val) values(6,'test6',30);

insert into t1(id,nm,val) values(7,'test7',30);

insert into t1(id,nm,val) values(8,'test8',30);

insert into t1(id,nm,val) values(9,'test9',30);

insert into t1(id,nm,val) values(10,'test10',30);

insert into t1(id,nm,val) values(11,'test11',30);

insert into t1(id,nm,val) values(12,'test12',30);

2、Sample [BLOCK](sample_percent)

取随机数(性能最佳)，但是不能取固定的记录数。

Oracle从表中随机选择记录样本，这样比从整个表中选择更高效。

不加BLOCK选项时是随机取记录行数，如果加上BLOCK选项时表示随机取数据块。

sample_percent是指定总记录行或数据块为数据样本的百分比数值，这个值只能在0.000001到100之间，且不能等于100限制。

只能在单表查询的SQL中指定sample选项，不支持有连接的查询。但是，你可以使用CREATE TABLE ... AS SELECT查询的语法创建临时的中间样本表，然后再采用新建的临时样本表重新编写查询SQL。当你指定用sample时，不支持基于规则(rule)的优化法则，ORACLE自动使用基本成本(cost)的优化法则。

按记录行的百分比

SQL> SELECT *  from t1 sample(10);

未选定行

SQL> SELECT *  from t1 sample(10);

ID NM                          VAL

---------- -------------------- ----------

3 test3                      1000

4 test4                     10000

6 test6                        30

7 test7                        30

SQL> SELECT *  from t1 sample(10);

ID NM                          VAL

---------- -------------------- ----------

5 test5                        20

12 test12                       30

按block的百分比

SQL> SELECT *  from t1 sample block(30);

未选定行

SQL> SELECT *  from t1 sample block(30);

ID NM                          VAL

---------- -------------------- ----------

1 test1                       233

3 test3                      1000

4 test4                     10000

5 test5                        20

6 test6                        30

7 test7                        30

8 test8                        30

9 test9                        30

10 test10                       30

11 test11                       30

12 test12                       30

已选择11行。

此表的所有记录在同一个block里。故上面的block选项查询时，要么所有的记录，要么没有记录。

从这里可以查询表t1的所有记录在同一个block上

SQL> col segment_name format a20

SQL>SQL> SELECT de.segment_name, de.EXTENT_ID, de.FILE_ID, de.BLOCK_ID, de.BLOCKS

From dba_extents de

where de.segment_name = 'T1';

SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS

-------------------- ---------- ---------- ---------- ----------

T1                            0          2        657          8

3、dbms_utility.get_hash_value

取随机数(效率稍差)

SQL> select * from (

select a.*

from t1 a

order by dbms_utility.get_hash_value(to_char(dbms_utility.get_time) || a.id,1,987)

) where rownum  <=5;

ID NM                          VAL

---------- -------------------- ----------

10 test10                       30

5 test5                        20

4 test4                     10000

3 test3                      1000

12 test12                       30

SQL> /

ID NM                          VAL

---------- -------------------- ----------

1 test1                       233

4 test4                     10000

8 test8                        30

9 test9                        30

5 test5                        20

4、dbms_random.value

取4条随机记录(效率最差)

SQL> select * from (select * from t1 order by trunc(dbms_random.value(0,1000)))

where rownum<=5;

ID NM                          VAL

---------- -------------------- ----------

8 test8                        30

7 test7                        30

1 test1                       233

6 test6                        30

10 test10                       30

SQL> /

ID NM                          VAL

---------- -------------------- ----------

5 test5                        20

7 test7                        30

3 test3                      1000

12 test12                       30

9 test9                        30