1 Reply Latest reply: Aug 8, 2013 2:50 PM by Jonathan Lewis RSS

    Where clause causing a query to slow down in a cursor

    Donbot

      I have a table "the_table" with about 10,000 rows and four columns (id, description, inventory, and category).

       

      This query returns all rows immediately:

       

      ----

          select id

               , description

               , inventory

          from the_table

          where category =  nvl(null, category);

      ----

       

      However, when it is put into a cursor, like this:

      (p_user_category is a user-defined variable which can be null if the user wants all of the rows)

       

      ----

          c_results  sys_refcursor;

          --

          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;

      ----

       

      then it takes five minutes to return even just one row when p_user_category is null.

      However, if I change the where clause to:

       

      ----

          where (p_user_category is null or category = p_user_category)

      ----

       

      then it returns all rows immediately.

       

      It started being a problem right around the time of the most recent update - I am running 11.2.0.2.0 (64-bit production).

        • 1. Re: Where clause causing a query to slow down in a cursor
          Jonathan Lewis

          The optimizer is smart enough to recognise that null is null, so "nvl(null,category)" collapses to "category", and your predicate "category = nvl(null, category)" is transformed to "category is not null" (if it's allowed to be null) or simply disappears - so the SQL test is not the same as the PL/SQL run.

           

          In PL/SQL your manual rewrite is not logicall the same as the original unless you have declared category to be non-null because your first disjunct will allow rows with a null category to be reported, while the original query would lose them.

           

          Check the execution plans for the SQL and the PL/SQL versions.

           

          Regards

          Jonathan Lewis

          http://jonathanlewis.wordpress.com

          Now on Twitter: @jloracle