Efficiency comparisons between two methods of Oracle acquiring LOB lengths

Two days ago, I wrote an article about a series of regular functions such as length to handle LOB types, and someone mentioned in the blog reply that using length is more efficient than dbms_lob. GetLength Low, here's a simple test.

Create a test table:

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;

Ten end;

11/

Pl/sql procedure successfully completed.

There is a single piece of data in the table, using length and Dbms_lob, respectively, in Pl/sql. The GetLength cycle gets the length of the comparison between the two efficiency:

Sql> SET TIMING on

Sql> DECLARE

2 V_num number;

3 BEGIN

4 for I in 1..10000 LOOP

5 SELECT LENGTH (CONTENTS) into the 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

This column more highlights: http://www.bianceng.cn/database/Oracle/

5 SELECT Dbms_lob. GetLength (CONTENTS) into the 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 the 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 the v_num from T_lob;

6 End LOOP;

7 End;

8/

Pl/sql procedure successfully completed.

elapsed:00:00:06.06

As you can see, the time required to use length is less than Dbms_lob. GetLength's 2/3.

This is the Pl/sql code, and the following tests the efficiency difference between the two in the SQL statement:

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

Because 1000 records are too few to see the needs of both, so the query contains the T_lob table two times, in order to avoid the impact of a large number of data returned to the client, set Autotrace to Traceonly STATISTICS:

sql> SET Autot TRACE STAT

Sql> SELECT LENGTH (a.contents) from T_lob A, T_lob;