显示游标与隐式游标的比较

这两种游标的使用也是经常关注的焦点，我也翻阅了一些资料，简单总结一下关于这两个的比较。

简单说来就是：在性能方面，采用隐式游标 >= 采用显示游标，但最好还是结合实际情况来考虑。

在现在9i以及10g版本中，已经引入了预取的概念，即当调用预取操作用于从游标中读取一行时，下一行也在统一调用中获得。数据库预测，你通常都会获取下一行，对于隐式游标来说，这个很重要，因为单一的读取调用需要完全估计程序调用的适当响应时间，也就是说，返回读取的行或引发NO_DATA_FOUND、TOO_MANY_ROWS的错误都是由单次读取决定的。

分3组验证，1）单行的读取   2）top-n 处理

1) 单行的读取：

测试第一组：

创建单行索引组织表用于测试。

SQL> create table one_row_tb(x primary key)
  2  organization index as select 1 from dual;

表已创建。

SQL> create or replace procedure implicit is
  2  dummy number;
  3  begin
  4  for i in 1..50000 loop
  5  select 1 into dummy
  6  from one_row_tb;
  7  end loop;
  8  end;
  9  /

过程已创建。

SQL> set timing on
SQL> create or replace procedure explicit is
  2    cursor explicit_cur is
  3     select 1 from one_row_tb;
  4  dummy number;
  5  begin
  6    for i in 1..50000 loop
  7      open explicit_cur;
  8      fetch explicit_cur into dummy;
  9      close explicit_cur;
 10    end loop;
 11  end;
 12  /

过程已创建。

已用时间:  00: 00: 00.04
SQL> exec implicit;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 03.53
SQL> exec explicit;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 04.46

结果：隐式游标  优于  显示游标。

可讨论的地方：在使用显示游标的时候，可以将 open 游标和close 游标放到loop外，这样时间就大大减少了。但这样一来，其实在内部loop循环内，只执行了一次（用游标读取了一次记录，因为只有一条，游标在向下一条记录移动的时候，已经找不到了）。

测试第二组：

SQL> create table sec_group_tb(x number,y char(100));

表已创建。

已用时间:  00: 00: 00.01
SQL> insert into sec_group_tb
  2  select rownum,'padding' from all_objects
  3  where rownum < 10001;

已创建10000行。

已用时间:  00: 00: 00.65
SQL> commit;

提交完成。

已用时间:  00: 00: 00.00
SQL> create or replace procedure implicit is
  2  dummy number;
  3  begin
  4  for i in 1..50000 loop
  5  select 1 into dummy
  6  from sec_group_tb
  7  where x = 1;
  8  end loop;
  9  end;
 10  /

过程已创建。

已用时间:  00: 00: 00.14
SQL> create or replace procedure explicit is
  2    cursor explicit_cur is
  3     select 1 from sec_group_tb where x =1;
  4  dummy number;
  5  begin
  6    for i in 1..50000 loop
  7      open explicit_cur;
  8      fetch explicit_cur into dummy;
  9      close explicit_cur;
 10    end loop;
 11  end;
 12  /

过程已创建。

已用时间:  00: 00: 00.03
SQL> exec implicit

PL/SQL 过程已成功完成。

已用时间:  00: 01: 48.45
SQL> exec explicit

PL/SQL 过程已成功完成。

已用时间:  00: 00: 06.15

结果：显示游标 优于 隐式游标。

因为在显示游标使用的procedure中，dummy是一个单值变量，所以只能接受一个值，这样一来，在用显示游标读取记录时，只返回找到结果中的第一条即可，不用在扫描表中其余行记录，而在使用隐式游标时，刚好相反，因为会同时预取第二条记录，所以在这种情况下，隐式游标的使用不得不扫描整张表，为了“获得”第二条记录，所以时间相差很大。

由于这个例子比较特殊，如果查找的不是第一条记录，而是最后一条，那么用显示游标也需扫描整张表，此时两者时间相差无几，还有一种情况，就是既然已经明确只需要一条记录，则可以显示给出限制条件，即

将implicit过程中sql语句换成：select 1 from sec_group_tb where x = 1 and rownum =1 ;

将explicit过程中sql语句换成：select 1 into dummy from sec_group_tb where x = 1 and rownum =1 ;

此时两者运行时间相差不大。

再次得出结论：两者相差不大。

3）top-n

SQL> create or replace procedure implicit is
  2  dummy number;
  3  begin
  4  for i in 1..500 loop
  5  select x into dummy
  6  from
  7  ( select * from sec_group_tb
  8       order by y desc)
  9  where rownum = 1;
 10  end loop;
 11  end;
 12  /

过程已创建。

已用时间:  00: 00: 00.04
SQL> create or replace procedure explicit is
  2    cursor explicit_cur is
  3     select 1 from sec_group_tb order by y desc;
  4  dummy number;
  5  begin
  6    for i in 1..500 loop
  7      open explicit_cur;
  8      fetch explicit_cur into dummy;
  9      close explicit_cur;
 10    end loop;
 11  end;
 12  /

过程已创建。

已用时间:  00: 00: 00.03
SQL> exec implicit

PL/SQL 过程已成功完成。

已用时间:  00: 00: 04.87
SQL> exec explicit

PL/SQL 过程已成功完成。

已用时间:  00: 00: 07.48

结果：隐式游标 优于 显示游标。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10037372/viewspace-663403/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10037372/viewspace-663403/