函数索引的问题一则

函数索引的问题一则

今天发现公司某个数据库的IO挺高的，随便瞄了一下statspack报告发现disk read top sql中存在大量相似的sql，消耗了大量的IO。

于是试图优化一番。

select count(distinct h.userid) usernum, h.packageid, h.actionplace

  from subscription_hist h, prefix p

 where h.actiontype = '1'

   and substr(h.userid, 0, 7) = p.prefixnum

   and p.provinceid = '11'

   AND to_char(h.LUPDDATE, 'yyyy-MM-dd') = '2011-09-07'

 group by h.packageid, h.actionplace;

1.首先发现sql语句使用了两个函数会造成无法使用索引to_char(h.LUPDDATE, 'yyyy-MM-dd')和substr(h.userid, 0, 7)。

2.历史表subscription_hist只做了半年的分区，相关的索引也没建上。

于是乎很自然的想到使用函数索引来解决问题，马上创建一个时间的函数索引

create index COMP_CORE.idx_sub_hist_lupddate on COMP_CORE.subscription_hist(to_char(LUPDDATE, 'yyyy-mm-dd')) tablespace INDEX_HIST local online;

创建索引后尝试测试一番看看优化后的成就，结果发现无法使用索引，嗯，不听话了，重新做了表和索引的分析，还是不行，奇怪了。

重新看看sql语句，发现to_char(h.LUPDDATE, 'yyyy-MM-dd')中的MM是大写的而且带字段带有别名h.，于是开始怀疑这两个家伙影响了oracle的判断。

将to_char(h.LUPDDATE, 'yyyy-MM-dd')改为to_char(LUPDDATE, 'yyyy-mm-dd')后索引正确的使用了，但to_char(LUPDDATE, 'yyyy-MM-dd')无法使用到索引。

问题很容易发现了，oracle在创建索引时保留了'yyyy-MM-dd'单引号内的大小值，当比较函数索引时发现大小写不同则认为是无法使用索引，因此引致索引失效。

再次证明做数据库调优确实要求非凡的眼力和耐力。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13605188/viewspace-706927/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13605188/viewspace-706927/