Oracle Scratchpad

December 11, 2013

Null Quiz

Filed under: Oracle,Performance,Troubleshooting,Tuning — Jonathan Lewis @ 6:42 pm BST Dec 11,2013

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 the first one in Hoek in comment 9 identified the original OTN post where I explained what was going on to the OP.

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 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 filter operations to select one of two different run-time paths depending on whether :b1 were 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 actually wanted Oracle to return in if the input to the pl/sql call as a NULL, did 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.

14 Comments »

  1. Hi, i see two different statements. the first is without bind variable, the second is with a bind variable. The first statement, maybe the CBO is smart enough to eliminate the nvl , because the null will Always be null. categorie=categorie. The second statement with bind variable , the nvl can’t go away, because bind variable in later executions is maybe not null. So if there is an index on category, it can’t be used due to the nvl.

    Comment by Patrick — December 11, 2013 @ 8:29 pm BST Dec 11,2013 | Reply

  2. They are equivalent only for the category is not nullable.

    Comment by Valentin Nikotin — December 11, 2013 @ 8:39 pm BST Dec 11,2013 | Reply

  3. Hi Jonathan,

    (p_user_category is null or category = p_user_category) is not equal to category = nvl(p_user_category, category);

    In case of p_user_category is null and category is null :

    in (p_user_category is null or category = p_user_category) we gel rows having category null and not nulls but the for the second clause category = nvl(p_user_category, category) we got only rows without category is null.

    Thanks,
    Cherif

    Comment by bijgaa — December 11, 2013 @ 8:42 pm BST Dec 11,2013 | Reply

  4. First,
    “where category = nvl(null, category)”
    seems like an expensive way to write
    “where category is not null”

    Second,
    Is getting all the rows when the passed in parameter is null and just categories matching the parameter value when it is not null the intended result?

    From the problem description it seems like what is probably intended is something like

    select category, id, description, inventory
    from the_table
    where category is not null
    order by category;

    Possibly with a break on category.

    But giving null as the input parameter to the nvl function certainly is a novel way to alias a column value or specify a literal!

    I can imagine a useful interpretation for the PL/SQL cursor

    where (p_user_category is null or category = p_user_category)
    and category is not null

    if the OP wanted the report for either one specific category (passed) in or all the rows where category is set to something other than null when not told a specific category by passing in null.

    Likely you would want that returned in some particular order, either id or category depending on whether you wanted to learn which categories were defined for each id (presuming multiple rows in the table for id, desc, inv columns) or which ids were in which categories. And you’d probably want category in the result array if the inbound parameter is null, while you don’t strictly need it if the caller already has a value in hand.

    I suppose they could also be meaning to not execute the query at all if a null parameter is supplied, which would then subordinate even bothering to open the cursor to an if on p_user_category is not null.

    Sigh. Do you see something different?

    Comment by rsiz — December 11, 2013 @ 9:14 pm BST Dec 11,2013 | Reply

  5. 1. Is category defined as `not null`?
    2. Is he expecting to get a single row back? If not, the PL/SQL will break. But that’s not a problem with nulls.

    Comment by Colin 't Hart — December 11, 2013 @ 9:33 pm BST Dec 11,2013 | Reply

  6. I don’t like these “quiz questions” without sufficient information. This “quiz question” doesn’t contain any of the information I would normally gather when diagnosing a performance problem: 10046 trace, 10053 trace, session events and alike. In practice, I have never seen anybody write anything even remotely like
    “where category = nvl(null, category)”
    It would be much more interesting if you told us what is the point here?

    Comment by mgogala — December 12, 2013 @ 6:57 am BST Dec 12,2013 | Reply

  7. the question or conundrum I guess JL is setting (its not clearly put TBH) is that can we see whats wrong with the original PLSQL without any more info.

    Firstly, In response to other posters.

    I have seen many coders use a technique like that “where x = nvl(null,x)” where the select requirement dictated that if its null to ignore it as a predicate. but thats not the question here
    Yes the SQL is different but the second way of writing it satisfies the predicate, thats not the issue.
    The plsql wont break, he’s populating a cursor. (its not doing anything in the fetch but thats beside the point)

    Im not seeing the obvious answer if there is one so 10046 all day long for me where Id expect to see a FTS going on instead of an index for some reason got to do with SQL and PLSQL engine differences evaluating the slow predicate.

    Comment by chad — December 12, 2013 @ 7:54 am BST Dec 12,2013 | Reply

  8. Type of bind?

    Comment by András Gábor — December 12, 2013 @ 8:18 am BST Dec 12,2013 | Reply

  9. NVL is a sql function in the kernel where the second argument to the function gets evaluated regardless of whether the first argument is NULL or NOT NULL. That becomes even more dangerous when the second argument is a function call and the first argument is NULL most of the time. On the other hand, DECODE and CASE…WHEN works differently. Try it.

    Comment by Guest — December 12, 2013 @ 6:26 pm BST Dec 12,2013 | Reply

  10. Chad, +1
    Maybe, the problem is that OR-expansion tends to use FTS in the plan

    Comment by Ilya — December 12, 2013 @ 6:44 pm BST Dec 12,2013 | Reply

  11. So why doesn’t NVL OR-expansion kick in here? Why did the OP have to do that rewrite by hand?

    Comment by Jason Bucata — December 13, 2013 @ 4:37 pm BST Dec 13,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,266 other followers