How to handle optional parameters in SQL query?

Question

Say I have a sample table:

 id_pk  value
------------
 1       a
 2       b
 3       c

And I have a sample PL/SQL block, which has a query that currently selects a single row into an array:

declare

  type t_table is table of myTable%rowtype;

  n_RequiredId myTable.id_pk%type := 1;  
  t_Output t_table := t_table();

begin

  select m.id_pk, m.value
    bulk collect into t_Output
    from myTable m 
   where m.id_pk = n_RequiredId;

end;

What I need to do is to implement an ability to select a single row into an array, as shown in the block above, OR to select all rows into an array, if n_RequiredID, which is actually a user-input parameter, is set to null.

And, the question is, what's the best practice to handle such situation?

I can think of modifying where clause of my query to something like this:

where m.id_pk = nvl(n_RequiredId, m.id_pk);

but I suppose that's going to slow down the query if the parameter won't be null, and I remember Kyte said something really bad about this approach.

I can also think of implementing the following PL/SQL logic:

if n_RequiredId is null then 

  select m.id_pk, m.value bulk collect into t_Output from myTable m;

else

  select m.id_pk, m.value bulk collect
    into t_Output
    from myTable m
   where m.id_pk = n_RequiredId;

end if;

But would become too complex if I encounter more than one parameter of this kind.

What would you advice me?

Answer 1

OMG_Ponies' and Rob van Wijk's answers are entirely correct, this is just supplemental.

There's a nice trick to make it easy to use bind variables and still use dynamic SQL. If you put all of the binds in a with clause at the beginning, you can always bind the same set of variables, whether or not you're going to use them.

For instance, say you have three parameters, representing a date range and an ID. If you want to just search on the ID, you could put the query together like this:

with parameters as (
     select :start_date as start_date,
            :end_date as end_date,
            :search_id as search_id
     from dual)
select * 
from your_table 
     inner join parameters
        on parameters.search_id = your_table.id;

On the other hand, if you need to search on the ID and date range, it could look like this:

with parameters as (
     select :start_date as start_date,
            :end_date as end_date,
            :search_id as search_id
     from dual)
select * 
from your_table 
     inner join parameters
         on parameters.search_id = your_table.id
            and your_table.create_date between (parameters.start_date
                                                and parameters.end_date);

This may seem like an round-about way of handling this, but the end result is that no matter how you complicated your dynamic SQL gets, as long as it only needs those three parameters, the PL/SQL call is always something like:

execute immediate v_SQL using v_start_date, v_end_date, v_search_id;

In my experience it's better to make the SQL construction slightly more complicated in order to ensure that there's only one line where it actually gets executed.

Answer 2

The NVL approach will usually work fine. The optimizer recognizes this pattern and will build a dynamic plan. The plan uses an index for a single value and a full table scan for a NULL.

Sample table and data

drop table myTable;
create table myTable(
    id_pk number,
    value varchar2(100),
    constraint myTable_pk primary key (id_pk)
);

insert into myTable select level, level from dual connect by level <= 100000;
commit;

Execute with different predicates

--Execute predicates that return one row if the ID is set, or all rows if ID is null. 
declare
    type t_table is table of myTable%rowtype;
    n_RequiredId myTable.id_pk%type := 1;  
    t_Output t_table := t_table();
begin
    select /*+ SO_QUERY_1 */ m.id_pk, m.value
    bulk collect into t_Output
    from myTable m
    where m.id_pk = nvl(n_RequiredId, m.id_pk);

    select /*+ SO_QUERY_2 */ m.id_pk, m.value
    bulk collect into t_Output
    from myTable m
    where m.id_pk = COALESCE(n_RequiredId, m.id_pk);

    select /*+ SO_QUERY_3 */ m.id_pk, m.value
    bulk collect into t_Output
    from myTable m
    where (n_RequiredId IS NULL OR m.id_pk = n_RequiredId);
end;
/

Get execution plans

select sql_id, child_number
from gv$sql
where lower(sql_text) like '%so_query_%'
    and sql_text not like '%QUINE%'
    and sql_text not like 'declare%';

select * from table(dbms_xplan.display_cursor(sql_id => '76ucq3bkgt0qa', cursor_child_no => 1, format => 'basic'));
select * from table(dbms_xplan.display_cursor(sql_id => '4vxf8yy5xd6qv', cursor_child_no => 1, format => 'basic'));
select * from table(dbms_xplan.display_cursor(sql_id => '457ypz0jpk3np', cursor_child_no => 1, format => 'basic'));

Bad plans for COALESCE and IS NULL OR

EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ SO_QUERY_2 */ M.ID_PK, M.VALUE FROM MYTABLE M WHERE M.ID_PK 
= COALESCE(:B1 , M.ID_PK)

Plan hash value: 1229213413

-------------------------------------
| Id  | Operation         | Name    |
-------------------------------------
|   0 | SELECT STATEMENT  |         |
|   1 |  TABLE ACCESS FULL| MYTABLE |
-------------------------------------


EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ SO_QUERY_3 */ M.ID_PK, M.VALUE FROM MYTABLE M WHERE (:B1 IS 
NULL OR M.ID_PK = :B1 )

Plan hash value: 1229213413

-------------------------------------
| Id  | Operation         | Name    |
-------------------------------------
|   0 | SELECT STATEMENT  |         |
|   1 |  TABLE ACCESS FULL| MYTABLE |
-------------------------------------

Good plan for NVL

The FILTER operations allow the optimizer to choose a different plan at run time, depending on the input values.

EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ SO_QUERY_1 */ M.ID_PK, M.VALUE FROM MYTABLE M WHERE M.ID_PK 
= NVL(:B1 , M.ID_PK)

Plan hash value: 730481884

----------------------------------------------------
| Id  | Operation                     | Name       |
----------------------------------------------------
|   0 | SELECT STATEMENT              |            |
|   1 |  CONCATENATION                |            |
|   2 |   FILTER                      |            |
|   3 |    TABLE ACCESS FULL          | MYTABLE    |
|   4 |   FILTER                      |            |
|   5 |    TABLE ACCESS BY INDEX ROWID| MYTABLE    |
|   6 |     INDEX UNIQUE SCAN         | MYTABLE_PK |
----------------------------------------------------

Warnings

FILTER operations and this NVL trick are not well documented. I'm not sure what version introduced these features but it works with 11g. I've had problems getting the FILTER to work correctly with some complicated queries, but for simple queries like these it is reliable.

Answer 3

Yes, using any of the following:

WHERE m.id_pk = NVL(n_RequiredId, m.id_pk);
WHERE m.id_pk = COALESCE(n_RequiredId, m.id_pk);
WHERE (n_RequiredId IS NULL OR m.id_pk = n_RequiredId);

...are not sargable. They will work, but perform the worst of the available options.

If you only have one parameter, the IF/ELSE and separate, tailored statements are a better alternative.

The next option after that is dynamic SQL. But coding dynamic SQL is useless if you carry over the non-sargable predicates in the first example. Dynamic SQL allows you to tailor the query while accommodating numerous paths. But it also risks SQL injection, so it should be performed behind parameterized queries (preferably within stored procedures/functions in packages.