论oracle随机查询一条记录

      这几天工作上的需要，要从一个比较大的表中随机取出一条记录，oracle 不像MS SQLSERVER那样，直接用Select TOP 1 * From TABLE Order By NewID(),就能高效的随机查出一条记录。经过一翻折腾，用一个有90万条记录的表t_id,只有一个gameid字段，该字段上没有索引，表里就是从100000到999999一连串的数据记录，进行测试：

     方法1.

    采用rownum和dbms_random.value,平均用时5秒，这个效率确实是太低了，对于小表应该还行，大表那就太不适合了。

       declare
       n_id number(6);
       begin
               SELECT gameid into n_id FROM(SELECT gameid FROM t_id T ORDER BY dbms_random.value()) WHERE ROWNUM=1;
               dbms_output.put_line(to_char(n_id));
       end;
       /

    方法2.　

　　 采用oracle sample语法，设置随机样本是1%，结果用时0.01左右，速度是相当快的，但是根据官网的说法，采用sample采集特性可能会产生不准确的结果集，我在测试中是没有碰到不正确的结果。但是有个问题，就是随机的结果分布很不平均，结果几乎都分内存卡在100000-200000的记录中。效率虽好，但并没有达到很好的随机效果，如果对于结果的要求不是很高的，这个方法是相当不错的。

       declare
            n_id number(6);
       begin
             SELECT gameid into n_id FROM t_id  SAMPLE (1)  WHERE ROWNUM = 1;
             dbms_output.put_line(to_char(n_id));
       end;
       /

      方法3.　

      采用minus语法，先随机获取一个在表总记录数范围内的一个随机数，再通过rownum查询两个结果集只相差一条记录，用minus相减留出事先随机数的那条记录，平均用时大概1秒，随机数越小，查询速度越快，当随机数是20000时，用于0.016秒。该方法虽然

能得到很有随机效果，但效率比较不上该方法2. 对效率要求一般的话， 还是可以考虑使用的。

declare
  n_count int:=0;
  n_rand_num int:=0;
  n_id number(6);
begin
  SELECT COUNT(*) INTO n_count FROM  t_id;
  SELECT  trunc(dbms_random.value(1,n_count+1)) INTO n_rand_num FROM DUAL;

  select gameid into n_id from (SELECT gameid FROM t_id T WHERE rownum<n_rand_num
  minus
  SELECT gameid FROM t_id T WHERE rownum<n_rand_num-1);
  dbms_output.put_line(to_char(n_id));
end;
/

还有别的方法，可以参考下http://www.oracle.com.cn/viewthread.php?tid=130433&extra=page%3D1

 

里面讨论了一些方法，其中版主增加一个ROWNUM的字段并加索引，这种情况一个要改变表结构，另一个情况是有些记录是要排除在随机查询以外的，就不好处理了。