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