SQL & PL/SQL

Hello,

I have a table which uses parent-child relationship to store the options available. I need a function to give me the full path given the id of a particular option.

I have two different functions. One of them uses the Oracle built in function and the other uses simple queries with a loop.The code of the functions are given below.

Now, the problem is with their "performance". The difference in their performance is significant. The function using the Oracle function takes more than 2 hours to run a query whereas the other function takes less than 2 minutes.

I am having trouble trusting the other function. No matter how many tests I perform on the output of both the functions, it always comes out to be the same.

Any thoughts to help me understand this ??


Function 1
=====================
FUNCTION Gettree (opt_id IN NUMBER,i_app_id IN NUMBER)
RETURN VARCHAR2
IS
path VARCHAR2(32767);
application_no NUMBER;
BEGIN

SELECT ABC.APP_OPT_ID INTO application_no FROM ABC
WHERE ABC.APP_ID = i_app_id AND ABC.PARENT_ID IS NULL;

SELECT LPAD(' ', 2*LEVEL-1)||SYS_CONNECT_BY_PATH(app_opt_name, '=>') "Path" INTO path
FROM ABC
WHERE app_opt_id = opt_id
START WITH parent_id =application_no
CONNECT BY PRIOR app_opt_id =parent_id;
path := SUBSTR(path,INSTR(path,'>')+1,LENGTH(path));

RETURN path;
END Gettree ;

Function 2
======================
FUNCTION GetOptPath(opt_id NUMBER,app_id NUMBER)
RETURN VARCHAR2
IS
string VARCHAR2(900);
opt VARCHAR2(100);
pid NUMBER(38);
BEGIN
SELECT ABC.parent_id,ABC.app_opt_name INTO pid,string FROM ABC WHERE ABC.app_opt_id = opt_id;

IF pid IS NULL
THEN
RETURN 'root';
ELSIF pid IS NOT NULL
THEN
LOOP
SELECT ABC.app_opt_name,ABC.parent_id INTO opt, pid FROM ABC WHERE ABC.app_opt_id = pid;
EXIT WHEN pid IS NULL;
string := opt || '=>'|| string;
END LOOP;
RETURN string;
END IF;
END;