文章存档
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2007-01-25 11:30:32
the forall keyword can improve the performance of insert, update, or delete statements that reference collection elements.
for example, the following pl/sql block increases the salary for employees whose manager's id number is 7902, 7698, or 7839, both with and without using bulk binds:
declare
type numlist is varray (100) of number;
id numlist := numlist(7902, 7698, 7839);
begin
-- efficient method, using a bulk bind
forall i in id.first..id.last -- bulk-bind the varray
update emp_tab set sal = 1.1 * sal
where mgr = id(i);
-- slower method, running the update statements within a regular loop
for i in id.first..id.last loop
update emp_tab set sal = 1.1 * sal
where mgr = id(i);
end loop;
end;
without the bulk bind, pl/sql sends a sql statement to the sql engine for each employee that is updated, leading to context switches that hurt performance.
if you have a set of rows prepared in a pl/sql table, you can bulk-insert or bulk-update the data using a loop like:
forall i in emp_data.first..emp_data.last
insert into emp_tab values(emp_data(i));
ref: oracle document (pl/sql procedures and packages)
for example, the following pl/sql block increases the salary for employees whose manager's id number is 7902, 7698, or 7839, both with and without using bulk binds:
declare
type numlist is varray (100) of number;
id numlist := numlist(7902, 7698, 7839);
begin
-- efficient method, using a bulk bind
forall i in id.first..id.last -- bulk-bind the varray
update emp_tab set sal = 1.1 * sal
where mgr = id(i);
-- slower method, running the update statements within a regular loop
for i in id.first..id.last loop
update emp_tab set sal = 1.1 * sal
where mgr = id(i);
end loop;
end;
without the bulk bind, pl/sql sends a sql statement to the sql engine for each employee that is updated, leading to context switches that hurt performance.
if you have a set of rows prepared in a pl/sql table, you can bulk-insert or bulk-update the data using a loop like:
forall i in emp_data.first..emp_data.last
insert into emp_tab values(emp_data(i));
ref: oracle document (pl/sql procedures and packages)
给主人留下些什么吧！~~