全扫描与快速全索引扫描的比较实验

先准备实验材料：

一张表及其数据：

create table tb_emp04(
    id number(8,0) primary key,
    name nvarchar2(20),
    age number(2,0)
)

insert into tb_emp04
select rownum,dbms_random.string('*',dbms_random.value(6,20)),dbms_random.value(20,80)  from dual
connect by level<=2000000
order by dbms_random.random

然后为name和age字段创建个索引：

create index idx_emp04_name_age on tb_emp04(name,age);

然后对sql plus进行一点设置：

SQL> set timing on;
SQL> set linesize 10000;
SQL> set autotrace trace exp;

再跑一句SQL，看看其执行计划：

SQL> select name from tb_emp04 where age>20 and age <69;
已用时间:  00: 00: 00.00

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

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |  1543K|    51M|  3180   (1)| 00:00:39 |
|*  1 |  INDEX FAST FULL SCAN| IDX_EMP04_NAME_AGE |  1543K|    51M|  3180   (1)| 00:00:39 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("AGE">20 AND "AGE"<69)

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

INDEX FAST FULL SCAN就是索引快速扫描，利用的正是刚才创建的索引idx_emp04_name_age，耗费是3180.

然后再删掉索引：

SQL> drop index idx_emp04_name_age;

索引已删除。

已用时间:  00: 00: 00.32

再执行一遍同样的sql：

SQL> select name from tb_emp04 where age>20 and age <22;
已用时间:  00: 00: 00.00

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

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 30439 |  1040K|  3328   (2)| 00:00:40 |
|*  1 |  TABLE ACCESS FULL| TB_EMP04 | 30439 |  1040K|  3328   (2)| 00:00:40 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("AGE">20 AND "AGE"<22)

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

可以看到索引快速扫描变成了全（表）扫描，name也换成了表名TB_EMP04，耗费上升了一成，到了3328.

感觉Oracle的优化器作用下，全表扫描也未必比索引快速扫描慢多少，索引的效果不那么显著了。

--2020年1月28日--