How to obtain random results from a result set

Random
Oracle provides sample table scanning features starting from oracle8i.

Oracle's basic methods of accessing data are:
1. Full table Scan
2. Sampling table Scanning

Full table scan (Scan)
The full table scan returns all the records in the table.
Perform a full table scan, all records in the Oracle read table, and examine whether each row satisfies the where condition. Oracle sequential reads are allocated to each block of data in the table, so that full table scans can benefit from multiple reads.
Oracle is read only once per block of data.

Sample table scan (sample table Scan)
The sample table scan returns random sampled data from the table.
This access requires the sample option or sample block option to be included in the FROM statement.

Sample option:
When a sample table scan is performed by row sampling, Oracle reads a specific percentage of records from the table and determines whether the WHERE clause is met to return the result.

SAMPLE block option:
When this option is used, Oracle reads a specific percentage of block to see if the result set satisfies the where condition to return the record that satisfies the condition.

Sample_percent:
Sample_percent is a number that defines the percentage of the total number of records that the result set contains.
The sample value should be between [0.000001,99.999999].

1. Use the sample option

Sql> SELECT * FROM Employee SAMPLE (30); EMPNO ename JOB MGR hiredate SAL COMM DEPTNO----------------------------------------------------------------------- -------7369 SMITH Clerk 7902 17-dec-80 7788 SCOTT ANALYST 7566 19-apr-87 3000 7839 KING PRESIDENT 17-nov-81 500 0 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 Sort s (memory) 0 sorts (disk) 3 rows processedsql> select * FROM Employee SAMPLE (20); EMPNO ename JOB MGR hiredate SAL COMM DEPTNO----------------------------------------------------------------------- -------7654 MARTIN SalesmA 7698 28-sep-81 1250 1400 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 ' E Mployee ' (cost=2 card=16 bytes=1392) Statistics----------------------------------------------------------0 Recursive calls 0 DB gets 5 consistent gets 0 physical reads 0 Redo size 839 bytes sent via sql*net to client 503 by TES received via sql*net from client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed

2. Use sample block option




Sql> SELECT * FROM employee SAMPLE block (50); EMPNO ename JOB MGR hiredate SAL COMM DEPTNO----------------------------------------------------------------------- -------7369 SMITH Clerk 7902 17-dec-80 7499 ALLEN salesman 7698 20-feb-81 1600 7521 WARD salesman 7698 22-f EB-81 1250 7566 JONES MANAGER 7839 02-apr-81 2975 7654 MARTIN salesman 7698 28-sep-81 1250 1400 7698 BLAKE MA Nager 7839 01-may-81 2850 7782 CLARK MANAGER 7839 09-jun-81 2450 7788 SCOTT ANALYST 7566 19-apr-87 3000 KING PRESIDENT 17-nov-81 5000 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 b Ytes 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 SOR TS (disk) rows processedsql>

3. Sampling the first n records of the query

You can also use the Dbms_random package to implement



Sql> SELECT * FROM (2 select * to 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 7839 KING PRESIDENT 17-nov-81 5000 a 7369 SMITH clerk 7902 17-de C-80 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) ' EMPLOYEE ' Statistics-------------------------------------- --------------------0 Recursive calls 0 DB block gets 3 consistent gets 0 physical reads 0 Redo size 927 bytes sent via S Ql*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

Compare sample Options



Sql> SELECT * FROM employee SAMPLE (40); EMPNO ename JOB MGR hiredate SAL COMM DEPTNO----------------------------------------------------------------------- -------7499 ALLEN salesman 7698 20-feb-81 1600 7521 WARD salesman 7698 22-feb-81 1250 7698 MANAGER 78 01-may-81 2850 7839 KING PRESIDENT 17-nov-81 5000 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 via received from Client 2 sql*net sql*net Ndtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processedsql>

Major attention to the following points:

1.sample only takes effect on a single table and cannot be used for table joins and remote tables
2.sample will make SQL automatically use CBO





The author of this article:
Eygle,oracle technology concern, from China's largest Oracle technology forum Itpub.
Www.eygle.com is the author's personal site. You can contact the author by Guoqiang.Gai@gmail.com. Welcome technical discussions and exchange of links.

Original source:

Http://www.eygle.com/sql/How.To.Get.Random.Output.Of.Record.Set.htm