Oracle Sample sentence Learning (iv) The relationship between sample scanning and hint

Oracle's documentation describes that when the sample statement is not included, you can use hint to specify the execution plan, which in fact takes effect even if the sample statement is included. Hint

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

object_id

----------

60607

70958

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 |

|* 2 | INDEX RANGE SCAN |    Ind_t_owner |       22 |     | 5 (0) | 00:00:01 |

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

predicate information (identified by Operation ID):

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

2-access ("OWNER" =user@!)

Filter (Ora_hash (rowid,0,2211694651, ' Sys_sample ', 0) <42949673)

Statistical information

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

0 Recursive calls

0 db Block gets

6 consistent gets

4 physical Reads

0 Redo Size

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

2 rows processed

Sql> SELECT/*+ Full (t)/object_id from T SAMPLE (1) WHERE wner = USER;

No rows selected

Execution plan

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

Plan Hash value:2767392432

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

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

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

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

| 0 |      SELECT STATEMENT |    |   22 |   506 | 158 (2) | 00:00:02 |

|* 1 | TABLE ACCESS sample|    T |   22 |   506 | 158 (2) | 00:00:02 |

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

predicate information (identified by Operation ID):

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

1-filter ("OWNER" =user@!)

Statistical information

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

1 Recursive calls

0 db Block gets

517 Consistent gets

430 physical Reads

0 Redo Size