SAMPLE语句（一）

最新推荐文章于 2021-01-20 18:27:22 发布

ckawt40802

最新推荐文章于 2021-01-20 18:27:22 发布

阅读量800

点赞数

文章标签：数据库

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条记录。