说明:优化这个存储过程,T_text 数据为100多条;T_text_standard 200万条数据,现在这个存储过程严重影响效率,请大神帮我优化一下,或这用普通sql写出来
T_text 表结构 ,ID,xm,ygid ,zh , lx ,time,status ;T_text_standard 表结构 xm,ygid ,zh ;
create or replace procedure PROC_text (inputterm in varchar2,return_value out varchar2) is cursor bm_cursor
is select * from T_text where time=inputterm; datarow bm_cursor%rowtype;--数据每行
data_count number; --数据数量
-- inputterm 日期
--status 状态 0删除 1.正常
--ygid 员工号
-- lx 类型 01:开户 02:变更 03:销户
-- zh 账户--T_text_standard 标准表
--T_text 基础表begin
return_value:='yes'; --删除不是当前日期的数据 for datarow in bm_cursor loop
update T_text t set t.status='0' where ygid=datarow.ygid and zh=datarow.zh and time<>inputterm;
commit; --查询标准库
--循环查询标准库信息
execute immediate'select count(1) from T_text_standard where ygid= '''||datarow.ygid||'''and zh='''||datarow.zh ||'''' into data_count; --如果查询数量>0 但不销户,修改类型为变更
if data_count >0 then
execute immediate 'update T_text set lx=''02'' where ygid='''||datarow.ygid||'''and zh='''||datarow.zh||'''and lx<>''03''';
commit;
else
--如果查询数量其他 但不销户,修改类型为开户,
execute immediate 'update T_text set lx=''01'' where ygid='''||datarow.ygid||'''and zh='''||datarow.zh||'''and lx<>''03''';
commit;
--如果标准库中不存销户信息,则复制这条信息为开户信息
if datarow.lx='03' then
execute immediate' insert into T_text
select ''012'', xm, ygid,lx,zh,status ,time
from T_text where khyhdm='''||datarow.id||'''';
commit;
end if;
end if;
end loop;
end PROC_text;
T_text 表结构 ,ID,xm,ygid ,zh , lx ,time,status ;T_text_standard 表结构 xm,ygid ,zh ;
create or replace procedure PROC_text (inputterm in varchar2,return_value out varchar2) is cursor bm_cursor
is select * from T_text where time=inputterm; datarow bm_cursor%rowtype;--数据每行
data_count number; --数据数量
-- inputterm 日期
--status 状态 0删除 1.正常
--ygid 员工号
-- lx 类型 01:开户 02:变更 03:销户
-- zh 账户--T_text_standard 标准表
--T_text 基础表begin
return_value:='yes'; --删除不是当前日期的数据 for datarow in bm_cursor loop
update T_text t set t.status='0' where ygid=datarow.ygid and zh=datarow.zh and time<>inputterm;
commit; --查询标准库
--循环查询标准库信息
execute immediate'select count(1) from T_text_standard where ygid= '''||datarow.ygid||'''and zh='''||datarow.zh ||'''' into data_count; --如果查询数量>0 但不销户,修改类型为变更
if data_count >0 then
execute immediate 'update T_text set lx=''02'' where ygid='''||datarow.ygid||'''and zh='''||datarow.zh||'''and lx<>''03''';
commit;
else
--如果查询数量其他 但不销户,修改类型为开户,
execute immediate 'update T_text set lx=''01'' where ygid='''||datarow.ygid||'''and zh='''||datarow.zh||'''and lx<>''03''';
commit;
--如果标准库中不存销户信息,则复制这条信息为开户信息
if datarow.lx='03' then
execute immediate' insert into T_text
select ''012'', xm, ygid,lx,zh,status ,time
from T_text where khyhdm='''||datarow.id||'''';
commit;
end if;
end if;
end loop;
end PROC_text;
解决方案 »
- 一个创建函数索引问题
- imp/exp请教
- oracle分析函数问题
- .net 通过 commandtext=”rollback” 执行oracle回滚为何无效???????????????
- 如何在数据库中复制记录?
- 弱弱地问一句,Oracle有没有类似计算字段的东西?
- 大哥大姐们帮忙啊!急急!!
- 关于ORA-00600错误的问题。
- 2000下怎么把。PC转成的。C 转到。EXE?
- oracle 验证函数约束。(求大神帮忙~)
- java.sql.SQLException: ORA-00923: 未找到要求的 FROM 关键字
- 服务器上oracle数据库实例最近两月就挂掉一次,报472错误,还没查到具体问题,求帮忙啊,万分感谢!
select count(1) from T_text_standard where ygid= '''||datarow.ygid||'''and zh='''||datarow.zh ||''''
看看是不是这条语句拖慢了效率
所以应该是select count(1) from T_text_standard where ygid= '''||datarow.ygid||'''and zh='''||datarow.zh ||''''出了问题吧是主键或者索引的问题吗?如果存在主键或非空列上的索引,那么COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主键)、COUNT(非空列)会首先选择主键上的索引快速全扫描(INDEX FAST FULL SCAN)。若主键不存在则会选择非空列上的索引。若非空列上没有索引则肯定走全表扫描(TABLE ACCESS FULL)。其中,COUNT(ROWID)在走索引的时候比其它几种方式要慢。通过10053事件可以看到这几种方式除了COUNT(ROWID)之外,其它最终都会转换成COUNT(*)的方式来执行。