Oracle支持SELECT语句只扫描表的一部分记录。

这篇简单介绍SAMPLE用法。

看一个简单的例子：

SQL> CREATE TABLE T AS
  2  SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> SET AUTOT ON
SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
     49611

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   157   (2)| 00:00:02 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 52182 |   157   (2)| 00:00:02 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
         28  recursive calls
          0  db block gets
        759  consistent gets
        684  physical reads
          0  redo size
        410  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

如果指定了SAMPLE语句：

SQL> SELECT COUNT(*) FROM T SAMPLE (5);

  COUNT(*)
----------
      2464

执行计划
----------------------------------------------------------
Plan hash value: 1807638002

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    12 |   157   (2)| 00:00:02 |
|   1 |  SORT AGGREGATE      |      |     1 |    12 |            |          |
|   2 |   TABLE ACCESS SAMPLE| T    |  2481 | 29772 |   157   (2)| 00:00:02 |
-----------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        687  consistent gets
          0  physical reads
          0  redo size
        409  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT COUNT(*) FROM T SAMPLE BLOCK (5);

  COUNT(*)
----------
      3392

执行计划
----------------------------------------------------------
Plan hash value: 1807638002

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    12 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE      |      |     1 |    12 |            |          |
|   2 |   TABLE ACCESS SAMPLE| T    |  2481 | 29772 |     9   (0)| 00:00:01 |
-----------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         53  consistent gets
          0  physical reads
          0  redo size
        409  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SAMPLE的两种语法，一种指定的是记录的SAMPLE，另一种指定的是BLOCK的SAMPLE。对于第一个查询而言，返回的结果是总记录的5%，但是从统计信息上看，Oracle扫描了所有的BLOCK。而指定BLOCK的SAMPLE，Oracle扫描了7%左右的BLOCK，从执行计划上看，Oracle也试图返回2481条记录，但是实际上Oracle返回了3392条记录。