获取LOB长度的两种方法效率对比

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

可以处理LOB字段的常用字符函数：http://yangtingkun.itpub.net/post/468/500648

 

 

建立一个测试表：

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程序实现的。所以二者效率上有所区别是正常的，而且对于数据量不大的情况，这种区别并不明显。

 

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

转载于:http://blog.itpub.net/4227/viewspace-664932/