Oracle SAMPLE语句（四）
Oracle支持SELECT语句只扫描表的一部分记录。这篇简单介绍SAMPLE扫描和HINT的关系。
Oracle的文档上描述，当不包含SAMPLE语句的时候，可以使用HINT来指定执行计划，实际上即使包含SAMPLE语句，HINT也是生效的。
SQL> SELECT OBJECT_ID FROM T SAMPLE (1) WHERE WNER = USER;
OBJECT_ID
----------
60607
70958
执行计划
----------------------------------------------------------
Plan hash value: 3630032853
-------------------------------------------------------------------------------------------
|> -------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 506 | 6 (0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| T | 22 | 506 | 6 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN | IND_T_OWNER | 22 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
2 - access("OWNER"=USER@!)
filter(ORA_HASH(ROWID,0,2211694651,'SYS_SAMPLE',0) 452bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
2rows processed
SQL> SELECT /*+ FULL(T) */ OBJECT_ID FROM T SAMPLE (1) WHERE WNER = USER;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2767392432
----------------------------------------------------------------------------
|> ----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 506 | 158 (2)| 00:00:02 |
|*1 |TABLE ACCESS SAMPLE| T | 22 | 506 | 158 (2)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
1 - filter("OWNER"=USER@!)
统计信息
----------------------------------------------------------
1recursive calls
0db block gets
517consistent gets
430physical reads
0redo> 275bytes sent via SQL*Net to client
374bytes received via SQL*Net from client
1SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
0rows processed
Oracle文档还提到基于BLOCK的SAMPLE扫描，只对全表扫描和快速索引全扫有效，而其他执行计划无效，不过索引扫描提供了一种类似实现方式：SYS_SAMPLE_BLOCK
SQL> SELECT OBJECT_ID FROM T SAMPLE BLOCK (1);
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2767392432
----------------------------------------------------------------------------
|> ----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 496 |8432 | 3 (0)| 00:00:01 |
| 1 |TABLE ACCESS SAMPLE| T | 496 |8432 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
219recursive calls
0db block gets
40consistent gets
0physical reads
0redo> 275bytes sent via SQL*Net to client
374bytes received via SQL*Net from client
1SQL*Net roundtrips to/from client
5sorts (memory)
0sorts (disk)
0rows processed
SQL> SELECT OBJECT_ID FROM T SAMPLE BLOCK (1) WHERE OBJECT_ID IS NOT NULL;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1022620004
----------------------------------------------------------------------------------------
|> ----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 496 |8432 | 2 (0)| 00:00:01 |
|*1 |INDEX SAMPLE FAST FULL SCAN| IND_T_ID | 496 |8432 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
1 - filter("OBJECT_ID" IS NOT NULL)
统计信息
----------------------------------------------------------
1recursive calls
0db block gets
6consistent gets
0physical reads
0redo> 275bytes sent via SQL*Net to client
374bytes received via SQL*Net from client
1SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
0rows processed
SQL> SELECT /*+ INDEX(T) */ OBJECT_ID FROM T SAMPLE BLOCK (0.01) WHERE OBJECT_ID IS NOT NULL;
OBJECT_ID
----------
627
35358
44369
执行计划
----------------------------------------------------------
Plan hash value: 1376157901
-----------------------------------------------------------------------------
|> -----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 85 | 113 (2)| 00:00:02 |
|*1 |INDEX FULL SCAN | IND_T_ID | 5 | 85 | 113 (2)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
1 - filter("OBJECT_ID" IS NOT NULL AND
ORA_HASH(ROWID,0,533460469,'SYS_SAMPLE_BLOCK',0) 461bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
3rows processed
事实上SAMPLE和HINT二者并不矛盾，绝大部分情况下，Oracle采用的执行计划会同时满足二者的需求。
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
页:
[1]