Null Quiz

Here’s an example I saw a few months ago of the confusion caused by NULL. As the owner of the problem put it: the first query, run from SQL*Plus for testing purposes, takes no time to complete; but when “put into a pl/sql cursor” (as shown in the second query) it takes ages to complete.

What’s going on ?

    select id
         , description
         , inventory
    from the_table
    where category =  nvl(null, category);

    --

    open c_results for
        select id
             , description
             , inventory
        from the_table
        where category =  nvl(p_user_category, category);

    fetch c_results into v_id, v_description, v_inventory;

    close c_results;

After pondering the problem for a while the OP managed to get the required level performance by changing the predicate to this:

    where (p_user_category is null or category = p_user_category)

So what’s the problem now ?

Update

Plenty of responses to this, but I’ll start by pointing out that Hoek in comment 9 identified the original OTN post where I explained what was going on to the OP.

There are three general points to consider, of course:

• first, if you change a query to make it go faster is the resulting query logically the same
• second, cutting “bind variable” SQL from PL/SQL and testing with a representative bind value can be very helpful, but it can mislead
• third, before asking why two queries perform differently it’s worth capturing their actual execution plans and comparing them

For demontration purposes, I’ve just done the same with one of my little demo tables and a slightly different SQL statement – here’s what I pulled from v$sql_plan for the NULL version, then for the BIND version (using 10.2.0.5):

select vc from t1 where id >= nvl(null,id)

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |    10 |   200 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID">="ID")

***************************************************************************

SELECT /*+ findthis */ VC FROM T1 WHERE ID >= NVL(:B1,ID)

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     8 |   160 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID">=NVL(:B1,"ID"))

In this case (small table, no indexes, requesting most of the table) the only possible path is a full tablescan – but that’s not significant, the critical detail is in the predicate information. In the case of the OP the impact of the predicate change would probably have resulted in a plan showing a concatenation operation with two child operations that were filter operators to select one of two different run-time paths depending on whether :b1 was null or not.

Once you’ve worked out why the performance is different – and given the clues about the predicate manipulation (especially if you get one of those concatenation paths) – you can ask yourself what data you would actually want Oracle to return if the input to the pl/sql call were a NULL. Would you want to exclude the possibility of picking up any rows where the column was null, or did you want a row with a null to be a legal return value.