Oracle Sample Statement Learning (iii) sample statement for index scan

And then, in the previous example, set up two indexes on the table:

Sql> CREATE INDEX Ind_t_owner on T (OWNER);

The index has been created.

Sql> CREATE INDEX ind_t_id on T (object_id);

The index has been created.

For full table scans, use the sample statement, which can be seen directly from the execution plan:

Sql> SELECT OWNER, object_id from T SAMPLE (0.01);

OWNER object_id

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

SYS 4479

Public 13929

Public 30919

Execution plan

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

Plan Hash value:2767392432

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

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

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

| 0 |      SELECT STATEMENT |     |   5 |   115 | 157 (2) | 00:00:02 |

|  1 | TABLE ACCESS sample|     T |   5 |   115 | 157 (2) | 00:00:02 |

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

Statistical information

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

1 Recursive calls

0 db Block gets

Consistent gets

0 physical Reads

0 Redo Size

527 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

For index scans, you can also use sample, but not directly from the execution plan:

Sql> SELECT OWNER, object_id from T SAMPLE (1) WHERE wner = USER;

OWNER object_id

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

YANGTK 61217

YANGTK 83222

YANGTK 90979

Execution plan

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

Plan Hash value:3630032853

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

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

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

| 0 |             SELECT STATEMENT |    |   22 |     506 | 6 (0) | 00:00:01 |

|  1 | TABLE ACCESS by INDEX rowid|    T |   22 |     506 | 6 (0) | 00:00:01 |

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