SUBSTR在CLOB上的性能

Question

我有一个PL/SQL过程，它对varchar2参数执行大量substrs。我想删除长度限制，所以尝试将其更改为clob。

工作正常，但性能受到影响，所以我做了一些测试（基于2005年的这些测试）。

更新：我可以用不同的Oracle版本和不同的硬件在几个不同的实例上重现这个过程，dbms_lob.substr总是明显比substr(CLOB)慢，而且比substr(VARCHAR2)慢得多。

Bob的结果和上面链接中的测试讲述了一个不同的故事。

有人能解释这一点吗，或者至少重现鲍勃或我的结果吗？谢了！

测试结果：

DECLARE
  l_text   VARCHAR2(30) := 'This is a test record';
  l_clob   CLOB := l_text;
  l_substr VARCHAR2(30);
  t TIMESTAMP;
BEGIN
  t := SYSTIMESTAMP;
  FOR i IN 1..100000 LOOP
    l_substr := SUBSTR(l_text,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (VARCHAR2)');

  t := SYSTIMESTAMP;
  FOR i IN 1..100000 LOOP
    l_substr := SUBSTR(l_clob,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (CLOB SUBSTR)');

  t := SYSTIMESTAMP;
  FOR i IN 1..100000 LOOP
    l_substr := DBMS_LOB.SUBSTR(l_clob,14,1);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (DBMS_LOB.SUBSTR)');
END;

Answer 1

（谎言，该死的谎言，还有基准……)

我重新运行了您的测试10次，将字符串扩展为整整30个字符，得到了以下平均结果：

+000000000 00:00:00.011694200 (VARCHAR2)
+000000000 00:00:00.901000600 (CLOB SUBSTR)
+000000000 00:00:00.013169200 (DBMS_LOB.SUBSTR)

然后，我将子字符串范围更改为5,14（对于dbms_lob.substr，为14,5）并得到：

+000000000 00:00:00.011731000 (VARCHAR2)
+000000000 00:00:01.010840000 (CLOB SUBSTR)
+000000000 00:00:00.011427000 (DBMS_LOB.SUBSTR)

+000000000 00:00:00.013578900 (VARCHAR2)
+000000000 00:00:00.964527400 (CLOB SUBSTR)
+000000000 00:00:00.011416800 (DBMS_LOB.SUBSTR)

+000000000 00:00:00.011210200 (VARCHAR2)
+000000000 00:00:00.916439800 (CLOB SUBSTR)
+000000000 00:00:00.013781300 (DBMS_LOB.SUBSTR)

我的结论是，在处理CLOB时，最好使用dbms_lob.SUBSTR，因为与使用SUBSTR处理“普通”VARCHAR2相比，它似乎没有任何性能损失。针对CLOB的SUBSTR似乎遭受了重大的性能损失。对于记录-OS=HP/UX（Unix变体），Oracle Version=11.1，Processor=HP Itanium 2-plex。YMMV.

分享和享受。

因为如果值得做就值得过度做，这里有一些字符串扩展到32767个字符的结果。每组结果给出的子字符串范围：

1, 25000
+000000000 00:00:00.198466400 (VARCHAR2)
+000000000 00:00:02.870958700 (CLOB SUBSTR)
+000000000 00:00:00.174490100 (DBMS_LOB.SUBSTR)

1000, 25000
+000000000 00:00:00.253447900 (VARCHAR2)
+000000000 00:00:02.491790500 (CLOB SUBSTR)
+000000000 00:00:00.193560100 (DBMS_LOB.SUBSTR)

10000, 25000
+000000000 00:00:00.217812000 (VARCHAR2)
+000000000 00:00:02.268794800 (CLOB SUBSTR)
+000000000 00:00:00.222200200 (DBMS_LOB.SUBSTR)

（亲爱的朋友们，再一次向缺口走去，再一次……)

重新运行基准测试，将CLOB的大小更改为3276700，并从中间开始从2475000取子字符串，长度为25000，我得到：

+000000000 00:00:00.176883200 (VARCHAR2)
+000000000 00:00:02.069482600 (CLOB SUBSTR)
+000000000 00:00:00.175341500 (DBMS_LOB.SUBSTR)

（注意更改只影响最后两个测试）。