上午阅读《ORACLE 编程艺术 》关于索引方面的详细介绍，遂联想起目前正在开发的 OLAP项目的数据库，想看看ORACLE优化器是如何利用索引查询数据的。以前从来没有关心过索引的物理大小、效率、维护成本等方面的问题 ，只知道表中建立了索引对查询一定是有帮助。不调研还真不知道，一调研被吓了一大跳，原来目前数据库中聚集表中建立的索引非常不科学，不仅庞大而且还影响了查询效率，以及增加维护（insert/delete ）成本。 通过优化索引，索引文件占用空间 缩小了3~5倍,而且最重要的是查询效率竟提高了好几倍！成果还是非常显著的！
下面来详细阐述优化的过程：
一、表结构概述：
得找一张数据量比较大的聚集表（AGG_CALL_CALLRECORD_DSBSEAT）进行分析，这样优化效果才比较明显。
表结构：
维度字段有 (YEARCODE、 QUARTERCODE、MONTHCODE 、 DAYCODE、WEEKCODE、 DAYOFWEEKCODE、SEATID、SERVICEMAPID )
其中SEATID与SERVICEMAPID是关联维度表中的ID。
表中每一行的维度字段组合 都是唯一的 。
剩下的字段（TIMELEN、REALTIMELEN、SEATREALTIMELEN、CALLNUM、REPEATCALL、UNRESOLVEDCALL、PROBLEM、FACT_COUNT、BUSSINESSMAPID、VOICECALL、VOICETIMELEN）都是数值型的指标 。
该表建立一个多列索引（IDX_AGG_CALL_CALLRECORD_DSBSE），索引列是所有的维度字段：
CREATE INDEX IDX_AGG_CALL_CALLRECORD_DSBSE ON AGG_CALL_CALLRECORD_DSBSEAT
(YEARCODE, MONTHCODE, DAYCODE, SERVICEMAPID, BUSSINESSMAPID, SEATID, QUARTERCODE, WEEKCODE, DAYOFWEEKCODE)；
该表约有一千五百万行数据:
表中共有八个月的数据，下面是每个月数据量的分布：
二、索引分析：
通过ANALYZE INDEX工具，可以分析出目前索引的详细信息。
ANALYZE INDEX IDX_AGG_CALL_CALLRECORD_DSBSE VALIDATE STRUCTURE;
分析这个索引居然用了483秒 ，可见这个索引文件确实蛮大的。
这个索引占用了差不多1.8G的空间，索引约有三百五十万的个条目，叶子节点的高度为4，索引不仅很宽，而且还很高，占用空间也非常的大。
我尝试查询该表2012年10月份的数据，下面是优化器的解释计划 :
我惊讶的发现，ORACLE优化器 居然使用了全表扫描！这是为什么呢？10月份的数据也才两百万左右呀，相对于整个表一千五百万的数据行 ，查询数据量只占了总行数的 13% ，我又不是查询该表90%的数据，没理由会选择全表扫描的！
经过分析才发现原来优化器估算出通过索引扫描的成本要比全表扫描要高，所以才选择了全表扫描，连ORACLE优化器都摒弃的该索引！
现在想想怪不得WEB查询效率慢，原来很多查询都选择了全表扫描！
可以预见这么庞大一个索引，查询、维护的成本都会比较高，缩减该索引势在必行了。
三、优化索引：
仔细考虑了下该索引中的字段，发现其实 这是三个字段SERVICEMAPID, BUSSINESSMAPID, SEATID是永远用不到索引的，这三个字段仅仅用来关联维度表的，WEB端需要查询相应维度表的数据都会关联这三个字段，即使查询条件中要筛选维度的数据， 那也是在维度表中筛选的，跟聚集表没有半毛钱关系 ！
所以决定删除这三个无用的字段，看看索引是不是时会缩减。
新建IDX_AGG_CALL_DSBSE_YMDWQD 索引：
CREATE INDEX IDX_AGG_CALL_DSBSE_YMDWQD ON
AGG_CALL_CALLRECORD_DSBSEAT (YEARCODE, MONTHCODE, DAYCODE,WEEKCODE,QUARTERCODE,DAYOFWEEKCODE)
分析新建的索引：
很明显现在索引文件占用了460MB的空间，相对于之前1.8G的空间 ，缩小了将近4倍。
下面我们再来看看，刚才那条查询语句，ORACLE优化器会不会还选择全表扫描：
优化器选择了索引扫描。
以上通过解释计划，比较确实查询效率提高了很多，但是实际执行计划又是什么样的呢？必须得搞清楚，不然看不到实际效果；
我们来看看执行计划：
1、首先使用旧索引（IDX_AGG_CALL_CALLRECORD_DSBSE ）使用查询;
SELECT * FROM AGG_CALL_CALLRECORD_DSBSEAT T WHERE T.YEARCODE='2013' AND T.MONTHCODE='10'
执行计划信息：
实际执行使用全表扫描（TABLE ACCESS FULL） ：
查询总用时7.35秒，访问磁盘 138657次，访问138876个数据块，SQL语句访问字节数：6837K
2、在表中新建IDX_AGG_CALL_DSBSE_YMDWQD索引：
再执行上面一样的查询语句：
查询总用时2.29秒秒，访问磁盘 10206次，访问111784个数据块；
无论从时间、I/O还是资源上都比以前查询要快很多倍； 
通过新建索引，去掉原索引中无用的关联维度的三个字段（SERVICEMAPID, BUSSINESSMAPID, SEATID ），索引空间缩小了四倍，查询效率提升三倍，I/O减少了十倍。
建立多列索引的时候一定要注意选择有用的列，不然会导致索引过于庞大，而且影响查询效率。
虽然我优化了索引，但是聚集表中的每天都会增加很多的数据，总有一天索引文件会很庞大的，到时扫描索引的时间也会很长！
那么必须得有一定的策略，去管理索引，ORACLE中索引和表一样也可以分区，这样在查询数据时，只需要扫描某几个分区的索引，而不用全索引扫描。