请问下面的sql语句可以优化吗?
主要是cus_inf,cus_account_his,cus_acc_rmb_current_deposit每张表都太大了,均超过2G
select t.cus_id,
t.advisor_id,
t.cus_type,
sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) as cus_assert,
CASE WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) < 10000.00 then 1
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 10000.00 and 49999.99 then 2
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 50000.00 and 99999.99 then 3
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 100000.00 and 199999.99 then 4
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 200000.00 and 299999.99 then 5
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 300000.00 and 399999.99 then 6
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 400000.00 and 499999.99 then 7
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 500000.00 and 999999.99 then 8
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) > 999999.99 THEN 9 END AS amount_type
from cus_inf t
left join cus_account_his a
on a.cus_id = t.cus_id
left join cus_acc_rmb_current_deposit b
on a.acno = b.acno
where t.delete_flag = '0'
and t.rescission_date is null
and (t.cus_type = 1
or t.cus_type = 3)
and a.stat_date = '2009-01-01'
and b.stat_date = '2009-01-01'
group by t.cus_id, t.advisor_id, t.cus_type执行计划如下:
主要是cus_inf,cus_account_his,cus_acc_rmb_current_deposit每张表都太大了,均超过2G
select t.cus_id,
t.advisor_id,
t.cus_type,
sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) as cus_assert,
CASE WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) < 10000.00 then 1
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 10000.00 and 49999.99 then 2
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 50000.00 and 99999.99 then 3
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 100000.00 and 199999.99 then 4
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 200000.00 and 299999.99 then 5
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 300000.00 and 399999.99 then 6
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 400000.00 and 499999.99 then 7
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) between 500000.00 and 999999.99 then 8
WHEN sum(DECODE(0, '0', b.balance, '1', rijun_this_month, '3', rijun_this_season, '4', rijun_this_year)) > 999999.99 THEN 9 END AS amount_type
from cus_inf t
left join cus_account_his a
on a.cus_id = t.cus_id
left join cus_acc_rmb_current_deposit b
on a.acno = b.acno
where t.delete_flag = '0'
and t.rescission_date is null
and (t.cus_type = 1
or t.cus_type = 3)
and a.stat_date = '2009-01-01'
and b.stat_date = '2009-01-01'
group by t.cus_id, t.advisor_id, t.cus_type执行计划如下:
t.delete_flag = '0' and t.rescission_date is null and (t.cus_type = 1 or t.cus_type = 3)
这三个条件没有利用到索引