如何從結果集中獲得隨機結果
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
