ORACLE数据库中关于游标和记录表的速度测试

1 、问题提出

      在我们日常的数据库操作尤其是存储过程设计中，经常有记录表之类的集合、显示游标等操作，但这两种数据处理方式在速度及性能上有什么区别呢？

2 、测试方法

    在 ORACLE 数据库中创建一个数据表，然后对创建的数据表分别插入 100 条、 1000 条、 10000 条、 100000 条数据，最后对这些数据分别用游标和记录表执行相同功能的操作 10 次，计算平均消耗的系统时间。

（ 1 ）创建数据表

create table  table_test
(
 COL1  INTEGER,
 COL2  VARCHAR2(20)
)

（ 2 ）插入数据（以 100000 条数据为例）

-- 循环 100000 次添加
DECLARE
 i     INTEGER;
BEGIN
 FOR i IN 1..100000 LOOP
     INSERT INTO TABLE_TEST(COL1,COL2)
     VALUES(i,'ADD'||i);
 END LOOP;
 COMMIT;
 EXCEPTION
   WHEN OTHERS THEN
   ROLLBACK;
END;

（ 3 ）游标操作

-- 游标处理
DECLARE
 v_COL1   TABLE_TEST.COL1%TYPE;
 v_COL2   TABLE_TEST.COL2%TYPE;
 v_BEGIN  INTEGER;
 v_END    INTEGER;
 CURSOR CUR_TYPE IS
 SELECT COL1,COL2 FROM TABLE_TEST;
BEGIN
 SELECT DBMS_UTILITY.get_time INTO v_BEGIN
 FROM   DUAL;
 DBMS_OUTPUT.put_line(' 开始时间： '||v_BEGIN);
  IF CUR_TYPE%ISOPEN = FALSE then
  OPEN CUR_TYPE;
 END IF;
 FETCH CUR_TYPE INTO v_COL1,v_COL2;
 WHILE CUR_TYPE%FOUND
 LOOP
  FETCH CUR_TYPE INTO v_COL1,v_COL2;
 END LOOP;
 CLOSE CUR_TYPE;   
 SELECT DBMS_UTILITY.get_time INTO v_END
 FROM   DUAL;
  DBMS_OUTPUT.put_line(' 结束时间： '||v_END);
 DBMS_OUTPUT.put_line(' 开始时间 '||v_BEGIN||' ，结束时间 '||v_END||' 消耗时间 '||TO_CHAR(v_END-v_BEGIN));
 DBMS_OUTPUT.put_line(' 消耗时间： '||TO_CHAR(v_END-v_BEGIN));
END;

（ 4 ）记录表操作

-- 记录表处理
DECLARE
 v_COL1   TABLE_TEST.COL1%TYPE;
 v_COL2   TABLE_TEST.COL2%TYPE;
 v_BEGIN  INTEGER;
 v_END    INTEGER;
 i     INTEGER;
 TYPE TYPE_TABLE_TEST IS TABLE OF TABLE_TEST%ROWTYPE
 INDEX BY BINARY_INTEGER;
 V_TABLE_TEST   TYPE_TABLE_TEST;
BEGIN
 SELECT DBMS_UTILITY.get_time INTO v_BEGIN
 FROM   DUAL;
 DBMS_OUTPUT.put_line(' 开始时间： '||v_BEGIN);
 SELECT COL1,COL2 BULK COLLECT INTO V_TABLE_TEST
 FROM TABLE_TEST;
 FOR i IN 1..V_TABLE_TEST.COUNT LOOP
   v_COL1 := V_TABLE_TEST(i).COL1;
   v_COL2 := V_TABLE_TEST(i).COL2;
 END LOOP;
 SELECT DBMS_UTILITY.get_time INTO v_END
 FROM   DUAL;
  DBMS_OUTPUT.put_line(' 结束时间： '||v_END);
 DBMS_OUTPUT.put_line(' 开始时间 '||v_BEGIN||' ，结束时间 '||v_END||' 消耗时间 '||TO_CHAR(v_END-v_BEGIN));
 DBMS_OUTPUT.put_line(' 消耗时间： '||TO_CHAR(v_END-v_BEGIN));
END;

（ 5 ）耗时统计（单位：毫秒）

100 条数据

游标	记录表
开始时间991567 ，结束时间：991567 消耗时间：0	开始时间1002253 ，结束时间1002253 消耗时间0
开始时间992690 ，结束时间：992690 消耗时间：0	开始时间1004351 ，结束时间1004351 消耗时间0
开始时间993682 ，结束时间：993682 消耗时间：0	开始时间1006856 ，结束时间1006856 消耗时间0
开始时间994570 ，结束时间：994570 消耗时间：0	开始时间1008770 ，结束时间1008770 消耗时间0
开始时间995460 ，结束时间：995460 消耗时间：0	开始时间1009632 ，结束时间1009632 消耗时间0
开始时间996237 ，结束时间：996237 消耗时间：0	开始时间1010445 ，结束时间1010445 消耗时间0
开始时间997139 ，结束时间：997139 消耗时间：0	开始时间1011326 ，结束时间1011326 消耗时间0
开始时间997945 ，结束时间：997945 消耗时间：0	开始时间1012121 ，结束时间1012121 消耗时间0
开始时间998742 ，结束时间：998742 消耗时间：0	开始时间1012979 ，结束时间1012979 消耗时间0
开始时间999590 ，结束时间：999592 消耗时间：2	开始时间1013698 ，结束时间1013698 消耗时间0
平均： 0.2	平均： 0

