SAMPLE语句（三）
Oracle支持SELECT语句只扫描表的一部分记录。
这篇简单介绍INDEX扫描的SAMPLE语句。
SAMPLE语句（一）：http://yangtingkun.itpub.net/post/468/506602
SAMPLE语句（二）：http://yangtingkun.itpub.net/post/468/506627
接着上一篇的例子，在表上建立两个索引：
SQL> CREATE INDEX IND_T_OWNER ON T (OWNER);
索引已创建。
SQL> CREATE INDEX IND_T_ID ON T (OBJECT_ID);
索引已创建。
对于全表扫描使用SAMPLE语句，从执行计划中可以直接看出来：
SQL> SELECT OWNER, OBJECT_ID FROM T SAMPLE (0.01);
OWNER OBJECT_ID
------------------------------ ----------
SYS 4479
PUBLIC 13929
PUBLIC 30919
执行计划
----------------------------------------------------------
Plan hash value: 2767392432
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 115 | 157 (2)| 00:00:02 |
| 1 | TABLE ACCESS SAMPLE| T | 5 | 115 | 157 (2)| 00:00:02 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
73 consistent gets
0 physical reads
0 redo size
527 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，不过直接从执行计划中看不出来：
SQL> SELECT OWNER, OBJECT_ID FROM T SAMPLE (1) WHERE WNER = USER;
OWNER OBJECT_ID
------------------------------ ----------
YANGTK 61217
YANGTK 83222
YANGTK 90979
执行计划
----------------------------------------------------------
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,4124907050,'SYS_SAMPLE',0)<42949673)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
524 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的身影：
filter(ORA_HASH(ROWID,0,4124907050,'SYS_SAMPLE',0)<42949673)
对于通过索引扫描获取的记录，Oracle采用调用HASH函数过滤ROWID的方式来达到SAMPLE的目的。
SQL> SELECT OWNER, OBJECT_ID FROM T SAMPLE (1) WHERE WNER = USER;
OWNER OBJECT_ID
------------------------------ ----------
YANGTK 117836
执行计划
----------------------------------------------------------
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,1958539417,'SYS_SAMPLE',0)<42949673)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
473 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
对于不指定SEED语句的SAMPLE，每次ORA_HASH函数的种子都是变化的，因此返回的结果每次都不相同。
当指定了SEED后，则种子是固定的，每次返回的结果是确定的：
SQL> SELECT OWNER, OBJECT_ID FROM T SAMPLE (1) SEED (2) WHERE WNER = USER;
OWNER OBJECT_ID
------------------------------ ----------
YANGTK 83220
执行计划
----------------------------------------------------------
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,1,3654570532,'SYS_SAMPLE',0)<42949673)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
473 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 OWNER, OBJECT_ID FROM T SAMPLE (1) SEED (2) WHERE WNER = USER;
OWNER OBJECT_ID
------------------------------ ----------
YANGTK 83220
执行计划
----------------------------------------------------------
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,1,3654570532,'SYS_SAMPLE',0)<42949673)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
473 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
用户给出的SEED和Oracle调用ORA_HASH时指定的种子值并不一致，Oracle应该在调用ORA_HASH之前进行了处理。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-675682/ ，如需转载，请注明出处，否则将追究法律责任。
