如何在2万条纪录里随机取200条纪录？

最新推荐文章于 2021-04-10 21:20:00 发布

「已注销」

最新推荐文章于 2021-04-10 21:20:00 发布

阅读量1k

点赞数

分类专栏： ORACLE数据库

ORACLE数据库专栏收录该内容

15 篇文章

订阅专栏

本文通过对比SQL查询方法，展示了使用样本查询和随机排序在性能上的差异，并提供了详细的执行计划和统计数据，揭示了SQL查询优化策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

 
<table cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td id="postmessage_1459845">试一试这条语句，select * from (select * from test sample(1)) where rownum<201 ;
可以比较和jianfs方法，如下：
SQL> select count(*) from test ;COUNT(*)
----------
20000

1 row selected.

SQL> analyze table test compute statistics ;

Table analyzed.

SQL> set autotrace traceonly
SQL> select * from (select * from test order by dbms_random.random()) where rownum<201 ;

200 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=<span style="color: red;">1850 [/COLOR] Card=20000 Byte
s=2560000)</span>

1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=1850 Card=20000 Bytes=2560000)
3 2 SORT (ORDER BY STOPKEY) (Cost=1850 Card=20000 Bytes=17
00000)

4 3 TABLE ACCESS (FULL) OF 'TEST' (Cost=230 Card=20000 B
ytes=1700000)

Statistics
----------------------------------------------------------
<span style="color: red;">483[/COLOR] recursive calls
<span style="color: red;">82[/COLOR] db block gets
1657 consistent gets
503 physical reads
<span style="color: red;">9068[/COLOR] redo size
28898 bytes sent via SQL*Net to client
1958 bytes received via SQL*Net from client
16 SQL*Net roundtrips to/from client
3 sorts (memory)
1 sorts (disk)
200 rows processed</span></span></span>

SQL> select * from (select * from test sample(1)) where rownum<201 ;

200 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=<span style="color: red;">230[/COLOR] Card=200 Bytes=1
7000)</span>

1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (SAMPLE) OF 'TEST' (Cost=230 Card=200 Bytes
=17000)

Statistics
----------------------------------------------------------
<span style="color: red;">3 [/COLOR] recursive calls
<span style="color: red;">8 [/COLOR] db block gets
<span style="color: red;">1507 [/COLOR] consistent gets
0 physical reads
<span style="color: red;">0 [/COLOR] redo size
26488 bytes sent via SQL*Net to client
1937 bytes received via SQL*Net from client
16 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
200 rows processed</span></span></span></span>

 

 </td>
</tr>
</tbody>
</table>
sample 不是分析函数，是select语句的一个子句括号里的1表示取样1％。