Use function or direct column name in sql
function, oracle10g, sql
Solution
Using a join is the way to go. Using the function will trigger a separate query with a separate index lookup for each row result, greatly slowing down the results.
I understand your desire to avoid multiple joins, but in your example, three joins would be better than three function calls.
Think of it this way. The function is doing it's own join on
WHERE emp_id = p_emp_no
And it is not only doing it once, but a separate join for EVERY SINGLE ROW!
Problem
I have a question regarding usage of functions in a sql query. I have the following function for getting username from a table ``` CREATE OR REPLACE FUNCTION get_info( p_emp_no employees.emp_id%TYPE) RETURN VARCHAR2 AS o_userid employees.emp_name%TYPE; BEGIN SELECT emp_name INTO o_userid FROM employees WHERE emp_id = p_emp_no; RETURN o_userid; END; / ``` when I use the above function in sql code as ``` select product_no, get_info(p.raised_by) from products p, employees e where p.raised_by = e.emp_no ``` execution takes more time than the below sql ``` select product_no, e.emp_name from products p, employees e where p.raised_by = e.emp_no ``` The second query is faster than the first query. So is it advisable to use functions to get username from a table or use direct column name and use join to link with employees table? Reason why I asked is there could be multiple references to employees table to get username for raised_by, created_by, modified_by etc. So I thought it would be better to use a function to get emp_name instead of multiple joins with employees table. Any insight into this is highly appreciable. Thanks