一直有朋友问，是不是表建了索引，一定会使用索引，在RBO时代，访问效率会参考一些规则，优先级高的，认为效率就高，例如索引就比全表扫描效率高，但CBO时代，则会以成本为依据，谁的成本低，谁的效率就高，这样更科学。
建了索引，SQL却未使用索引，有很多情况，何况我不精通，所以不能一一枚举出来，但结合昨天广分一位兄弟的问题，列举出两个场景，提供一些思路和方法。
场景一：正确的有索引却不用
创建测试表，插入一条数据，创建索引，采集表和索引的统计信息，USER_TABLES视图显示有1条记录，平均行长为14字节。
执行update语句，条件是索引字段id，执行计划显示，对表的扫描，用全表扫描而不是索引扫描，
如果各位对索引的结构，比较了解的话，就比较容易理解其原因了，我们此处用的是BTree索引，即平衡二叉树索引，他的结构类似一棵树形，有根节点、分支节点，以及叶子结点，唯一索引和非唯一索引，叶子结点存储的信息会略有不同，我们此处建立的是非唯一索引，因此叶子结点中存储的，则是索引字段键值，以及对应的rowid，rowid是一个伪列，通过他可以快速定位，一条记录对应的物理位置，因为他的信息包括了，这条记录对应的文件号、块号、行号等信息，rowid的访问CBO时代他的优先级是最高的，关于rowid，内容其实还是很丰富的，有机会我们再聊。
再说索引结构，为什么说索引快，主要就是因为索引的查找，就是以这棵树的根节点开始，找分支节点，如果等值查询，则可以直接定位到具体的叶子结点，如果是范围查询，因为叶子结点是排序的，因此只要找出起始节点，按照叶子结点的指针，就可以找出对应结果集，无论何种用法，我们可以看出，他的执行路径都是有限的，根节点-分支节点-叶子结点，而且即使表的数据量再增加，只要索引数层级不变，其消耗的代价就是稳定的，而全表扫描，则会随着表数据量的增加，高水位不断上升，导致增加的成本消耗。
但一些情况下，索引扫描效率未必高，比如上面实验，因为要是SQL语句需要的数据，除了索引字段外，还有其他字段，则首先使用索引扫描，定位叶子结点，根据其中存储的rowid，回表找出对应的其他字段信息，而且若是INDEX RANGE SCAN这种索引范围扫描，会是单块读，而全表扫描则是多块读，相比之下，1次IO读的数据块数量就不同，对应的数据量就不同，效率就会不同，如果使用全表扫描，由于只有1条记录，则可以1次IO就完成数据读取。如果使用索引扫描，则先要消耗IO扫描索引，再回表消耗IO读取数据，成本高于全表。
虽然此处用了1条记录测试，有些极端，但即使有很多记录，还是需要综合考虑多块读、单块读、表的记录数、平均行长、回表等各种因素，只要TABLE ACCESS FULL的成本值低，无论是否有索引，都会选择TABLE ACCESS FULL。如果要用科学的数据，则可以做一个10053事件，就可以看出全表扫描和索引扫描两种方法对应的成本计算过程和结果，了解Oracle自己的选择。
场景二：错误的有索引却不用
我们接着插入10000条记录，但不执行统计信息更新，USER_TABLES视图显示表只有1条记录，可实际此时应该有10001条记录了。
我们执行comment表操作，让Oracle重新生成执行计划，但发现还是采用了全表扫描，
其实此处我们就可以看出问题，TABLE ACCESS FULL会扫描所有数据，但此处Rows值是1，说明Oracle认为表记录只有1条，自然TABLE ACCESS FULL是比较合适的选择，无可厚非。
接下来我们用一个11g推出的工具，STA(SQL Tuning Advisor)，来看看此时Oracle可以给我们什么建议，首先创建任务，其中sql_id是我们执行update语句对应的sqlid，
接着执行report_tuning_task输出建议结果，请注意要是不设置开始的set，则可能结果显示为空，
内容如下，表示Oracle对这条SQL有两个建议，
第一个建议是，手工采集表和索引的统计信息，并且给出了SQL语句，
第二个建议，则是使用SQL Profile，固定执行计划，
并且给出了按照原始SQL，以及使用了SQL Profile的SQL，各执行10次的统计信息平均值数据，原始SQL用的TABLE ACCESS FULL，
使用SQL Profile的SQL，用的索引扫描INDEX RANGE SCAN，
可以看出，通过SQL Tuning Advisor，可以让Oracle来提供一些优化建议，并且直接给出了一些方法SQL，能辅助我们进行优化工作。
接下来针对实验问题，我们采用手工收集统计信息，再次执行，就会发现SQL用了索引范围扫描，相应地可以看10053事件，就会发现索引的成本，此时就会低于TABLE ACCESS FULL，
总结：
1. CBO时代，并不是有了索引，就一定会用索引，能不能用上，需要看谁的成本更低，影响成本值计算的因素很多，本文的问题，只有1条记录的时候，不用索引是对的，因为多块读的全表扫描，成本低于单块读的索引扫描(需要回表)，但当有10001条记录的时候，不用索引就是错误的了，原因就是由于统计信息不准，造成Oracle计算成本值出现偏差，此时要么手工采集统计信息，要么使用SQL Profile固化执行计划，当然有索引但不用的场景，还有其他的因素，具体问题具体分析。
2. 像本文中，灌入大量数据，此时需要手工收集统计信息，才能保证Oracle估算成本值的正确，虽然Oracle有自动收集统计信息的job，但前提是要求这张表，当日的增删改数据量超过表总量的10%(参数可以调整)，或者执行过truncate操作，可以参考dbsnake的书，而且每晚开始，因此之前这段时间其实是有可能使用了错误的执行计划，这就会有一些副作用。
3. SQL Tuning Advisor工具，可以让Oracle为我们优化SQL提出一些建议，自动化指出一些方向，还是比较有用的一种方法。
如果您觉得此篇文章对您有帮助，欢迎关注微信公众号：bisal的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)
