今日在对一报表数据源作优化时无论任何都不能让其走索引.
数据源部分语句如下:
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/