CODEWITHSUNDEEP
databaseoracle-databaseindexing
aleroot
aleroot

Reputation: 72646

SMART Logic to avoid dynamic SQL and indexes usage

Let's say we have an EMPLOYEE table that we want to query with three filters on the following fields(on which we have indexes on) : subsidiary_id, employee_id, last_name .

If we construct the query with dynamic SQL with simple filter and parameter binding in the where clause like: WHERE last_name = :name, the index is used and the response is fast.

Now the problem is that if we use a SMART Logic in the query to construct the query with Static SQL, in this way : 

SELECT subsidiary_id, employee_id, last_name
FROM EMPLOYEE 
WHERE (:sub_id IS NULL OR subsidiary_id = :sub_id)
AND   (:emp_id IS NULL OR employee_id = :emp_id)
AND   (:name IS NULL OR last_name = :name)

Even though the query executes, and avoid the need for using Dynamic SQL since all the possible filter expressions are statically coded in the statement, it results in an anti-pattern since the database(Oracle) can't optimize the execution plan for a particular filter(since any of them could be cancelled at runtime), it has to prepare for the worst case(all filters disabled) performing a Full Table Scan even if there is an index for each column used for the filter.

The question is: what happens if the query with the smart logic is put inside a stored procedure/function? Is the database smart enough to use the indexes or a Full Table Scan is performed like with a query submitted with binding parameters?

Oracle Stored Procedure body: 

create procedure myproc (employee_id IN NUMBER, subsidiary_id IN NUMBER, name IN VARCHAR2, prc out sys_refcursor)
is
begin
     open prc for (SELECT e.subsidiary_id, e.employee_id, e.last_name
                    FROM EMPLOYEE e
                    WHERE (subsidiary_id IS NULL OR e.subsidiary_id = subsidiary_id)
                    AND   (employee_id IS NULL OR e.employee_id = employee_id)
                    AND   (name IS NULL OR e.last_name = name)
                );
end;

Is the query using the indexes if executed inside a Stored Procedure or Function ?

Upvotes: 2

Views: 457

Answers (2)

Gary Myers
Gary Myers

Reputation: 35401

Stepping back, if the query is going to be run dozens/hundreds/thousands of times, how many times should the query optimizer determine the plan.

It is definitely inefficient to optimize for each execution. Oracle used to optimize once per statement (with some exceptions if a session changed default optimizer, NLS, collation settings etc.)

In 11g it came up with Adaptive Cursor Sharing, where it would try to see if different plans would be better for different query parameters. If it chose one plan initially, but found that subsequent queries didn't match the assumptions in the plan, it could switch to a different one.

https://oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1

My recommendation would be to not rely on this. Explicitly code queries for the most 'expected' paths where you can be confident there will be suitable indexes. You're building an application that is entitled to expectations beyond that of serving ad-hoc queries.

And always use a naming convention to ensure you're PL/SQL variable/parameter names cannot be confused with column names.

create procedure myproc (p_employee_id IN NUMBER, p_subsidiary_id IN NUMBER, p_name IN VARCHAR2, prc out sys_refcursor)
is
begin
    if p_employee_id is not null then
     open prc for (SELECT e.subsidiary_id, e.employee_id, e.last_name
                    FROM EMPLOYEE e
                    WHERE (p_subsidiary_id IS NULL OR e.subsidiary_id = p_subsidiary_id)
                    AND   e.employee_id = p_employee_id
                    AND   (p_name IS NULL OR e.last_name = p_name)
                );
    elsif p_name is not null
     open prc for (SELECT e.subsidiary_id, e.employee_id, e.last_name
                    FROM EMPLOYEE e
                    WHERE (p_subsidiary_id IS NULL OR e.subsidiary_id = p_subsidiary_id)
                    AND   (p_employee_id IS NULL OR e.employee_id = p_employee_id)
                    AND   e.last_name = p_name
                );
    elsif p_subsidiary_id is not null
     open prc for (SELECT e.subsidiary_id, e.employee_id, e.last_name
                    FROM EMPLOYEE e
                    WHERE e.subsidiary_id = p_subsidiary_id
                    AND   (p_employee_id IS NULL OR e.employee_id = p_employee_id)
                    AND   (p_name IS NULL OR e.last_name = p_name)
                );
    else
     open prc for (SELECT e.subsidiary_id, e.employee_id, e.last_name
                    FROM EMPLOYEE e
                );
    end if;
end;

Upvotes: 2

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50047

In my experience with queries doing exactly what you've shown above, your best option is to build your query dynamically to avoid the (:sub_id IS NULL OR subsidiary_id = :sub_id) type of logic. You can try using NVL(:sub_id, subsidiary_id) = subsidiary_id but in general I haven't found this to give good performance. I've found the performance of a dynamically-executed cursor to be much better than the logic you've shown, even with appropriate indexes in place.

Best of luck.

Upvotes: 0

Related Questions