Pl/SQL批量绑定/更快的更新语句
我在PL/SQL中使用批量绑定时遇到了问题。基本上，我想要的是一个表(组件)根据Component_id和fieldname更新它的fieldvalue。所有这些都作为参数传入( varchar2_nested_table类型实际上是字符串数组，每个需要出现的update语句对应一个元素)。例如，如果Component_id = 'Compid1‘，fieldname = 'name’，那么fieldvalue应该更新为'new component name‘。
我键入了与此http://www.oracle.com/technetwork/issue-archive/o14tech-plsql-l2-091157.html相关的以下代码。代码可以工作，但并不比对in参数中的每个元素执行更新的简单循环快。因此，如果参数有1000个元素，那么将执行1000条update语句。我也意识到我没有使用批量收集，但我不认为我需要它，因为我不需要从数据库中选择任何东西，只需更新即可。
目前，1000次更新都需要4-5秒。我假设我错误地使用了批量绑定，或者对主题有误解，比如在示例中，我发现人们在2秒内执行了50,000行，等等。从我所了解的情况来看，所有人都应该通过减少上下文切换的数量来提高性能。我尝试了另一种我在网上找到的使用游标和批量绑定的方法，但得到了相同的结果。也许我对性能的期望太高了？从其他结果来看，我不这么认为。任何帮助都将不胜感激。
create or replace procedure BulkUpdate(sendSubject_in IN varchar2_nested_table_type,
fieldname_in IN varchar2_nested_table_type,fieldvalue_in IN varchar2_nested_table_type) is
TYPE component_aat IS TABLE OF component.component_id%TYPE
INDEX BY PLS_INTEGER;
TYPE fieldname_aat IS TABLE OF component.fieldname%TYPE
INDEX BY PLS_INTEGER;
TYPE fieldvalue_aat IS TABLE OF component.fieldvalue%TYPE
INDEX BY PLS_INTEGER;
fieldnames fieldname_aat;
fieldvalues fieldvalue_aat;
approved_components component_aat;
PROCEDURE partition_eligibility
IS
BEGIN
FOR indx IN sendSubject_in.FIRST .. sendSubject_in.LAST
LOOP
approved_components(indx) := sendSubject_in(indx);
fieldnames(indx):= fieldname_in(indx);
fieldvalues(indx) := fieldvalue_in(indx);
END LOOP;
END;
PROCEDURE update_components
IS
BEGIN
FORALL indx IN approved_components.FIRST .. approved_components.LAST
UPDATE Component
SET Fieldvalue = fieldvalues(indx)
WHERE Component_id = approved_components(indx)
AND Fieldname = fieldnames(indx);
END;
BEGIN
partition_eligibility;
update_components;
END BulkUpdate;回答 1
Stack Overflow用户
发布于 2012-03-08 21:32:08
还有一些其他的事情在发生，我怀疑你的每个单独的更新都花费了很多时间，可能是因为有触发器或低效的索引。(请注意，如果每个语句的开销都很大，那么使用批量更新不会节省很多时间，因为与实际工作相比，上下文切换可以忽略不计)。
下面是我的测试设置：
CREATE TABLE Component (
Component_id NUMBER,
fieldname VARCHAR2(100),
Fieldvalue VARCHAR2(100),
CONSTRAINT component_pk PRIMARY KEY (component_id, fieldname)
);
-- insert 1 million rows
INSERT INTO component
(SELECT ROWNUM, to_char(MOD(ROWNUM, 100)), dbms_random.string('p', 10)
FROM dual
CONNECT BY LEVEL <= 1e6);
CREATE OR REPLACE TYPE varchar2_nested_table_type AS TABLE OF VARCHAR2(100);
/
SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
DECLARE
l_id varchar2_nested_table_type;
l_names varchar2_nested_table_type;
l_value varchar2_nested_table_type;
l_time NUMBER;
BEGIN
SELECT rownum, to_char(MOD(rownum, 100)), dbms_random.STRING('p', 10)
BULK COLLECT INTO l_id, l_names, l_value
FROM dual
CONNECT BY LEVEL <= 100000;
l_time := dbms_utility.get_time;
BulkUpdate(l_id, l_names, l_value);
dbms_output.put_line((dbms_utility.get_time - l_time) || ' cs elapsed.');
END;
/在一台不起眼的测试机器上，在1.5秒内更新了100000行数据。逐行更新相同的数据集大约需要4秒。
您可以对新创建的表运行类似的脚本吗？
https://stackoverflow.com/questions/9615934
复制相似问题
腾讯云开发者
