如何使用数百万行的表更快地在oracle中选择随机行 [英] how to make selecting random rows in oracle faster with table with millions of rows

问题描述

有没有一种方法可以使Oracle在具有一百万行的表中更快地选择随机行.我尝试使用sample(x)和dbms_random.value及其花费很长时间运行.

Is there a way to make selecting random rows faster in oracle with a table that has million of rows. I tried to use sample(x) and dbms_random.value and its taking a long time to run.

谢谢！

推荐答案

使用适当的sample(x)值是最快的方法.它是块内的块随机和行随机，因此，如果您只想要一个随机行:

Using appropriate values of sample(x) is the fastest way you can. It's block-random and row-random within blocks, so if you only want one random row:

select dbms_rowid.rowid_relative_fno(rowid) as fileno,
       dbms_rowid.rowid_block_number(rowid) as blockno,
       dbms_rowid.rowid_row_number(rowid) as offset
  from (select rowid from [my_big_table] sample (.01))
 where rownum = 1

我正在使用分区表，即使抓取多行，我也获得了很好的随机性:

I'm using a subpartitioned table, and I'm getting pretty good randomness even grabbing multiple rows:

select dbms_rowid.rowid_relative_fno(rowid) as fileno,
       dbms_rowid.rowid_block_number(rowid) as blockno,
       dbms_rowid.rowid_row_number(rowid) as offset
  from (select rowid from [my_big_table] sample (.01))
 where rownum <= 5

    FILENO    BLOCKNO     OFFSET
---------- ---------- ----------
       152    2454936         11
       152    2463140         32
       152    2335208          2
       152    2429207         23
       152    2746125         28

我怀疑您可能应该调整SAMPLE子句，以对要获取的内容使用适当的样本大小.

I suspect you should probably tune your SAMPLE clause to use an appropriate sample size for what you're fetching.

这篇关于如何使用数百万行的表更快地在oracle中选择随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！