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
