oracle最快访问行,如何使用数百万行的表格更快地选择oracle中的随机行

使用适当的sample(x)值是您可以最快的方式.它在块内是块随机的和行随机的,所以如果你只想要一个随机行：

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

我正在使用一个子分区表,即使抓取多行,我也获得了非常好的随机性：

select dbms_rowid.rowid_relative_fno(rowid) as fileno,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子句,使用适当的样本大小来取得你想要的东西.