CREATE PROCEDURE informix.pro_counteveryday
()
BEGIN
/* Procedure body */
define ls_cuid varchar(255);
define ls_ipv6 varchar(255);
define ld_count_date datetime year to second;
define start_date datetime year to second;
define end_date datetime year to second;
let start_date = extend(sysdate-interval(1) day to day,year to day);
let end_date = extend(sysdate day to day,year to day);
delete from rm_alarm_trans_inserttemp where 1=1;
--EXECUTE IMMEDIATE 'truncate table rm_alarm_trans_inserttemp';
-- union all
foreach select related_city_cuid as regionid,extend(a.revalarm_time,year to day) as startdate ,b.dev_ipv6
into ls_cuid,ld_count_date,ls_ipv6 from history_alarm as a,alarm_trans_resource as b
where length(a.related_port_cuid)>0 and length(b.port_cuid)>0
and (a.revalarm_time<end_date and a.revalarm_time>=start_date )
and a.related_port_cuid=b.port_cuid
--and length(b.dev_ipv6)>0
and a.alarmobject_type in('物理端口' ,'CTP端口')
insert into rm_alarm_trans_inserttemp(regionid,startdate,dev_ipv6) values(ls_cuid,ld_count_date,ls_ipv6);
end foreach;
foreach select related_city_cuid as regionid,extend(a.revalarm_time,year to day) as startdate,b.dev_ipv6
into ls_cuid,ld_count_date,ls_ipv6 from current_alarm as a ,alarm_trans_resource as b
where (a.revalarm_time<end_date and a.revalarm_time>=start_date )
and a.related_port_cuid=b.port_cuid
--and length(b.dev_ipv6)>0
and a.alarmobject_type in('物理端口' ,'CTP端口') and length(b.dev_ipv6)>0
--is not null
if(length(ls_ipv6)>0){
insert into rm_alarm_trans_inserttemp(regionid,startdate,dev_ipv6) values(ls_cuid,ld_count_date,ls_ipv6);}
end foreach;
foreach select related_city_cuid as regionid,extend(a.revalarm_time,year to day) as startdate,b.dev_ipv6
into ls_cuid,ld_count_date,ls_ipv6 from current_alarm as a,alarm_trans_resource as b
where (a.revalarm_time<end_date and a.revalarm_time>=start_date)
and a.related_ne_cuid = b.related_ne_cuid
--and length(b.dev_ipv6)>0
and a.alarmobject_type='网元'
if(length(ls_ipv6)>0){
insert into rm_alarm_trans_inserttemp(regionid,startdate,dev_ipv6) values(ls_cuid,ld_count_date,ls_ipv6);
}
end foreach;
foreach select related_city_cuid as regionid,extend(a.revalarm_time,year to day) as startdate ,b.dev_ipv6
into ls_cuid,ld_count_date,ls_ipv6 from history_alarm as a,alarm_trans_resource as b
where (a.revalarm_time<end_date and a.revalarm_time>=start_date )
and a.related_ne_cuid = b.related_ne_cuid
--and length(b.dev_ipv6)>0
and a.alarmobject_type='网元' and b.dev_ipv6 is not null
insert into rm_alarm_trans_inserttemp(regionid,startdate,dev_ipv6) values(ls_cuid,ld_count_date,ls_ipv6);
end foreach;
-- union all
/* truncate table rm_alarm_trans_souday;
insert into rm_alarm_trans_souday
select b.regionid,b.regionname,b.cityname,nvl(a.alarmnum,0) as alarmnum,nvl(a.startdate,current) as startdate
from (select cuid as regionid,label_cn as regionname,label_cn_x as cityname from v_district where label_cn = label_cn_x ) as b
left join (select regionid,startdate,count(regionid) as alarmnum from rm_alarm_trans_inserttemp group by regionid,startdate) as a
on a.regionid=b.regionid;*/
END;
END PROCEDURE;
这个数据量不太大，才一千万以上，当加上 字段的is not null的时候，查询效率非常慢。试过length(字段)>0效果也是不理想。有没有更好的查询？
