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

-------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   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 id):

---------------------------------------------------

  2 - access("OWNER"=USER@!)

      filter(ORA_HASH(ROWID,0,2211694651,'SYS_SAMPLE',0)<42949673)


统计信息

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         6  consistent gets

         4  physical reads

         0  redo size

       452  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)

         2  rows processed

SQL> SELECT /*+ FULL(T) */ OBJECT_ID FROM T SAMPLE (1) WHERE WNER = USER;

未选定行


执行计划

----------------------------------------------------------

Plan hash value: 2767392432

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   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 id):

---------------------------------------------------

  1 - filter("OWNER"=USER@!)


统计信息

----------------------------------------------------------

         1  recursive calls

         0  db block gets

       517  consistent gets

       430  physical reads

         0  redo size

       275  bytes sent via SQL*Net to client

       374  bytes received via SQL*Net from client

         1  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

         0  rows processed

Oracle文档还提到基于BLOCK的SAMPLE扫描，只对全表扫描和快速索引全扫有效，而其他执行计划无效，不过索引扫描提供了一种类似实现方式：SYS_SAMPLE_BLOCK

SQL> SELECT OBJECT_ID FROM T SAMPLE BLOCK (1);

未选定行


执行计划

----------------------------------------------------------

Plan hash value: 2767392432

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |   496 |  8432 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS SAMPLE| T    |   496 |  8432 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------


统计信息

----------------------------------------------------------

       219  recursive calls

         0  db block gets

        40  consistent gets

         0  physical reads

         0  redo size

       275  bytes sent via SQL*Net to client

       374  bytes received via SQL*Net from client

         1  SQL*Net roundtrips to/from client

         5  sorts (memory)

         0  sorts (disk)

         0  rows processed

SQL> SELECT OBJECT_ID FROM T SAMPLE BLOCK (1) WHERE OBJECT_ID IS NOT NULL;

未选定行


执行计划

----------------------------------------------------------

Plan hash value: 1022620004

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   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 id):

---------------------------------------------------

  1 - filter("OBJECT_ID" IS NOT NULL)


统计信息

----------------------------------------------------------

         1  recursive calls

         0  db block gets

         6  consistent gets

         0  physical reads

         0  redo size

       275  bytes sent via SQL*Net to client

       374  bytes received via SQL*Net from client

         1  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

         0  rows 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

-----------------------------------------------------------------------------

| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   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 id):

---------------------------------------------------

  1 - filter("OBJECT_ID" IS NOT NULL AND

             ORA_HASH(ROWID,0,533460469,'SYS_SAMPLE_BLOCK',0)<429497)


统计信息

----------------------------------------------------------

         1  recursive calls

         0  db block gets

       112  consistent gets

         0  physical reads

         0  redo size

       461  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)

         3  rows processed

事实上SAMPLE和HINT二者并不矛盾，绝大部分情况下，Oracle采用的执行计划会同时满足二者的需求。


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