J2EE社区

苟有恒,何必三更起五更眠;
最无益,只怕一日曝十日寒.
posts - 241, comments - 318, trackbacks - 0, articles - 16

使用Bulk Collect提高Oracle查询效率

Posted on 2011-12-31 15:03 xcp 阅读(1002) 评论(0)  编辑  收藏 所属分类: Database
使用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语句来实现批插入、删除和更新,这在大批量数据操作时可以显著提高执行效率。



名称: ♪4C.ESL | .↗Evon
口号: 遇到新问题♪先要寻找一个方案乄而不是创造一个方案こ
mail: 联系我



只有注册用户登录后才能发表评论。


网站导航: