Hi Tom,

Will calling a package function repetitively in SELECT clause impacts performance of a SQL?

Function returns DateTime from a table and in SELECT clause, Date (DD-MON-RRRR) is shown as separate column and the same function is called again to show the Time (HH24:MI:SS) as a separate column.
The function which relies on a table is a huge table and indexed properly.

I would like to know whether such repetitive call to the function in select clause will have performance impact.

As per my understanding, when a package function is called, function will be cached and repetitive call will use the code in cache.

In this case whether the result set evaluated during the first call will be reused for the subsequent calls or the result set is evaluated every time even though the WHERE
clause and parameters passed are same.

SQL 1 :
SELECT
TO_CHAR(fn_get_dt_tme(emp_id), 'DD-MON-RRRR') join_date, TO_CHAR(fn_get_dt_tme(emp_id), 'HH24:MI:SS') join_time
FROM emp;

I have rewritten the above query as below:

SQL 2:
SELECT TO_CHAR(join_dt, 'DD-MON-RRRR') join_date,
TO_CHAR(join_dt, 'HH24:MI:SS') join_time
FROM (SELECT fn_get_dt_tme(emp_id) join_dt FROM emp );

CREATE FUNCTION fn_get_dt_tme(p_emp_id NUMBER)
RETURN DATE AS
v_date DATE;
BEGIN
SELECT join_date INTO v_date FROM emp WHERE emp_id = p_emp_id;
RETURN v_date;
END;

Will there be performance improvement in the second query over the first query? kindly advise.

Thanks in advance.

Best Regards,
Arulkumar S