如何从结果集中获得随机结果
2024-07-21 02:06:33
供稿：网友
注册会员，创建你的web开发资料库，
从oracle8i开始oracle提供采样表扫描特性。
oracle访问数据的基本方法有:
1.全表扫描
2.采样表扫描
全表扫描(full table scan)
全表扫描返回表中所有的记录。
执行全表扫描，oracle读表中的所有记录，考查每一行是否满足where条件。oracle顺序的读分配给该表的每一个数据块，这样全表扫描能够受益于多块读.
每个数据块oracle只读一次.
采样表扫描(sample table scan)
采样表扫描返回表中随机采样数据。
这种访问方式需要在from语句中包含sample选项或者sample block选项.
sample选项:
当按行采样来执行一个采样表扫描时，oracle从表中读取特定百分比的记录，并判断是否满足where子句以返回结果。
sample block选项:
使用此选项时，oracle读取特定百分比的block，考查结果集是否满足where条件以返回满足条件的纪录.
sample_percent:
sample_percent是一个数字，定义结果集中包含记录占总记录数量的百分比。
sample值应该在[0.000001,99.999999]之间。
1.使用sample选项
sql> select * from employee sample(30); empno ename job mgr hiredate sal comm deptno---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 smith clerk 7902 17-dec-80 800 20 7788 scott analyst 7566 19-apr-87 3000 20 7839 king president 17-nov-81 5000 10execution plan---------------------------------------------------------- 0 select statement optimizer=choose (cost=2 card=25 bytes=2175) 1 0 table access (sample) of 'employee' (cost=2 card=25 bytes=2175)statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 880 bytes sent via sql*net to client 503 bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processedsql> select * from employee sample(20); empno ename job mgr hiredate sal comm deptno---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7654 martin salesman 7698 28-sep-81 1250 1400 30 7844 turner salesman 7698 08-sep-81 1500 0 30execution plan---------------------------------------------------------- 0 select statement optimizer=choose (cost=2 card=16 bytes=1392) 1 0 table access (sample) of 'employee' (cost=2 card=16 bytes=1392)statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 839 bytes sent via sql*net to client 503 bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed
2.使用sample block选项
sql> select * from employee sample block (50); empno ename job mgr hiredate sal comm deptno---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 smith clerk 7902 17-dec-80 800 20 7499 allen salesman 7698 20-feb-81 1600 300 30 7521 ward salesman 7698 22-feb-81 1250 500 30 7566 jones manager 7839 02-apr-81 2975 20 7654 martin salesman 7698 28-sep-81 1250 1400 30 7698 blake manager 7839 01-may-81 2850 30 7782 clark manager 7839 09-jun-81 2450 10 7788 scott analyst 7566 19-apr-87 3000 20 7839 king president 17-nov-81 5000 10 7844 turner salesman 7698 08-sep-81 1500 0 3010 rows selected.execution plan---------------------------------------------------------- 0 select statement optimizer=choose (cost=2 card=41 bytes=3567) 1 0 table access (sample) of 'employee' (cost=2 card=41 bytes=3567)statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1162 bytes sent via sql*net to client 503 bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processedsql>
3.采样前n条记录的查询
也可以使用dbms_random包实现
sql> select * from ( 2 select * from employee 3 order by dbms_random.value ) 4 where rownum <= 4; empno ename job mgr hiredate sal comm deptno---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7654 martin salesman 7698 28-sep-81 1250 1400 30 7839 king president 17-nov-81 5000 10 7369 smith clerk 7902 17-dec-80 800 20 7788 scott analyst 7566 19-apr-87 3000 20execution plan---------------------------------------------------------- 0 select statement optimizer=choose 1 0 count (stopkey) 2 1 view 3 2 sort (order by stopkey) 4 3 table access (full) of 'employee'statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 927 bytes sent via sql*net to client 503 bytes received via sql*net from client 2 sql*net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 4 rows processed
对比一下sample选项
sql> select * from employee sample (40); empno ename job mgr hiredate sal comm deptno---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 allen salesman 7698 20-feb-81 1600 300 30 7521 ward salesman 7698 22-feb-81 1250 500 30 7698 blake manager 7839 01-may-81 2850 30 7839 king president 17-nov-81 5000 10 7844 turner salesman 7698 08-sep-81 1500 0 30execution plan---------------------------------------------------------- 0 select statement optimizer=choose (cost=2 card=33 bytes=2871) 1 0 table access (sample) of 'employee' (cost=2 card=33 bytes=2871)statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 961 bytes sent via sql*net to client 503 bytes received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processedsql>
主要注意以下几点:
1.sample只对单表生效，不能用于表连接和远程表
2.sample会使sql自动使用cbo
本文作者:
eygle,oracle技术关注者,来自中国最大的oracle技术论坛itpub.
www.eygle.com是作者的个人站点.你可通过[email protected]来联系作者.欢迎技术探讨交流以及链接交换.
原文出处:
http://www.eygle.com/sql/how.to.get.random.output.of.record.set.htm
