oracle中collect排序,使用Bulk Collect提高Oracle查询效率

使用Bulk Collect提高Oracle查询效率

Oracle8i中首次引入了Bulk Collect特性，该特性可以让我们在PL/SQL中能使用批查询，批查询在某些情况下能显著提高查询效率。现在，我们对该特性进行一些简单的测试和分析。

一、首先，我们创建一个表，并插入100000条记录

在SQL/Plus中执行下列脚本：

drop table empl_tbl

/

create table empl_tbl(last_name varchar2(20),

first_name varchar2(10),

salary number(10))

/

begin

for i in 30000..1029999 loop

insert into empl_tbl(last_name,first_name,salary) values('carl'||(i-3000),'wu'||(1003000-i),i);

end loop;

end;

/

commit

/

select count(*) from empl_tbl;

/

二、使用三种方法计算表中某一字段含有多少个不重复值

2.1 使用常规的Distinct来实现

SQL> select count(distinct last_name) from empl_tbl;

Distinct Last Name

------------------

1000000

Executed in 1.531 seconds

我们可以看到，常规方法需要1.531 秒查出该表中有1000000个不重复的Last_name值。

2.2 使用游标来实现

我们执行下面语句来统计Last_name字段的不重复值个数：

CREATE OR REPLACE PROCEDURE tttt IS

all_rows number(10);

temp_last_name empl_tbl.last_name%type;

begin

all_rows:=0;

temp_last_name:=' ';

for cur in (select last_name from empl_tbl order by last_name) loop

if cur.last_name!=temp_last_name then

all_rows:=all_rows+1;

end if;

temp_last_name:=cur.last_name;

end loop;

dbms_output.put_line('all_rows are '||all_rows);

end;

请注意上面代码中的黑体部分使用了一个For Loop游标，为了提高程序可读性，我们没有显示定义游标变量。

执行结果：

all_rows are 1000000

PL/SQL procedure successfully completed

Executed in 10.39 seconds

游标需要10.396秒才能查出该表中有1000000个不重复的Last_name值，所耗时间是Distinct查询的10倍多。

2.3 使用Bulk Collect批查询来实现

示例代码如下：

declare

all_rows number(10);

--首先，定义一个Index-by表数据类型

type last_name_tab is table of empl_tbl.last_name%type index by binary_integer;

last_name_arr last_name_tab;

--定义一个Index-by表集合变量

temp_last_name empl_tbl.last_name%type;

begin

all_rows:=0;

temp_last_name:=' ';

--使用Bulk Collect批查询来充填集合变量

select last_name bulk collect into last_name_arr from empl_tbl;

for i in 1..last_name_arr.count loop

if temp_last_name!=last_name_arr(i) then

all_rows:=all_rows+1;

end if;

temp_last_name:=last_name_arr(i);

end loop;

dbms_output.put_line('all_rows are '||all_rows);

end;

请注意上面代码中，我们首先定义了一个Index-by表数据类型last_name_tab，然后定义了一个该集合数据类型的变量last_name_arr，最后我们使用Bulk Collect批查询来充填last_name_arr，请注意它

的使用语法。

执行结果：

all_rows are 1000000

PL/SQL procedure successfully completed

Executed in 2.343 seconds

从上面执行结果，我们可以看到，Bulk Collect批查询只需要2.343 秒就能查出该表中有1000000个不重复的Last_name值，所耗时间只有游标查询的1/2.5。

三． 测试结果分析

为什么会出现上面的结果呢？我们可以使用Oracle的SQL_Trace来分析一下结果。在SQL命令行中，使用alter session set sql_trace=true语句打开Oracle的Trace，然后在命令行中执行上面三种查询并使用TKPROF工具生成Trace报告。

3.1 常规Distinct查询结果分析

3.2 游标查询效率分析

3.3 Bulk Collect的查询效率分析

四． 结论

通过上面的测试和分析，我们可以看到Bulk Collect批查询在某种程度上可以提高查询效率，它首先将所需数据读入内存，然后再统计分析，这样就可以提高查询效率。但是，如果Oracle数据库的内存较小，Shared Pool Size不足以保存Bulk Collect批查询结果，那么该方法需要将Bulk Collect的集合结果保存在磁盘上，在这种情况下，Bulk Collect方法的效率反而不如其他两种方法，有兴趣的读者可以进一步测试。

另外，除了Bulk Collect批查询外，我们还可以使用FORALL语句来实现批插入、删除和更新，这在大批量数据操作时可以显著提高执行效率。