前两天写了一篇文章,介绍LENGTH等一系列的常规函数一样可以处理LOB类型,有人在BLOG回复中提到,使用LENGTH的效率要比DBMS_LOB.GETLENGTH低,这里进行简单的测试。


建立一个测试表:

SQL> CREATE TABLE T_LOB (ID NUMBER, CONTENTS CLOB);

Table created.

SQL> DECLARE

 2     V_LOB CLOB;

 3  BEGIN

 4        INSERT INTO T_LOB

 5     VALUES (1, EMPTY_CLOB())

 6     RETURN  CONTENTS INTO V_LOB;

 7     FOR I IN 1..100 LOOP

 8             DBMS_LOB.WRITEAPPEND(V_LOB, 32767, LPAD('A', 32767, 'A'));

 9     END LOOP;

10  END;

11  /

PL/SQL procedure successfully completed.

表中有一条数据,下面在PL/SQL中分别使用LENGTH和DBMS_LOB.GETLENGTH循环获取长度对比二者的效率:

SQL> SET TIMING ON

SQL> DECLARE

 2  V_NUM NUMBER;

 3  BEGIN

 4  FOR I IN 1..10000 LOOP

 5  SELECT LENGTH(CONTENTS) INTO V_NUM FROM T_LOB;

 6  END LOOP;

 7  END;

 8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.38

SQL> DECLARE

 2  V_NUM NUMBER;

 3  BEGIN

 4  FOR I IN 1..10000 LOOP

 5  SELECT DBMS_LOB.GETLENGTH(CONTENTS) INTO V_NUM FROM T_LOB;

 6  END LOOP;

 7  END;

 8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.60

SQL> DECLARE

 2  V_NUM NUMBER;

 3  BEGIN

 4  FOR I IN 1..100000 LOOP

 5  SELECT LENGTH(CONTENTS) INTO V_NUM FROM T_LOB;

 6  END LOOP;

 7  END;

 8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.82

SQL> DECLARE

 2  V_NUM NUMBER;

 3  BEGIN

 4  FOR I IN 1..100000 LOOP

 5  SELECT DBMS_LOB.GETLENGTH(CONTENTS) INTO V_NUM FROM T_LOB;

 6  END LOOP;

 7  END;

 8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.06

可以看到,使用LENGTH所需的时间不到DBMS_LOB.GETLENGTH的2/3。

这是PL/SQL代码,下面测试一下SQL语句中二者的效率区别:

SQL> INSERT INTO T_LOB

 2  SELECT A.*  

 3  FROM T_LOB A, ALL_OBJECTS B

 4  WHERE ROWNUM < 1000;

999 rows created.

Elapsed: 00:01:45.69

SQL> SELECT COUNT(*) FROM T_LOB;

 COUNT(*)

----------

     1000

Elapsed: 00:00:00.00

SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.00

由于1000条记录太少,不足以看出二者的需求,因此查询中包含了T_LOB表两次,为了避免大量数据返回客户端带来的影响,设置AUTOTRACE为TRACEONLY STATISTICS:

SQL> SET AUTOT TRACE STAT

SQL> SELECT LENGTH(A.CONTENTS) FROM T_LOB A, T_LOB;

1000000 rows selected.

Elapsed: 00:00:06.03

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

      1017  consistent gets

         0  physical reads

         0  redo size

  17200447  bytes sent via SQL*Net to client

    733818  bytes received via SQL*Net from client

     66668  SQL*Net roundtrips to/from client

         1  sorts (memory)

         0  sorts (disk)

   1000000  rows processed

SQL> SELECT DBMS_LOB.GETLENGTH(A.CONTENTS) FROM T_LOB A, T_LOB;

1000000 rows selected.

Elapsed: 00:00:15.12

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

      1017  consistent gets

         0  physical reads

         0  redo size

  17200459  bytes sent via SQL*Net to client

    733818  bytes received via SQL*Net from client

     66668  SQL*Net roundtrips to/from client

         1  sorts (memory)

         0  sorts (disk)

   1000000  rows processed

这次区别更加明显,LENGTH所用时间只有DBMS_LENGTH.GETLENGTH的2/5。显然LENGTH的效应要比DBMS_LENGTH.GETLENGTH更高。

其实这也不难理解,LENGTH的实现是Oracle通过外部C程序实现的。而DBMS_LOB包是通过PL/SQL程序实现的。所以二者效率上有所区别是正常的,而且对于数据量不大的情况,这种区别并不明显。



oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html