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