1000 条数据

游标	记录表
开始时间966807 ，结束时间：966809 消耗时间：2	开始时间978254 ，结束时间：978254 消耗时间：0
开始时间967942 ，结束时间：967942 消耗时间：0	开始时间979382 ，结束时间：979384 消耗时间：2
开始时间968885 ，结束时间：968885 消耗时间：0	开始时间980384 ，结束时间：980384 消耗时间：0
开始时间969792 ，结束时间：969792 消耗时间：0	开始时间981201 ，结束时间：981201 消耗时间：0
开始时间971296 ，结束时间：971296 消耗时间：0	开始时间982025 ，结束时间：982025 消耗时间：0
开始时间972254 ，结束时间：972256 消耗时间：2	开始时间982854 ，结束时间：982854 消耗时间：0
开始时间973057 ，结束时间：973059 消耗时间：2	开始时间983643 ，结束时间：983643 消耗时间：0
开始时间974062 ，结束时间：974062 消耗时间：0	开始时间984426 ，结束时间：984426 消耗时间：0
开始时间974917 ，结束时间：974918 消耗时间：1	开始时间985315 ，结束时间：985315 消耗时间：0
开始时间975850 ，结束时间：975850 消耗时间：0	开始时间986057 ，结束时间：986057 消耗时间：0
平均： 0.7	平均： 0.2

10000 条数据

游标	记录表
开始时间932771 ，结束时间：932779 消耗时间：8	开始时间949053 ，结束时间：949056 消耗时间：3
开始时间933851 ，结束时间：933857 消耗时间：6	开始时间950020 ，结束时间：950021 消耗时间：1
开始时间934981 ，结束时间：934987 消耗时间：6	开始时间950829 ，结束时间：950831 消耗时间：2
开始时间936012 ，结束时间：936020 消耗时间：8	开始时间951704 ，结束时间：951707 消耗时间：3
开始时间936992 ，结束时间：937000 消耗时间：8	开始时间952473 ，结束时间：952475 消耗时间：2
开始时间937828 ，结束时间：937835 消耗时间：7	开始时间953284 ，结束时间：953285 消耗时间：1
开始时间938629 ，结束时间：938635 消耗时间：6	开始时间954056 ，结束时间：954057 消耗时间：1
开始时间939496 ，结束时间：939503 消耗时间：7	开始时间954984 ，结束时间：954985 消耗时间：1
开始时间940395 ，结束时间：940401 消耗时间：6	开始时间955715 ，结束时间：955717 消耗时间：2
开始时间941409 ，结束时间：941415 消耗时间：6	开始时间956557 ，结束时间：956559 消耗时间：2
平均： 6.8	平均： 1.8

100000 条数据

游标	记录表
开始时间1018625 ，结束时间1018693 消耗时间68	开始时间1031904 ，结束时间1031925 消耗时间21
开始时间1019901 ，结束时间1019970 消耗时间69	开始时间1032773 ，结束时间1032792 消耗时间19
开始时间1020903 ，结束时间1020971 消耗时间68	开始时间1033553 ，结束时间1033570 消耗时间17
开始时间1021798 ，结束时间1021867 消耗时间69	开始时间1034384 ，结束时间1034403 消耗时间19
开始时间1022864 ，结束时间1022934 消耗时间70	开始时间1035215 ，结束时间1035234 消耗时间19
开始时间1023901 ，结束时间1023970 消耗时间69	开始时间1036065 ，结束时间1036084 消耗时间19
开始时间1024706 ，结束时间1024776 消耗时间70	开始时间1037067 ，结束时间1037085 消耗时间18
开始时间1025587 ，结束时间1025656 消耗时间69	开始时间1037834 ，结束时间1037853 消耗时间19
开始时间1026432 ，结束时间1026501 消耗时间69	开始时间1038617 ，结束时间1038635 消耗时间18
开始时间1027259 ，结束时间1027329 消耗时间70	开始时间1039598 ，结束时间1039617 消耗时间19
平均： 69.1	平均： 18.8

 

3 、问题现象描述

     ORACLE 数据库将管理内存区分为系统全局区 SGA （ System Global Area ）、程序全局区 PGA （ Process Global Area ）、用户全局区 UGA （ User Global Area ）。

     记录表之类的集合类型在处理时以管道的形式从程序全局区 PGA 中读取数据，游标是以集合的方式从全局区 SGA 中读取数据，这样在数据处理时，游标就比记录表多了一些中间环节。

     由上述测试结果可以看出：

    当使用静态数据表或表结构相对简单时，使用记录表要比游标在速度上提高 2 ～ 4 倍，但由于记录表对系统的内存要求较高，当单列数据长度过大时，建议不使用记录表。

    另外，并不是所有的宿主语言都支持记录表之类的集合操作，为增强系统的可移植性，建议在需要用 PL/SQL 环境中的数据返回到宿主语言环境（如 DELPHI ）中时，采用游标的方式返回数据。