Oracle中随机抽取N条记录

一、Oracle访问数据的基本方法:

1)、全表扫描(Full table Scan):执行全表扫描,Oracle读表中的所有记录,考查每一行是否满足WHERE条件。Oracle顺序的读分配给该表的每一个数据块,且每个数据块Oracle只读一次.这样全表扫描能够受益于多块读。

2)、采样表扫描(sample table scan):扫描返回表中随机采样数据,这种访问方式需要在FROM语句中包含SAMPLE选项或者SAMPLE BLOCK选项。

二、获取随机结果集的方法:

1)、使用sample获取随机结果集

  1、语法: SAMPLE [ BLOCK ](sample_percent)[ SEED (seed_value) ]

     SAMPLE选项:表示按行采样来执行一个全表扫描,Oracle从表中读取特定百分比的记录,并判断是否满足WHERE子句以返回结果。

     BLOCK: 表示使用随机块例举而不是随机行例举。

     sample_percent:是随机获取一张表中记录的百分比。比如值为10,那就是表中的随机的百分之10的记录。值必须大于等于.000001,小于100。

     SEED:表示从哪条记录返回,类似于预先设定例举结果,因而每次返回的结果都是固定的。该值必须介于0和4294967295之间。

  2、例子(看语法不如看例子)

    --从表 zs_xj_family 中“全表扫描”随机抽取20%的记录,再从中随机查询5条记录

    SELECT   *
      FROM   zs_xj_family SAMPLE (20)
     WHERE   ROWNUM <= 5;

    -- 从表 zs_xj_family 中“采样表扫描”随机抽取10%的记录,再从中随机查询5条记录

    SELECT   *
      FROM   zs_xj_family SAMPLE BLOCK (10)
     WHERE   ROWNUM <= 5;

    --使用seed,返回固定的结果集。

    SELECT   *
      FROM   zs_xj_family SAMPLE (10) SEED (3)
     WHERE   ROWNUM <= 5
    SELECT   *
      FROM   zs_xj_family SAMPLE BLOCK (10) SEED (11)
     WHERE   ROWNUM <= 5;

    注意:

    1.sample只对单表生效,不能用于表连接、远程表、视图

    2.sample会使SQL自动使用CBO

    疑点:

   我在测试sample 的时候发现如果我的表中数据有20条,按照它的sample值我写这样的sql: select * from tablename sample(50)，应该结果集中的数据占总数据的50%才对,但是多次执行之后的结果却是多少数据都有,小于50%、大于50%的都有,这跟 Sample_Percent是一个数字,定义结果集中包含记录占总记录数量的百分比的说明不符,而且用sample block时有时候还能查询出空结果集(select * from tablename sample block(50))

2)使用DBMS_RANDOM包

DBMS_RANDOM有两种主要的使用方法分别是:DBMS_RANDOM.VALUE()和DBMS_RANDOM.RANDOM

    1、获取一个随机数  --(0-10的整数)

    SELECT   TRUNC (DBMS_RANDOM.VALUE (0, 10)) randomNum FROM DUAL; --(0-100的浮点数)
    SELECT   DBMS_RANDOM.VALUE (0, 100) randomNum FROM DUAL;    --(0.几的小数)
    SELECT   DBMS_RANDOM.VALUE () randomNum FROM DUAL;

    2、获取随机记录

    SELECT   *
      FROM   (  SELECT   *
                  FROM   zs_family
                 where is_test=1
              ORDER BY   DBMS_RANDOM.VALUE)
     WHERE   ROWNUM <= 3
    SELECT   *
      FROM   (  SELECT   *
                  FROM   zs_family
                 where is_test=1
              ORDER BY   DBMS_RANDOM.VALUE(1,3))
     WHERE   ROWNUM <= 3
    SELECT   *
      FROM   (  SELECT   *
                  FROM   zs_family
                 WHERE   is_dxx = 1 AND is_test = 1
              ORDER BY   DBMS_RANDOM.random)
     WHERE   ROWNUM <= 3

3)使用内部函数sys_guid()

    SELECT   *
      FROM   (  SELECT   *
                  FROM   zs_family
                 WHERE   is_test = 1
              ORDER BY   sys_guid())
     WHERE   ROWNUM <= 3

    注意:

        在使用sys_guid() 这种方法时,有时会获取到相同的记录,即和前一次查询的结果集是一样的,查找相关资料,有些说是和操作系统有关,在windows平台下正常,获取到的数据是随机的,而在linux等平台下始终是相同不变的数据集,有些说是因为sys_guid()函数本身的问题,即sys_guid()会在查询上生成一个 16字节的全局唯一标识符,这个标识符在绝大部分平台上由一个宿主标识符和进程或进程的线程标识符组成,这就是说,它很可能是随机的,但是并不表示一定是百分之百的这样。 

       所以,为确保在不同的平台每次读取的数据都是随机的,我们大多采用使用sample函数或者 DBMS_RANDOM 包获得随机结果集,其中使用sample函数更常用,因为其查询时缩小了查询范围,在查询大表,且要提取数据不是很不多的情况下,会对查询速度上有明显的提高。

三、其他数据库随机取出n条记录:

1、SqlServer中随机提取数据库记录

select * from 表 order by newid()

2、mysql中随机提取数据库记录

select * from tablename order by rand() limit 10

3、Access中随机提取数据库记录

SELECT top 10 * FROM tablename ORDER BY Rnd(字段名)

 您阅读本篇文章共花了：