[Oracle]-[索引][HINT][执行计划]-带HINT的索引执行计划(三)
nt
SQL> select count(id) from tbl_big;
Execution Plan
----------------------------------------------------------
Plan hash value: 2252048431
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01
| 1 | SORT AGGREGATE | | 1 | 13 | |
| 2 | INDEX FAST FULL SCAN| T_B_IDX | 1000 | 13000 | 3 (0)| 00:00:01
|
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> select /*+ index(tbl_big, t_b_idx) */ count(*) from tbl_big;
Execution Plan
----------------------------------------------------------
Plan hash value: 475686685
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TBL_BIG | 1000 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> select /*+ index(tbl_big, t_b_idx) */ count(id) from tbl_big;
Execution Plan
----------------------------------------------------------
Plan hash value: 1004523789
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN| T_B_IDX | 1000 | 13000 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
不同的点：
小表对id字段count，无论是否使用hint，都是INDEX FULL SCAN。
大表对id字段count，不带hint，是INDEX FAST FULL SCAN，对id字段count带hint，是INDEX FULL SCAN。(这里我感觉不带hint，CBO还能选择FFS的方式可能更优，但如果带了hint，则强制使用并不最优的FS)。