问题描述
有没有一种方法可以使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中选择随机行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!