oracle dbms_lob.getlength,获取LOB长度的两种方法效率对比

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