文章存档
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2007-01-25 11:48:39
the bulk collect into clause can improve the performance of queries that reference collections.
for example, the following pl/sql block queries multiple values into pl/sql tables, both with and without bulk binds:
-- find all employees whose manager's id number is 7698.
declare
type var_tab is table of varchar2(20) index by binary_integer;
empno var_tab;
ename var_tab;
counter number;
cursor c is
select empno, ename from emp_tab where mgr = 7698;
begin
-- efficient method, using a bulk bind
select empno, ename bulk collect into empno, ename
from emp_tab where mgr = 7698;
-- slower method, assigning each collection element within a loop.
counter := 1;
for rec in c loop
empno(counter) := rec.empno;
ename(counter) := rec.ename;
counter := counter 1;
end loop;
end;
you can use bulk collect into with tables of scalar values, or tables of %type values.
without the bulk bind, pl/sql sends a sql statement to the sql engine for each employee that is selected, leading to context switches that hurt performance.
ref: oracle document (pl/sql procedures and packages)
当oracle运行pl/sql时会使用两套引擎，所有procedural code由pl/sql engine 完成，所有sql由sql engine处理。所以如果oracle从一个collection中循环执行相同的dml操作，那么为了避免两套engine切换所消耗的系统资源，可以使用bulk binds来把所有的dml操作binding到一次操作中完成。这将极大提高pl/sql的执行效率。
for example, the following pl/sql block queries multiple values into pl/sql tables, both with and without bulk binds:
-- find all employees whose manager's id number is 7698.
declare
type var_tab is table of varchar2(20) index by binary_integer;
empno var_tab;
ename var_tab;
counter number;
cursor c is
select empno, ename from emp_tab where mgr = 7698;
begin
-- efficient method, using a bulk bind
select empno, ename bulk collect into empno, ename
from emp_tab where mgr = 7698;
-- slower method, assigning each collection element within a loop.
counter := 1;
for rec in c loop
empno(counter) := rec.empno;
ename(counter) := rec.ename;
counter := counter 1;
end loop;
end;
you can use bulk collect into with tables of scalar values, or tables of %type values.
without the bulk bind, pl/sql sends a sql statement to the sql engine for each employee that is selected, leading to context switches that hurt performance.
ref: oracle document (pl/sql procedures and packages)
当oracle运行pl/sql时会使用两套引擎，所有procedural code由pl/sql engine 完成，所有sql由sql engine处理。所以如果oracle从一个collection中循环执行相同的dml操作，那么为了避免两套engine切换所消耗的系统资源，可以使用bulk binds来把所有的dml操作binding到一次操作中完成。这将极大提高pl/sql的执行效率。
给主人留下些什么吧！~~