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


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html