问题描述
我有一个 PL/SQL 过程，它在 VARCHAR2 参数上执行了很多 SUBSTR 操作.我想去掉长度限制，所以我试着把它改成CLOB.
I have a PL/SQL procedure that does a lot of SUBSTRs on a VARCHAR2 parameter. I would like to remove the length limit, so I tried to change it to CLOB.
工作正常，但性能受到影响，所以我做了一些测试(基于 这些2005 年的测试).
Works fine, but performance suffers, so I did some tests (based on these tests from 2005).
更新:我可以在具有不同 Oracle 版本和不同硬件的多个不同实例上重现这一点，dbms_lob.substr 总是明显比 substr(CLOB) 慢，比 SUBSTR(VARCHAR2) 慢很多.
UPDATE: I can reproduce this on several different instances with different Oracle versions and different hardware, dbms_lob.substr is always noticeable slower than substr(CLOB), and a lot slower than SUBSTR(VARCHAR2).
Bob 的结果和上面链接中的测试讲述了一个不同的故事.
Bob's results and the tests in the link above tell a different story.
谁能解释一下，或者至少重现鲍勃或我的结果?谢谢！
Can anyone explain this, or at least reproduce either Bob's or my results? Thanks!
测试结果:
+000000000 00:00:00.004000000 (VARCHAR2)
+000000000 00:00:00.298000000(CLOB SUBSTR)
+000000000 00:00:00.356000000 (DBMS_LOB.SUBSTR)
测试代码:
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;
推荐答案
(谎言，该死的谎言，和基准...)
(Lies, damn lies, and benchmarks...)
我重新运行了您的测试 10 次，将字符串扩展到整整 30 个字符，并得到以下平均结果:
I re-ran your test 10 times, expanding the string so it was a full 30 characters long, and got the following averaged results:
+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)并得到:
I then changed the substring range to 5,14 (14,5 for DBMS_LOB.SUBSTR) and got:
+000000000 00:00:00.011731000 (VARCHAR2)
+000000000 00:00:01.010840000 (CLOB SUBSTR)
+000000000 00:00:00.011427000 (DBMS_LOB.SUBSTR)
然后我将范围更改为 17,14(DBMS_LOB.SUBSTR 为 14,17)并得到
I then changed the range to 17,14 (14,17 for DBMS_LOB.SUBSTR) and got
+000000000 00:00:00.013578900 (VARCHAR2)
+000000000 00:00:00.964527400 (CLOB SUBSTR)
+000000000 00:00:00.011416800 (DBMS_LOB.SUBSTR)
最后，我将范围更改为 25,14(DBMS_LOB.SUBSTR 为 14,25)并得到
Finally, I changed the range to 25,14 (14,25 for DBMS_LOB.SUBSTR) and got
+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，因为与针对正常"VARCHAR2 使用 SUBSTR 相比，它似乎实际上没有性能损失.针对 CLOB 的 SUBSTR 似乎遭受了显着的性能损失.作为记录 - 操作系统 = HP/UX(Unix 变体)，Oracle 版本 = 11.1，处理器 =HP Itanium 2-plex.天啊.
My conclusion is that when working against CLOB's it's best to use DBMS_LOB.SUBSTR as it appears to have effectively no performance penalty compared to using SUBSTR against a "normal" VARCHAR2. SUBSTR against a CLOB seems to suffer from a significant performance penalty. For the record - OS = HP/UX (Unix variant), Oracle version=11.1, processor=HP Itanium 2-plex. YMMV.
分享和享受.
而且因为如果值得做就值得过度做，这里有更多的结果，字符串扩展到 32767 个字符.每组结果给出的子串范围:
And because if it's worth doing it's worth over-doing, here's some more results with the strings expanded to 32767 characters. Substring ranges given with each set of results:
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)
同一天，同样的结论.
克苏鲁 fhtagn.
Cthulhu fhtagn.
(再次违反，亲爱的朋友们，再次......)
(Once more unto the breach, dear friends, once more...)
重新运行基准测试，将 CLOB 的大小更改为 3276700，并从中间取子串，从 2475000 开始，长度为 25000，我得到:
Re-ran the benchmarks, changing the size of the CLOB to 3276700, and taking the substring from the middle starting at 2475000 for length 25000 I get:
+000000000 00:00:00.176883200 (VARCHAR2)
+000000000 00:00:02.069482600 (CLOB SUBSTR)
+000000000 00:00:00.175341500 (DBMS_LOB.SUBSTR)
(请注意，更改仅影响最后两个测试).
(Note that changes only affect the last two tests).
AND...相同的结果，不同的日子.
AND...same results, different day.
天啊.
这篇关于SUBSTR 在 CLOB 上的表现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！