PL/SQL性能受到SQL%rowcount影响。

huangapple go评论58阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2020年1月6日 15:29:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/59608213.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定