认为索引比全表扫描快

这个观点很多人都这么认同,看执行计划,发现没走索引,就觉得出问题了,其实这个认识是错的,基于代价的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的大小来决定选择索引还是全表扫描,代价更小的就会胜出!

Trackback

no comment untill now

Add your comment now

切换到手机版