OracleBrains.Com header image 2

PL/SQL Tuning::Using SELECT INTO OR FOR LOOP Part-2

August 14th, 2007 by Rajender Singh · No Comments

Let me share with you guys new test which I did:

I again created following two functions, this time without exception:

CREATE OR REPLACE FUNCTION example1 RETURN VARCHAR2
IS
i VARCHAR2(10);
BEGIN
BEGIN
SELECT ‘x’ INTO i FROM DUAL;
RETURN i;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN null;
END;
END example1;

CREATE OR REPLACE FUNCTION example2 RETURN VARCHAR2
IS
BEGIN
FOR c1 IN (SELECT ‘x’ col1 FROM DUAL) LOOP
RETURN c1.col1;
END LOOP;
RETURN NULL;
END example2;

Then I ran following PL/SQL block to collect statictics:

DECLARE
x VARCHAR2(10);
l_result PLS_INTEGER;
BEGIN
l_result := DBMS_PROFILER.START_PROFILER(RUN_COMMENT => ’selectinto_ok’);
FOR i IN 1 .. 1000000 LOOP
x := example1;
END LOOP;
l_result := DBMS_PROFILER.STOP_PROFILER;
END;

DECLARE
x VARCHAR2(10);
l_result PLS_INTEGER;
BEGIN
l_result := DBMS_PROFILER.START_PROFILER(RUN_COMMENT => ‘forcusor_ok’);
FOR i IN 1 .. 1000000 LOOP
x := example2;
END LOOP;
l_result := DBMS_PROFILER.STOP_PROFILER;
l_result := DBMS_PROFILER.FLUSH_DATA;
END;

My results:

RUNID RUN_DATE RUN_COMMENT RUN_TOTAL_TIME
—— ——— ————————————————– ———————–
13 14-AUG-07 selectinto_ok 18,734,314,785,817.00
14 14-AUG-07 forcusor_ok 23,177,037,928,563.00

Once there was no exception, the result totally reversed.

wow!


Tags: SQL and PL/SQL

0 responses so far ↓

  • There are no comments yet...Kick things off by filling out the form below.

Leave a Comment