使用Hint /*+ full(emp)*/ 将索引全扫描改成全表扫描，看cost差别

索引全扫描的执行计划：

SQL> select max(age) from tb_emp04 emp;
已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 2758779452

--------------------------------------------------------------------------------------------
| 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 FULL SCAN (MIN/MAX)| IDX_EMP04_AGE |     1 |    13 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

 

使用Hint改为全表扫描;

SQL> select  max(age) from tb_emp04 emp;
已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 2807496884

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    13 |  3321   (1)| 00:00:40 |
|   1 |  SORT AGGREGATE    |          |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| TB_EMP04 |  1930K|    23M|  3321   (1)| 00:00:40 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

 

Operation 从 INDEX FULL SCAN变成了TABLE ACCESS FULL，cost从3 飙升到了3321，涨一千多倍。这里真正体现了有无索引的区别。

2020年1月28日