英文:
PL/SQL performace hit with SQL%rowcount
问题
我有一个PL/SQL脚本,其中我正在更新一些记录,根据这个查询的结果,我正在更新另一个表格。
在一个循环中,我正在执行以下查询,可以循环执行多达100,000条记录。
update employee set dept='department' where empid=a.id;
IF SQL%rowcount = 1 THEN
-- 更新其他表格
END IF;
是否有任何替代逻辑来实现这个?
英文:
I have a PL/SQL script in which I am updating some record and depending upon the result of this query I am updating another table.
In a loop I am executing below queries which can loop up to 100 000 records.
update employee set dept='department' where empid=a.id;
IF SQL%rowcount = 1 THEN
-- updating other table
END IF;
Is there any alternative logic to achieve this?
答案1
得分: 3
Most probably there is another option.
从你描述的情况来看,你正在循环中执行这个操作,这表明它只影响一行(基于 where
子句和 sql%rowcount = 1
;如果有多行,你会检查 >= 1
)。这种方法通常意味着逐行处理,效率较低。
看看是否可以在集合上执行操作,而不是单个行。完全去掉循环。
例如,如果这是你现在的代码:
begin
for cur_r in (select deptno, empno
from emp
where job = 'CLERK'
)
loop
update emp set sal = sal + 100
where empno = cur_r.empno;
if sql%rowcount = 1 then
update dept set
dname = dname || 'x'
where deptno = cur_r.deptno
end if;
end loop;
end;
重写成以下形式:
begin
update emp e set
e.sal = e.sal + 100
where e.job = 'CLERK'; --> 来自游标 FOR 循环的条件
update dept d set
d.dname = d.dname || 'x'
where d.deptno in (select e.deptno --> 在
from emp e --> 游标 FOR
where e.job = 'CLERK' --> 循环中使用的 SELECT
);
end;
英文:
Most probably there is another option.
From what you described, you're doing that in a loop which suggests that it affects only one row at a time (based on where
clause and sql%rowcount = 1
; if there were many rows, you'd check >= 1
). Such an approach usually means row-by-row which is slow-by-slow.
See whether you can do that on sets, not single rows. Remove the loop entirely.
For example, if this is what you have now:
begin
for cur_r in (select deptno, empno
from emp
where job = 'CLERK'
)
loop
update emp set sal = sal + 100
where empno = cur_r.empno;
if sql%rowcount = 1 then
update dept set
dname = dname || 'x'
where deptno = cur_r.deptno
end if;
end loop;
end;
rewrite it to
begin
update emp e set
e.sal = e.sal + 100
where e.job = 'CLERK'; --> condition from the cursor FOR loop
update dept d set
d.dname = d.dname || 'x'
where d.deptno in (select e.deptno --> SELECT used in
from emp e --> cursor FOR
where e.job = 'CLERK' --> loop
);
end;
答案2
得分: 2
使用SQL集合和UPDATE
语句的RETURNING ... [BULK COLLECT] INTO
子句:
CREATE TYPE int_array IS TABLE OF NUMBER(10,0);
然后,如果您正在使用:
BEGIN
FOR a IN (
SELECT id
FROM first_table
WHERE some_condition = 'MET'
)
LOOP
update employee set dept='department' where empid=a.id;
IF sql%rowcount = 1 THEN
UPDATE other_table
SET something = 'X'
WHERE employee_id = a.id;
END IF;
END LOOP;
END;
/
将其更改为:
DECLARE
p_ids int_array;
BEGIN
update employee
set dept = 'department'
where empid IN (
SELECT id
FROM first_table
WHERE some_condition = 'MET'
)
RETURNING empid BULK COLLECT INTO int_array;
UPDATE other_table
SET something = 'X'
WHERE employee_id MEMBER OF int_array;
END;
/
这样,您将完全摆脱了循环,并直接使用已更新的主键。
英文:
Use an SQL collection and the RETURNING ... [BULK COLLECT] INTO
clause of the UPDATE
statement:
CREATE TYPE int_array IS TABLE OF NUMBER(10,0);
Then if you were using:
BEGIN
FOR a IN (
SELECT id
FROM first_table
WHERE some_condition = 'MET'
)
LOOP
update employee set dept='department' where empid=a.id;
IF sql%rowcount = 1 THEN
UPDATE other_table
SET something = 'X'
WHERE employee_id = a.id;
END IF;
END LOOP;
END;
/
Change it to:
DECLARE
p_ids int_array;
BEGIN
update employee
set dept = 'department'
where empid IN (
SELECT id
FROM first_table
WHERE some_condition = 'MET'
)
RETURNING empid BULK COLLECT INTO int_array;
UPDATE other_table
SET something = 'X'
WHERE employee_id MEMBER OF int_array;
END;
/
And you will have got rid of the loop entirely and directly use the primary keys that have been updated.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论