使用Bulk Collect提高Oracle查询效率

http://www.cnblogs.com/rootq/archive/2008/11/17/1335491.html

 

使用Bulk Collect提高Oracle查询效率

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

1． 首先，我们创建一个表，并插入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 3000..102999 loop
insert into empl_tbl(last_name,first_name,salary) values('carl'||(i-3000),'wu'||(103000-i),i);
end loop;
end;
/
commit
/
select count(*) from empl_tbl;
/

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

2.1 使用常规的Distinct来实现

SQL> select count(distinct last_name) "Distinct Last Name" from empl_tbl;

Distinct Last Name
------------------
            100000
 
Executed in 0.36 seconds

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

2.2 使用游标来实现

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

declare
  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 100000
PL/SQL procedure successfully completed
Executed in 1.402 seconds

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

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 100000
PL/SQL procedure successfully completed
Executed in 0.28 seconds
从上面执行结果，我们可以看到，Bulk Collect批查询只需要0.28秒就能查出该表中有100000个不重复的Last_name值，所耗时间只有游标查询的1/5，同时它比Distinct常规查询的速度也要快。

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

3.1 常规Distinct查询结果分析********************************************************************************

select count(distinct last_name)
from
 empl_tbl

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.28       0.32        198        425          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.28       0.32        198        425          4           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT GROUP BY
 100000   TABLE ACCESS FULL EMPL_TBL

 

********************************************************************************
上述查询首先对empl_tbl进行全表扫描，然后分组排序得出结果。SQL解析、执行的时间都可忽略不计，主要时间花在读取数据上，因为当前SQL只是一个查询，没有任何增、删改操作。在数据读取阶段，需要从磁盘读取198个Oracle数据块，一致性读取（query，consistent gets）数据块425块。因为磁盘物理读是非常耗时的，所以该查询执行起来不是特别快。

3.2 游标查询效率分析

 

********************************************************************************
SELECT LAST_NAME
FROM
 EMPL_TBL ORDER BY LAST_NAME

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch   100001      0.71       0.62        198        425          4      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100002      0.71       0.62        198        425          4      100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62     (recursive depth: 1)********************************************************************************该方法的主要耗时也是在数据读取阶段，虽然磁盘读取数据块和一致性读取数据块的数目同Distinct SQL查询相等，但是，因为该方法中的游标要循环100001次，所以上面的SQL的读取会发生100001次，总共读出了100000行数据，这就是为什么使用游标需要1.4秒的原因。下面我们看看使用Bulk Collect会发生什么？

3.3 Bulk Collect的查询效率分析

 

********************************************************************************

SELECT LAST_NAME
FROM
 EMPL_TBL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.08       0.07          0        425          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.08       0.07          0        425          0      100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62     (recursive depth: 1)

********************************************************************************

虽然这种方法也要读取100000行数据，但是读取操作只发生了1次，并且Bulk Collect语句将所需数据一次性读入内存，所以该方法没有从磁盘读取数据块，所以这种方法比上述两种方法都具有优势，所以执行效率最高。

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

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

 

ps:简单记录下自己验证的结果

ps:按照例子跑了一遍，(我用的是9i)

      发现数据为10万条时，

          distinct 查询时间是：0.29 （多次查询后，时间会降低到0.05 seconds左右）

          游标查询时间是：0.27 （多次查询后，时间会升高到3.7 seconds左右）

          bulk collect 查询时间是：1.683

      数据为100万条时，

          distinct 查询时间是：9.083 seconds（多次查询后，时间会降低到5 seconds左右）

          游标查询时间是：2.945 seconds （多次查询后，时间会升高到3.2 seconds左右）

          bulk collect 查询时间是：18.506 seconds