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;

 

现在进行测试

SQL> set timing on;  
//设置打印执行时间
SQL> create table big_data as select level a,'b' b from dual connect by level<=2000000;
//创建表big_data并插入2000000条数据
表已创建。

已用时间:  00: 00: 00.82

SQL> declare
  2  cursor c_big is
  3  select * from big_data;
  4  v_big c_big%rowtype;
  5  begin
  6  open c_big;
  7  loop
  8  fetch c_big into v_big;
  9  exit when c_big%notfound;
 10  end loop;
 11  close c_big;
 12  end;
 13  /
PL/SQL 过程已成功完成。
已用时间:  00: 00: 07.30

SQL> declare
  2  cursor c_big is
  3  select * from big_data;
  4  type t_big is table of c_big%rowtype index by pls_integer;
  5  v_big t_big;
  6  begin
  7  open c_big;
  8  loop
  9  exit when c_big%notfound;
 10  fetch c_big bulk collect into v_big limit 10000;
 11  end loop;
 12  close c_big;
 13  end;
 14  /

PL/SQL 过程已成功完成。
已用时间:  00: 00: 00.38
SQL> declare
  2  v_big big_data%rowtype;
  3  begin
  4  for x in (select * from big_data) loop
  5  v_big := x;
  6  end loop;
  7  end;
  8  /

PL/SQL 过程已成功完成。
已用时间:  00: 00: 00.57
//可以看到三种游标遍历中第一种最耗时，只是遍历什么处理都没做就耗费了7秒多时间，因为它要fetch2000000次，而第二种批量获取
//这里设置每次获取10000条，也就获取200次，隐式获取其实获取每次获取多条数据
SQL> create table new_data as select * from big_data where 1=2;
//使用这个sql语句创建一个跟big_data表结构一样的表new_data，这样的建表方式只复制表结构，其他像主键、注释什么都不复制
表已创建。
已用时间:  00: 00: 00.00
//下面是给每种游标遍历加上一个插入表记录的操作
SQL> declare
  2  cursor c_big is
  3  select * from big_data;
  4  v_big c_big%rowtype;
  5  begin
  6  open c_big;
  7  loop
  8  fetch c_big into v_big;
  9  exit when c_big%notfound;
 10  insert /*+append*/ into new_data nologging (a,b) values(v_big.a,v_big.b);
 11  end loop;
 12  close c_big;
 13  end;
 14  /

PL/SQL 过程已成功完成。
已用时间:  00: 00: 35.46
SQL> truncate table new_data;

表被截断。
已用时间:  00: 00: 00.86
SQL> declare
  2  cursor c_big is
  3  select * from big_data;
  4  type t_big is table of c_big%rowtype index by pls_integer;
  5  v_big t_big;
  6  begin
  7  open c_big;
  8  loop
  9  exit when c_big%notfound;
 10  fetch c_big bulk collect into v_big limit 100000;
 11  begin
 12  forall i in 1 .. v_big.count
 13  insert /*+append*/ into new_data nologging (a,b) values(v_big(i).a,v_big(i).b);
 14  end;
 15  end loop;
 16  close c_big;
 17  end;
 18  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 01.83
SQL> truncate table new_data;

表被截断。
已用时间:  00: 00: 00.77
SQL> begin
  2  for x in(select * from big_data) loop
  3  insert /*+append*/ into new_data nologging (a,b) values(x.a,x.b);
  4  end loop;
  5  end;
  6  /

PL/SQL 过程已成功完成。
已用时间:  00: 00: 31.11
//可以看到这个差距就明显了，批量获取的游标方式只需要不到2秒钟，而单条获取和隐式游标都需要30几秒
//我们分析一下，因为获取次数过多而产生的时间差是固定的，因而这相差的30秒应该是因为插入语句执行次数
//而产生的差距，也就是说同意的插入语句、同样的数据，每次获取数量不同进行处理效率也会发生很大差距，
//也许换一种不受处理数量影响的操作语句，他们相差的时间还会是7秒左右
SQL> spool off