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;
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.