SELECT
*
FROM F_PAYIN, D_INDUSTRY_TYPE, D_TAXLEVELWHERE ( F_PAYIN.DHD_LEVEL=D_TAXLEVEL.LEVEL_ID ) AND ( D_INDUSTRY_TYPE.INDUSTRY_THIRD_CODE=F_PAYIN.MSFT_ID ) AND ( F_PAYIN.BTO_SUBID = '10700' AND to_number(to_char(F_PAYIN.AH_INCOMEDATE,'YYYY')) = 2012 AND to_number(to_char(F_PAYIN.AH_INCOMEDATE,'MM')) <= 3 AND F_PAYIN.BTK_ID < '60' AND F_PAYIN.BTK_ID <> '19' AND ( F_PAYIN.CHILD_CODE = '170' OR F_PAYIN.DH_ECTYPE In ( '120','130','142','143','149' ) OR F_PAYIN.PARENT_CODE = '400' OR ( F_PAYIN.CHILD_CODE In ( '150','160' ) AND ( F_PAYIN.MGYKG_ID = '0' OR F_PAYIN.MGYKG_ID Is Null OR F_PAYIN.RTP_TYPE In ( 2, 3 ) ) ) ) )f_payin记录上亿,另两个表记录几十条,f_payin已经走了时间索引,但是还是很慢,20分钟都查不出来,请教高手如何解决
*
FROM F_PAYIN, D_INDUSTRY_TYPE, D_TAXLEVELWHERE ( F_PAYIN.DHD_LEVEL=D_TAXLEVEL.LEVEL_ID ) AND ( D_INDUSTRY_TYPE.INDUSTRY_THIRD_CODE=F_PAYIN.MSFT_ID ) AND ( F_PAYIN.BTO_SUBID = '10700' AND to_number(to_char(F_PAYIN.AH_INCOMEDATE,'YYYY')) = 2012 AND to_number(to_char(F_PAYIN.AH_INCOMEDATE,'MM')) <= 3 AND F_PAYIN.BTK_ID < '60' AND F_PAYIN.BTK_ID <> '19' AND ( F_PAYIN.CHILD_CODE = '170' OR F_PAYIN.DH_ECTYPE In ( '120','130','142','143','149' ) OR F_PAYIN.PARENT_CODE = '400' OR ( F_PAYIN.CHILD_CODE In ( '150','160' ) AND ( F_PAYIN.MGYKG_ID = '0' OR F_PAYIN.MGYKG_ID Is Null OR F_PAYIN.RTP_TYPE In ( 2, 3 ) ) ) ) )f_payin记录上亿,另两个表记录几十条,f_payin已经走了时间索引,但是还是很慢,20分钟都查不出来,请教高手如何解决
楼主把F_PAYIN表结构show一下吧to_number(to_char(F_PAYIN.AH_INCOMEDATE, 'YYYY')) = 2012 AND
to_number(to_char(F_PAYIN.AH_INCOMEDATE, 'MM')) <= 3 AND为什么不建立函数索引?
//在超过一定的数据量后 尽量不要对表内数据类型进行转换
to_number(to_char(F_PAYIN.AH_INCOMEDATE,'YYYY')) = 2012
AND
to_number(to_char(F_PAYIN.AH_INCOMEDATE,'MM')) <= 3F_PAYIN.AH_INCOMEDATE >= date'2012-01-01'
and F_PAYIN.AH_INCOMEDATE < date'2013-04-01'
本来sql是这个样子的,要求按一些维度求sum,就是非常慢,明明已经走了时间函数的索引(执行计划中的第三步走的就是这个索引),并且即使改成F_PAYIN.AH_INCOMEDATE >= date'2012-01-01' and F_PAYIN.AH_INCOMEDATE < date'2013-04-01'走F_PAYIN.AH_INCOMEDATE这个字段的索引也是非常慢,求大神解救啊
不过几亿条记录还是分区为好日期时间楼上几位已经说了,这里为什么要分成两个条件,明明是一个字段
to_number(to_char(F_PAYIN.AH_INCOMEDATE,'YYYY')) = 2012
AND
to_number(to_char(F_PAYIN.AH_INCOMEDATE,'MM')) <= 3