The title is indeed strange, but I will try to explain.
I have been programming Oracle (PL-SQL) in version 11G for several years.
I recently moved to a new workplace, and in the first task I was required to write a function that accepts 5 variables as input, and it is possible that not all variables will be sent – maybe only one.
As part of the process, there is a very large query between lots of very large tables, so the more values there are in the input, the more the JOINS between the tables are realized and the indexes are used efficiently.
Like
create function get_customer(p_ssn number, p_phone number, p_email varchar2, p_address varchar2, p_unique_id number number) return <type>
is
begin
select...
from A, B, C, D, E, F, G
JOIN ..
where ssn = p_ssn
and phone = p_phone ...
end;
The first way I thought to solve the task is by using dynamic SQL, so that we check for a certain input whether it is not NULL, and if so we can chain its AND to the query and at the end EXECUTE IMMEDIATE for the entire query
Like
v_sql varchar2(4000);
v_sql := 'select...
from A, B, C, D, E, F, G
JOIN ..
WHERE 1=1'
if p_phone is not null then
v_sql := v_sql || ' AND phone = p_phone'
end if;
etc..
But, my new team leader decided not to use dynamic SQL because it is complicated and can crash and other reasons.
He suggested using NVL for each of the inputs, for example
select...
from A, B, C, D, E, F, G
JOIN ..
where ssn = nvl (p_ssn, a.ssn)
and phone = nvl(p_phone, b.phone) etc..
I was stunned. I thought a DBA team leader should understand a thing or two about efficiency and runtime.
After I showed him the execution plan was really bad, and in particular that it took him a lot of time to finish his run, he told me to find another effective way without using dynamic SQL.
So, other suggestions How can the task be solved?
4
In your example, you are not varying which tables are joined depending to whether the arguments are set; instead you are always selecting the same columns from the same tables and applying different filters based on whether the arguments are set or unset.
In that case, pass NULL as the default value for the optional arguments and then use a static query and check if the optional arguments are unset with IS NULL so that you do not apply that filter:
CREATE FUNCTION get_customer(
p_ssn A.SSN%TYPE DEFAULT NULL,
p_phone B.PHONE%TYPE DEFAULT NULL,
p_email C.EMAIL%TYPE DEFAULT NULL,
p_address D.ADDRESS%TYPE DEFAULT NULL,
p_unique_id E.UNIQUE_ID%TYPE DEFAULT NULL
) RETURN <type>
IS
BEGIN
SELECT A.column1,
B.column2,
C.column3,
D.column4,
E.column5,
F.column6,
G.column7
INTO ...
FROM A
INNER JOIN B ON (...)
INNER JOIN C ON (...)
INNER JOIN D ON (...)
INNER JOIN E ON (...)
INNER JOIN F ON (...)
INNER JOIN G ON (...)
WHERE (p_ssn IS NULL OR a.ssn = p_ssn)
AND (p_phone IS NULL OR b.phone = p_phone)
...;
END;
If you want to use dynamic SQL then don’t concatenate the values into the query (as that is how you introduce SQL injection vulnerabilities into your code). Instead, use a parameterised query to pass the values into the query using bind variables:
v_sql varchar2(4000);
v_sql := 'SELECT A.column1,
B.column2,
C.column3,
D.column4,
E.column5,
F.column6,
G.column7
FROM A
INNER JOIN B ON (...)
INNER JOIN C ON (...)
INNER JOIN D ON (...)
INNER JOIN E ON (...)
INNER JOIN F ON (...)
INNER JOIN G ON (...)
WHERE 1=1'
IF p_ssn IS NOT NULL THEN
v_sql := v_sql || ' AND ssn = :1';
ELSE
v_sql := v_sql || ' AND :1 IS NULL';
END IF;
IF p_phone IS NOT NULL THEN
v_sql := v_sql || ' AND phone = :2';
ELSE
v_sql := v_sql || ' AND :2 IS NULL';
END IF;
EXECUTE IMMEDIATE v_sql
INTO ...
USING p_ssn, p_phone;
5