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