将 oracle DB 中的 100 万条记录更新为 10k 批；
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32160236/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
updating 1 million records in oracle DB as batch of 10k;
提问by user2875775
I have to update a table in oracle db as batch of 10k.
我必须将 oracle db 中的表更新为 10k 的批次。
I tried this:
我试过这个：
BEGIN
WHILE (true) LOOP
UPDATE TOP (10000) CUSTOMERS SET ACTIVE = 'N' WHERE ACTIVE='Y';
IF sql%notfound THEN
EXIT;
END IF;
COMMIT;
END LOOP;
END;
It doesn't works as plsql doesn't support top.
它不起作用，因为 plsql 不支持 top。
Any suggestions?
有什么建议？
回答by Carlo Sirna
your pl/SQL block, using JVA's suggestion, should be completed like this (somce you are new to pl/sql I am adding some syntactical suggestions that you could be interested in):
你的 pl/SQL 块，使用 JVA 的建议，应该像这样完成（因为你是 pl/sql 的新手，我正在添加一些你可能感兴趣的语法建议）：
BEGIN
-- WHILE (TRUE) can be omitted: "loop ... end loop;"
-- already is an endless loop
LOOP
UPDATE CUSTOMERS
SET ACTIVE = 'N'
WHERE ACTIVE='Y'
AND rownum <= 1000;
exit when sql%notfound; -- notice that exit accepts "when condition"
--IF sql%notfound THEN -- you can avoid a if/endif by using "exit when"
-- EXIT;
-- END IF;
COMMIT;
END LOOP;
commit; -- you missed this commit for the last iteration
END;
Don't be tempted of placing "commit" before the "exit when sql%notfound": after a "commit" sql%notfound is always false and your loop would be really endless.
不要试图将“commit”放在“exit when sql%notfound”之前：在“commit”之后 sql%notfound 总是假的，你的循环真的是无止境的。
Let me point out that, in order to be efficient, this approach requires the the "ACTIVE" column to be indexed!
我要指出的是，为了提高效率，这种方法需要对“ACTIVE”列进行索引！
if you don't have an index on the "active" column, each "update" will be forced to restart a full table scan from the beginning just to find the next 1000 records that still need to be updated.
如果您在“活动”列上没有索引，则每次“更新”都将被迫从头开始重新启动全表扫描，以找到下一个仍需要更新的 1000 条记录。
This other approach I am proposing uses some advanced PL/SQL features you, as a learner, mighy be interested in (rowid, "table of",cursor bulk fetches and "forall") and does only one scan of the table to be updated so (in case of absence of indexes) it performs better than the previous approach. keep in mind that if you have indexes, this is slower (but using foralls, bulk collects and rowid accesses, it is not that slower), but it can get handy in cases where things are more complex (for example: when the where condition needs to access data from other tables using complex joins that can't be made faster). There are cases when the "where" is so complex and slow that you really don't want to re-execute it over and over using a "where rownum<=1000" approach.
我提议的另一种方法使用了一些高级 PL/SQL 功能，作为学习者，您可能会感兴趣（rowid、“table of”、cursor bulk fetches 和“forall”）并且只对要更新的表进行一次扫描所以（在没有索引的情况下）它比以前的方法表现更好。请记住，如果您有索引，这会较慢（但使用 foralls、批量收集和 rowid 访问，它并没有那么慢），但在事情更复杂的情况下（例如：where 条件时，它可以派上用场需要使用无法更快的复杂连接访问其他表中的数据）。在某些情况下，“where”是如此复杂和缓慢，以至于您真的不想使用“where rownum<=1000”方法一遍又一遍地重新执行它。
declare
type rowid_array is table of rowid;
ids rowid_array;
cursor cur is
select rowid as id
from CUSTOMERS
where ACTIVE='Y';
begin
open cur;
loop
fetch cur bulk collect into ids limit 1000;
exit when ids.count = 0;
forall c in ids.first .. ids.last
update CUSTOMERS set ACTIVE='N';
commit;
end loop;
end;
回答by jva
UPDATE CUSTOMERS
SET ACTIVE = 'N'
WHERE ACTIVE='Y'
AND ROWNUM <= 10000; -- first 10k rows
回答by mahi_0707
Adding on top of jva'sanswer to process all rows without changing the query for every batch:
添加在jva 的答案之上以处理所有行而不更改每个批次的查询：
Before update:
更新前：
select count(1) from cust ; --1000000
select distinct active from cust ; -- Y
Update:
更新：
Begin
for i in 1..100
loop
update cust set Active = 'N' where ACTIVE = 'Y'
and rownum <= 10000;
-- dbms_output.put_line ('i value : ' || i );
commit;
end loop;
dbms_output.put_line ('All rows updated ' );
end;
Output:
输出：
All rows updated
Statement processed.
3.77 seconds
After :
后 ：
select distinct active from cust ; -- N
回答by Rahul
If you have auto incremented synthetic IDcolumn on CUSTOMERStable then you can simply use a WHEREcondition saying
如果你ID在CUSTOMERS表上有自动递增的合成列，那么你可以简单地使用一个WHERE条件说
WHERE ID <= 10000
