case when中的条件不能用上索引

今日在对一报表数据源作优化时无论任何都不能让其走索引.

数据源部分语句如下:

select cityCode,servtype,1,channel,b.day_time,
sum(case when to_number(to_char(subscribe_time,'yyyymmdd'))<=to_number(b.day_time||'01') then to_number(TRANSMONEY) else 0 end)/100,
sum(case when to_number(to_char(subscribe_time,'yyyymmdd'))<=to_number(b.day_time||'31') then to_number(TRANSMONEY) else 0 end)/100
from temp_channel_code_money a,g_card_report_day b
where to_number(to_char(subscribe_time,'yyyymm'))<=to_number(b.day_time)
group by cityCode,servtype,channel,b.day_time;

本想在subscribe_time列上建一funciton型的索引,结果跟踪发现其执行计划中都是full scan.

也即when ... then 之间的条件是不会利用上索引的.

在where条件中才可以利用到index.

[@more@]

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

转载于:http://blog.itpub.net/38542/viewspace-905529/