当前位置：首页 -> AI编程基础 -> 数据库编程

pl/sql中三种游标循环效率对比(一)

2014-11-24 15:53:18 · 作者: · 浏览: 0
标签: pl/sql 游标 循环 效率 对比
pl/sql中三种游标循环效率对比
这里主要对比以下三种格式的游标循环: 
1.单条处理
open 游标;
LOOP  
FETCH 游标 INTO 变量;
EXIT WHEN  条件;
END LOOP;
CLOSE 游标;

2.批量处理
open 游标;
FETCH 游标 BULK COLLECT INTO 集合变量;
CLOSE 游标;

3.隐式游标
for x in (sql语句) loop
...--逻辑处理
end loop;

以上为工作中常见的几种游标处理方式,一般来说批量处理的速度要最好,隐式游标的次之,单条处理的最差,但是在我的实际工作中发现大部分使用的还是第一种游标处理。
归其原因竟是对集合变量及批量处理的效率等问题不了解所致。
这里简单的测试一下以上三种游标的效率,并分析trace文件来查看这3种处理方式的本质。 
--创建测试大表
[sql] 
00:09:54 SCOTT@orcl> create table big_data as select 'Cc'||mod(level,8) a,'Dd'||  
mod(level,13) b from dual connect by level<1000000;  
  
Table created.  
  
Elapsed: 00:00:05.87  
00:11:17 SCOTT@orcl> select count(*) from big_data;  
  
  COUNT(*)  
----------  
    999999  
  
1 row selected.  
  
Elapsed: 00:00:00.07  

--分别执行以上三种方式的游标处理的plsql块
[sql] 
00:11:21 SCOTT@orcl> declare  
00:17:54   2    cursor c_a is  
00:17:54   3      select a from big_data;  
00:17:54   4  
00:17:54   5    v_a big_data.a%type;  
00:17:54   6  begin  
00:17:54   7    open c_a;  
00:17:54   8    loop  
00:17:54   9      fetch c_a into v_a;  
00:17:54  10      exit when c_a%notfound;  
00:17:54  11    end loop;  
00:17:54  12    close c_a;  
00:17:54  13  end;  
00:17:56  14  /  
  
PL/SQL procedure successfully completed.  
  
Elapsed: 00:00:07.42  
00:18:05 SCOTT@orcl> declare  
00:19:56   2    cursor c_a is  
00:19:56   3      select a from big_data;  
00:19:56   4  
00:19:56   5  type t_a is table of c_a%rowtype;  
00:19:56   6    v_a t_a;  
00:19:56   7  begin  
00:19:56   8    open c_a;  
00:19:56   9    --批量处理  
00:19:56  10      fetch c_a bulk collect into v_a;  
00:19:56  11    close c_a;  
00:19:56  12  end;  
00:19:57  13  /  
  
PL/SQL procedure successfully completed.  
  
Elapsed: 00:00:00.64  
00:22:55 SCOTT@orcl>






 declare  
00:23:18   2    v_a big_data.a%type;  
00:23:18   3    begin  
00:23:18   4    --批量处理  
00:23:18   5    for x in (select a from big_data) loop  
00:23:18   6      v_a:=x.a;  
00:23:18   7    end loop;  
00:23:18   8  end;  
00:23:18   9  /  
  
PL/SQL procedure successfully completed.  
  
Elapsed: 00:00:00.79

注意对比消耗时间,1为7.42s, 2为0.64s, 3为0.79s
在执行pl/sql块之前,需要执行语句: alter session set sql_trace=true;
以便之后查看trace文件.
第一个游标方式的trace文件如下:(单条处理) 
PARSING IN CURSOR #7 len=181 dep=0 uid=84 oct=47 lid=84 tim=1357453194221500 hv=4093379502 ad='3ab9f6ec' sqlid='3nz96vvtzs0xf'
declare
  cursor c_a is
    select a from big_data;
  v_a big_data.a%type;
begin
  open c_a;
  loop
    fetch c_a into v_a;
    exit when c_a%notfound;
  end loop;
  close c_a;
end;
END OF STMT
PARSE #7:c=7998,e=8406,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1357453194221495
=====================
PARSING IN CURSOR #4 len=444 dep=2 uid=84 oct=3 lid=84 tim=1357453194225811 hv=1611503607 ad='3ab64c10' sqlid='c7tu1h9h0v5zr'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("BIG_DATA") FULL("BIG_DATA") NO_PARALLEL_INDEX("BIG_DATA") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "BIG_DATA" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5") SEED (:"SYS_B_6") "BIG_DATA") SAMPLESUB
END OF STMT
PARSE #4:c=2000,e=1958,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=0,tim=1357453194225807
*** 2013-01-06 14:19:54.284
EXEC #4:c=3998,e=58289,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=3098652591,tim=1357453194284371
FETCH #4:c=18997,e=19074,p=0,cr=55,cu=0,mis=0,r=1,dep=2,og=1,plh=3098652591,tim