Oracle SAMPLE语句（三）
Oracle支持SELECT语句只扫描表的一部分记录。这篇简单介绍INDEX扫描的SAMPLE语句。
接着上一篇的例子，在表上建立两个索引：
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
----------------------------------------------------------------------------
|> ----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 115 | 157 (2)| 00:00:02 |
| 1 |TABLE ACCESS SAMPLE| T | 5 | 115 | 157 (2)| 00:00:02 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1recursive calls
0db block gets
73consistent gets
0physical reads
0redo> 527bytes 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，不过直接从执行计划中看不出来：
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
-------------------------------------------------------------------------------------------
|> -------------------------------------------------------------------------------------------
| 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,4124907050,'SYS_SAMPLE',0) 524bytes 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的身影：
filter(ORA_HASH(ROWID,0,4124907050,'SYS_SAMPLE',0) SELECT OWNER, OBJECT_ID FROM T SAMPLE (1) WHERE WNER = USER;
OWNER OBJECT_ID
------------------------------ ----------
YANGTK 117836
执行计划
----------------------------------------------------------
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,1958539417,'SYS_SAMPLE',0) 473bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows 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
-------------------------------------------------------------------------------------------
|> -------------------------------------------------------------------------------------------
| 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,1,3654570532,'SYS_SAMPLE',0) 473bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
SQL> SELECT OWNER, OBJECT_ID FROM T SAMPLE (1) SEED (2) WHERE WNER = USER;
OWNER OBJECT_ID
------------------------------ ----------
YANGTK 83220
执行计划
----------------------------------------------------------
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,1,3654570532,'SYS_SAMPLE',0) 473bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
用户给出的SEED和Oracle调用ORA_HASH时指定的种子值并不一致，Oracle应该在调用ORA_HASH之前进行了处理。
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
页:
[1]