SUBSTR vs DBMS_LOB.SUBSTR

We have to choose the right built-in function when developing pl/sql applications. The following test case shows the performance outcome between SUBSTR and DBMS_LOB.SUBSTR functions. This test was carried out in Oracle 11g.

DECLARE
  V_TEXT   VARCHAR2(200) := 'This is test case between SUBSTR and DBMS_LOB.SUBSTR';
  V_CLOB   CLOB := V_TEXT;
  V_SUBSTR VARCHAR2(30);
  V_START NUMBER := 1;
  V_END NUMBER := 15;
  V_TIME NUMBER;
BEGIN

  V_TIME := DBMS_UTILITY.GET_TIME;
  FOR i IN 1..5000000 LOOP
    V_SUBSTR := SUBSTR(V_TEXT,1,15);
  END LOOP;
  dbms_output.put_line('SUBSTR VARCHAR2 => ' || ((DBMS_UTILITY.GET_TIME - V_TIME)/100));


  V_TIME := DBMS_UTILITY.GET_TIME;
  FOR i IN 1..5000000 LOOP
    V_SUBSTR := SUBSTR(V_CLOB,1,15);
  END LOOP;
  dbms_output.put_line('SUBSTR CLOB => '||((DBMS_UTILITY.GET_TIME - V_TIME)/100));  


  V_TIME := DBMS_UTILITY.GET_TIME;  
  FOR i IN 1..5000000 LOOP
    V_SUBSTR := DBMS_LOB.SUBSTR(V_CLOB,15,1);
  END LOOP;
  dbms_output.put_line('DBMS_LOB.SUBSTR with 15 - 1 => '||((DBMS_UTILITY.GET_TIME - V_TIME)/100));  


  V_TIME := DBMS_UTILITY.GET_TIME;  
  FOR i IN 1..5000000 LOOP
    V_SUBSTR := DBMS_LOB.SUBSTR(V_CLOB,15.0,1.0);
  END LOOP;
  dbms_output.put_line('(DBMS_LOB.SUBSTR with 15.0 - 1.0) => ' || ((DBMS_UTILITY.GET_TIME - V_TIME)/100)); 
   

  V_TIME := DBMS_UTILITY.GET_TIME;
  FOR i IN 1..5000000 LOOP
    V_SUBSTR := DBMS_LOB.SUBSTR(V_CLOB,cast(15 as number), cast(1 as number));
  END LOOP;
  dbms_output.put_line('DBMS_LOB.SUBSTR with casts => '||((DBMS_UTILITY.GET_TIME - V_TIME)/100));  
  
END;

Test outcome

As you can see from the outcome of the above code. How the performance changes with different data types. As a result, if you use the CLOB data type, DBMS_LOB.SUBSTR function will gain better performance. Moreover, can you notice the difference when use number as a parameter instead of integer. It finishes 0.17 seconds. However, it takes 11.44 seconds when integers are used. Almost 68 times more time consuming. So, it is very important to use the DBMS_LOB.SUBSTR function with the correct data types.

Leave a Reply

Your email address will not be published.