我有一个语句
insert into das.TB_LSYJ_CXF_type
(CAPITAL_ACCOUNT, stock_type)
select a.capital_account, wm_concat(a.stock_type)
from (select a.capital_account, a.stock_type
from history.tb_stk_cap_chg a
where a.tradedate >= v_begin_date
and a.tradedate <= v_end_date
group by a.capital_account, a.stock_type) a
group by a.capital_account;
commit;
单独在外边执行,大概就2分钟,很快的
但是放到存储过程里面后,就很慢了,1个小时了,还是在执行中,而且存储过程中,就是这一个语句啊,是怎么回事啊?
各位高手指点下啊
insert into das.TB_LSYJ_CXF_type
(CAPITAL_ACCOUNT, stock_type)
select a.capital_account, wm_concat(a.stock_type)
from (select a.capital_account, a.stock_type
from history.tb_stk_cap_chg a
where a.tradedate >= v_begin_date
and a.tradedate <= v_end_date
group by a.capital_account, a.stock_type) a
group by a.capital_account;
commit;
单独在外边执行,大概就2分钟,很快的
但是放到存储过程里面后,就很慢了,1个小时了,还是在执行中,而且存储过程中,就是这一个语句啊,是怎么回事啊?
各位高手指点下啊
(CAPITAL_ACCOUNT, stock_type)
select B.capital_account, wm_concat(B.stock_type)
from (select a.capital_account, a.stock_type
from history.tb_stk_cap_chg a
where a.tradedate >= v_begin_date
and a.tradedate <= v_end_date
group by a.capital_account, a.stock_type) B
group by B.capital_account;
commit;这样 看可以否
或者 这个GROUP BY 语句不要。照以下方式insert into das.TB_LSYJ_CXF_type
(CAPITAL_ACCOUNT, stock_type)
select B.capital_account, wm_concat(B.stock_type)
from (select a.capital_account, a.stock_type
from history.tb_stk_cap_chg a
where a.tradedate >= v_begin_date
and a.tradedate <= v_end_date
) B
group by B.capital_account;
commit;还有存储过程中v_begin_date 和v_end_date 是固定的数值吗?还是 insert into语句是写在了一个循环之中?
insert into das.TB_LSYJ_CXF_type(CAPITAL_ACCOUNT, stock_type)
select b.capital_account, wm_concat(b.stock_type)
from (select a.capital_account, a.stock_type
from history.tb_stk_cap_chg a
where a.tradedate between v_begin_date and v_end_date) b
group by b.capital_account;
commit;
检查两者的执行计划有什么不同
是否被锁阻塞
create or replace procedure sp_test(p_month in char, --业务月份
o_return_status OUT int, --返回状态 <0 失败 ; 0 成功
o_hint OUT char --返回提示信息 成功,或者失败原因
) IS /*==============================================================================
Copyright (c)
版 本
创建人
日 期
功能描述
==============================================================================*/
/*==============================================================================
修改人
日 期
描 述
*/
strsql varchar2(8000);
v_end_date number(19, 4);
v_begin_date number(19, 4);BEGIN
o_return_status := 0;
o_hint := 'OK'; BEGIN
--每个月的第一个及最后一个交易日
select max(a.tradedate)
into v_end_date
from params.tb_exchange_date a
where a.et_code = '1'
and a.date_flag = '1' and
left(a.tradedate, 6) = p_month;
select min(a.tradedate)
into v_begin_date
from params.tb_exchange_date a
where a.et_code = '1'
and a.date_flag = '1' and
left(a.tradedate, 6) = p_month;
EXCEPTION
WHEN OTHERS THEN
o_return_status := -1;
o_hint := '取日期出错' || 'SQLCODE:' || to_char(SQLCODE);
RAISE;
END; BEGIN
strsql := 'truncate table das.TB_LSYJ_CXF_type';
execute immediate strsql;
insert into das.TB_LSYJ_CXF_type
(CAPITAL_ACCOUNT, stock_type)
select a.capital_account, wm_concat(a.stock_type)
from (select a.capital_account, a.stock_type
from history.tb_stk_cap_chg a
where a.tradedate >= v_begin_date
and a.tradedate <= v_end_date
group by a.capital_account, a.stock_type) a
group by a.capital_account;
commit;
EXCEPTION
WHEN OTHERS THEN
o_return_status := -1;
o_hint := '插入数据报错' || 'SQLCODE:' || to_char(SQLCODE);
RAISE;
END;
commit;
-- Exception Handle
EXCEPTION
WHEN OTHERS THEN
rollback;
END;
truncate table 很快的,但是insert的时候,两个小时了,还是过不去
单独执行的时候,大概插入的数据量是五六十万条
from
(
select *
from
(
select rs.id,rs.projectName,rs.res,rn,lead(rn) over(partition by rs.id order by rn) as rn1
from (
select tb.*,rownum as rn
from
(
select project.id,project.name as projectName,e.name as res
from taskplan project
left join team t on project.id = t.taskplanid
inner join employeerole er on t.id = er.teamid and er.role = 550
inner join employee e on er.res = e.employeeid
group by project.id,project.name,e.name
order by project.id
) tb
) rs
) rs2 order by rs2.rn,rs2.rn1
)rs3
connect by rs3.rn1 = prior rs3.rn start with rs3.rn1 is null
group by rs3.id,rs3.projectName按我这种写法可以回避存储过程中wm_concat效率低的问题,主要是人为构造层次结构,实现效果是一样的!