Oracle SAMPLE语句（二）
Oracle支持SELECT语句只扫描表的一部分记录。这篇简单介绍SEED语句。
对于普通的SAMPLE语句，Oracle每次返回的结果是不固定的：
SQL> SELECT OBJECT_ID FROM T SAMPLE (0.01);
OBJECT_ID
----------
35199
执行计划
----------------------------------------------------------
Plan hash value: 2767392432
----------------------------------------------------------------------------
|> ----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 85 | 157 (2)| 00:00:02 |
| 1 |TABLE ACCESS SAMPLE| T | 5 | 85 | 157 (2)| 00:00:02 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1recursive calls
0db block gets
70consistent gets
0physical reads
0redo> 411bytes 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 OBJECT_ID FROM T SAMPLE (0.01);
OBJECT_ID
----------
2013
5637
16087
18032
21327
27788
38549
40479
44344
49347
已选择10行。
执行计划
----------------------------------------------------------
Plan hash value: 2767392432
----------------------------------------------------------------------------
|> ----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 85 | 157 (2)| 00:00:02 |
| 1 |TABLE ACCESS SAMPLE| T | 5 | 85 | 157 (2)| 00:00:02 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0recursive calls
0db block gets
83consistent gets
0physical reads
0redo> 530bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
10rows processed
SQL> SELECT OBJECT_ID FROM T SAMPLE (0.01);
OBJECT_ID
----------
21949
26619
43388
执行计划
----------------------------------------------------------
Plan hash value: 2767392432
----------------------------------------------------------------------------
|> ----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 85 | 157 (2)| 00:00:02 |
| 1 |TABLE ACCESS SAMPLE| T | 5 | 85 | 157 (2)| 00:00:02 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0recursive calls
0db block gets
76consistent gets
0physical reads
0redo> 462bytes 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
如果指定SEED语法，则Oracle保证对应相同的SEED，每次返回的结果是相同的：
SQL> SELECT OBJECT_ID FROM T SAMPLE (0.01) SEED (1);
OBJECT_ID
----------
523
3896
5433
14517
24876
32183
39766
47931
已选择8行。
执行计划
----------------------------------------------------------
Plan hash value: 2767392432
----------------------------------------------------------------------------
|> ----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 85 | 157 (2)| 00:00:02 |
| 1 |TABLE ACCESS SAMPLE| T | 5 | 85 | 157 (2)| 00:00:02 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1recursive calls
0db block gets
80consistent gets
0physical reads
0redo> 509bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
8rows processed
SQL> SELECT OBJECT_ID FROM T SAMPLE (0.01) SEED (1);
OBJECT_ID
----------
523
3896
5433
14517
24876
32183
39766
47931
已选择8行。
执行计划
----------------------------------------------------------
Plan hash value: 2767392432
----------------------------------------------------------------------------
|> ----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 85 | 157 (2)| 00:00:02 |
| 1 |TABLE ACCESS SAMPLE| T | 5 | 85 | 157 (2)| 00:00:02 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0recursive calls
0db block gets
80consistent gets
0physical reads
0redo> 509bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
8rows processed
SQL> SELECT OBJECT_ID FROM T SAMPLE (0.01) SEED (2);
OBJECT_ID
----------
7837
11394
17636
34234
45510
执行计划
----------------------------------------------------------
Plan hash value: 2767392432
----------------------------------------------------------------------------
|> ----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 85 | 157 (2)| 00:00:02 |
| 1 |TABLE ACCESS SAMPLE| T | 5 | 85 | 157 (2)| 00:00:02 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
133recursive calls
0db block gets
89consistent gets
0physical reads
0redo> 481bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
4sorts (memory)
0sorts (disk)
5rows processed
SQL> SELECT OBJECT_ID FROM T SAMPLE (0.01) SEED (2);
OBJECT_ID
----------
7837
11394
17636
34234
45510
执行计划
----------------------------------------------------------
Plan hash value: 2767392432
----------------------------------------------------------------------------
|> ----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 85 | 157 (2)| 00:00:02 |
| 1 |TABLE ACCESS SAMPLE| T | 5 | 85 | 157 (2)| 00:00:02 |
----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0recursive calls
0db block gets
75consistent gets
0physical reads
0redo> 481bytes sent via SQL*Net to client
385bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
5rows processed
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
页:
[1]