Oracle 11g R2 – How to Implement Optional Query Parameters

oracleoracle-11g-r2

I have a relatively big (10^7 rows and growing) table and I want to query some rows from it.
Since table is this big – I want to use every perfomance boost that I can.

My query has optional parameter (let's call it agent_id) – it may be NULL or may have some numeric value.
The column agent_id in table is NOT NULL.

I have thought of two ways to create such a query, here are simplified examples:

Query 1:

select c.agent_id,
    c.date_time
from calls c
where 1 = 1
    and c.agent_id = nvl(agent_id$i, c.agent_id)

Query 2:

select c.agent_id,
    c.date_time
from calls c
where 1 = 1
    and (
        user_id$i is null
        or
        c.agent_id = user_id$i
    )

Is there any perfomance advantage for either one?

I guessed that second one will be slightly faster, because it can evaluate only half of expression, excluding the need to evaluate other half – but it's only a guess, and I want to hear what experts have to say about this issue.

Best Answer

You should not use user_id$i is null or c.agent_id = user_id$i. The optimizer is not able to handle the 2 branches separately and you will end up with a full table scan. Here is a simple blog post about it: link

The NVL version (c.agent_id = nvl(agent_id$i, c.agent_id)) is slightly better. There is a feature called NVL optimization, where the optimizer creates 2 branches from an NVL predicate and performs an UNION ALL on them. This is also demonstrated in the above blog post. This works on very simple queries like the one you posted above, but if you have more parameters like this, it will not work and you will end up with full table scan again.

You can also rewrite your query to use UNION ALL as already suggested in the comment.

To be honest, I do not recommend any of the above.

I have seen this countless times, where for example there was a form with multiple search fields, and clicking on search eecuted a query with lots of columnX = nvl(paramX, columnX) predicates. That will perform poorly, because the above small optimization does not work on complex queries with a lot of parameters.

The same goes for UNION and predicate pushing. If you have a predicate outside of the UNION that could be pushed down to the branches, the database can do that. It works with simple queries, but if you a complex query with multiple UNION, that may or may not happen.

Such queries cause a lot of performance problems. Developers think it results in simpler code and it is elegant to handle separate cases with a single SQL. The database is not good at that. It can not optimize complex queries for multiple outliers based on multiple parameters. If you want performance, you have to work for it. You should change the SQL based on the parameter. With your example, if a value is specified for the parameter use this:

select c.agent_id,
    c.date_time
from calls c
where 1 = 1
    and c.agent_id = agent_id$i;

If there is no value specified for the parameter use this:

select c.agent_id,
    c.date_time
from calls c
where 1 = 1;

With such a simple query it does not make sense, but as your query gets more complex with more predicates, that is the way to go.

Related Question