麻袋爸爸

认为索引比全表扫描快

这个观点很多人都这么认同，看执行计划，发现没走索引，就觉得出问题了，其实这个认识是错的，基于代价的CBO是这样认为的：“不管是索引还是全表扫描，哪种方式代价低，就胜出，衡量出全表扫描代价比索引低，就会放弃使用索引”。这个话题如果展开，内容将非常多，这里就不详细描述了，现仅构造一例来证明自己的观点

SQL> drop table ljb_test;
Table dropped
SQL> create table ljb_test as select * from scott.emp;
Table created
SQL> insert into ljb_test select  * from ljb_test;
14 rows inserted
SQL> /
28 rows inserted

重复多次插表，让表记录大些，好更接近生产真实环境。

SQL> select count(*) from ljb_test;
  COUNT(*)
------------------
    458752
SQL> create index idx_ljb_test on ljb_test(empno);
Index created

下面将该表的索引列empno值全部设为88，目的是数据库如利用该索引列查询将返回全部值，这个索引就成为一个选择性很差的索引了。原理是：索引要有一次INDEX RANGE SCAN，而通过索引再来找其他列的信息，需要回表，要再来一次TABLE ACCESS BY INDEX ROWID，这里存在了两个动作，就很有可能比不上直接TABLE ACCESS FULL全表扫描一种动作了。

SQL> update ljb_test set empno=88 ;
458752 rows updated
SQL> commit;
Commit complete
SQL> analyze table ljb_test compute statistics for table for all indexes for all indexed columns;
Table analyzed
SQL> explain plan for select * from ljb_test where empno=88;
Explained

查看执行计划，发现果真是走全表扫描，ORACLE认为全表扫代价更低，放弃了索引。

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 2595830654
--------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   455K|    17M|   517   (6)| 00:00:07 |
|*  1 |  TABLE ACCESS FULL| LJB_TEST |   455K|    17M|   517   (6)| 00:00:07 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMPNO"=88)
13 rows selected

如果你强制使用索引，也能走索引，但是结果会怎样呢？

SQL> explain plan for select /*+index(a,idx_ljb_test)*/ * from ljb_test a where empno=88;
Explained

可以看出来，强拗的瓜不甜，索引是走了，但是开销却更大了！目的不是一定要让系统走索引，而是让系统更快！

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3905012767
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |   455K|    17M|  4080   (2)
|   1 |  TABLE ACCESS BY INDEX ROWID| LJB_TEST     |   455K|    17M|  4080   (2)
|*  2 |   INDEX RANGE SCAN          | IDX_LJB_TEST |   455K|       |  1420   (2)
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=88)
14 rows selected

总结：索引并不总是比全表扫描来的快的，最好的方式是让ORACLE正确收集到所有信息，由自己计算出来的COST的大小来决定选择索引还是全表扫描，代价更小的就会胜出！