索引全扫描的执行计划:
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日