Oracle 数据采样

简介

从Oracle8i开始Oracle提供采样表扫描特性。

Oracle访问数据的基本方法有:

1.全表扫描

2.采样表扫描

3.全表随机读取

全表扫描(Full table Scan)

全表扫描返回表中所有的记录。
执行全表扫描，Oracle读表中的所有记录，考查每一行是否满足WHERE条件。Oracle顺序的读分配给该表的每一个数据块，这样全表扫描能够受益于多块读.
每个数据块Oracle只读一次.

采样表扫描(sample table scan)

采样表扫描返回表中随机采样数据。
这种访问方式需要在FROM语句中包含SAMPLE选项或者SAMPLE BLOCK选项.

全表随机读取n条

采样前n条记录的查询，可以使用dbms_random包实现

案例

SAMPLE选项:
当按行采样来执行一个采样表扫描时，Oracle从表中读取特定百分比的记录，并判断是否满足WHERE子句以返回结果。

SAMPLE BLOCK选项:
使用此选项时，Oracle读取特定百分比的BLOCK，考查结果集是否满足WHERE条件以返回满足条件的纪录.

Sample_Percent:
Sample_Percent是一个数字，定义结果集中包含记录占总记录数量的百分比。
Sample值应该在[0.000001,99.999999]之间。

1.使用SAMPLE选项

SQL> select * from emp sample(20);

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7566 JONES      MANAGER    7839 1981-4-2      2866.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     2866.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2866.00               30
 7839 KING       PRESIDENT       1981-11-17    2866.00               10

2、使用SAMPLE BLOCK选项

SQL> select * from emp sample block(35);

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17    3066.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     2866.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     2866.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2866.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     2866.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2866.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2866.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     2866.00               20
 7839 KING       PRESIDENT       1981-11-17    2866.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      2866.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     2866.00               20
 7900 JAMES      CLERK      7698 1981-12-3     2866.00               30
 7902 FORD       ANALYST    7566 1981-12-3     2866.00               20
 7934 MILLER     CLERK      7782 1982-1-23     2866.00               10

14 rows selected

3、采样前n条记录的查询

也可以使用dbms_random包实现

SQL> select * from (
  　　　　select * from emp order by dbms_random.value)
  　　where rownum < 4;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING       PRESIDENT       1981-11-17    2866.00               10
 7902 FORD       ANALYST    7566 1981-12-3     2866.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     2866.00   1400.00     30

SQL> select * from (
  2  select * from emp order by dbms_random.random)
  3  where rownum < 5;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7934 MILLER     CLERK      7782 1982-1-23     2866.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      2866.00      0.00     30
 7369 SMITH      CLERK      7902 1980-12-17    3066.00               20
 7876 ADAMS      CLERK      7788 1987-5-23     2866.00               20

主要注意以下几点:

1.sample只对单表生效，不能用于表连接和远程表
2.sample会使SQL自动使用CBO

（补充：随机连续10条记录）

SELECT * FROM
(   SELECT *   FROM TABLE
WHERE ROWNUM <=
        (SELECT MAX(FLOOR(DBMS_RANDOM.VALUE(0,(SELECT COUNT(*) FROM TABLE)))) FROM TABLE WHERE ROWNUM <= 10)
        ORDER BY ROWNUM DESC)
        WHERE Rownum<=10;