title: Performance of SUBSTR on CLOB
body: <p>I have a PL/SQL procedure that does a lot of <code>SUBSTR</code>s on a <code>VARCHAR2</code> parameter. I would like to remove the length limit, so I tried to change it to <code>CLOB</code>.</p>
  
  <p>Works fine, but performance suffers, so I did some tests (based on <a href="http://www.orafaq.com/forum/mv/msg/47418/123773/0/#msg_123773">these tests</a> from 2005).</p>
  
  <hr>
  
  <p><strong>UPDATE</strong>: I can reproduce this on several different instances with different Oracle versions and different hardware, <code>dbms_lob.substr</code> is always noticeable slower than <code>substr(CLOB)</code>, and a lot slower than <code>SUBSTR(VARCHAR2)</code>.</p>
  
  <p>Bob's results and the tests in the link above tell a different story.</p>
  
  <p>Can anyone explain this, or at least reproduce either Bob's or my results? Thanks!</p>
  
  <hr>
  
  <p><strong>Test results:</strong></p>
  
  <blockquote>
    <p>+000000000 00:00:00.<strong>004000000</strong> (VARCHAR2)<br>
    +000000000 00:00:00.<strong>298000000</strong> (CLOB SUBSTR)<br>
    +000000000 00:00:00.<strong>356000000</strong> (DBMS_LOB.SUBSTR)  </p>
  </blockquote>
  
  <p><strong>Test code:</strong></p>
  
  <pre><code>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;
  </code></pre>