Oracle Sample Statement Learning (i) Introduction to sample Usage

Look at a simple example:

Sql> CREATE TABLE T as

2 SELECT * from Dba_objects;

Table has been created.

Sql> SET Autot on

Sql> SELECT COUNT (*) from T;

COUNT (*)

----------

49611

Execution plan

----------------------------------------------------------

Plan Hash value:2966233522

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU) | Time |

-------------------------------------------------------------------

| 0 |      SELECT STATEMENT |     |   1 | 157 (2) | 00:00:02 |

|  1 |      SORT AGGREGATE |     |            1 |          | |

|   2 | TABLE ACCESS full| T |   52182 | 157 (2) | 00:00:02 |

-------------------------------------------------------------------

Note

-----

-Dynamic sampling used for this statement

Statistical information

----------------------------------------------------------

Recursive calls

0 db Block gets

759 consistent gets

684 physical Reads

0 Redo Size

410 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

If the sample statement is specified:

Sql> SELECT COUNT (*) from T SAMPLE (5);

COUNT (*)

----------

2464

Execution plan

----------------------------------------------------------

Plan Hash value:1807638002

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

-----------------------------------------------------------------------------

| 0 |      SELECT STATEMENT |     |    1 |   12 | 157 (2) | 00:00:02 |

This column more highlights: http://www.bianceng.cn/database/Oracle/

|  1 |      SORT AGGREGATE |     |    1 |            12 |          | |

|   2 | TABLE ACCESS sample|  T | 2481 |   29772 | 157 (2) | 00:00:02 |

-----------------------------------------------------------------------------

Statistical information

----------------------------------------------------------

0 Recursive calls

0 db Block gets

687 consistent gets

0 physical Reads

0 Redo Size

409 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)