Conditional SQL

Here’s a model of an SQL pattern that I often used to see coming from screen generators; we start with a bit of code to generate a little data before running a very simple query:

rem
rem     Script:         null_plan.sql
rem     Author:         Jonathan Lewis
rem     Dated:          February 2003
rem

create table t1 (
        n1              number(5),
        n2              number(5),
        v1              varchar2(10),
        v2              varchar2(10),
        v3              varchar2(10),
        v4              varchar2(10),
        v5              varchar2(10),
        padding         varchar2(100),
        constraint t1_pk primary key(n1)
);

insert into t1
select
        rownum,
        rownum,
        rownum,
        trunc(100 * dbms_random.value),
        trunc(100 * dbms_random.value),
        trunc(100 * dbms_random.value),
        trunc(100 * dbms_random.value),
        rpad('x',100)
from 
        all_objects
where
        rownum <= 1000 -- > comment to avoid WordPress format issue
;

execute dbms_stats.gather_table_stats(user,'t1')

select 	*
from 	t1
where	t1.n1 = nvl(:n1,t1.n1)
;

This code is basically trying to say:

“if the user supplies an input value for some on-screen field then return data for that value, otherwise return all the data unconditionally.”

Here’s its execution plan (from a 10.2.0.1 instance):

Execution Plan
----------------------------------------------------------
Plan hash value: 3794124282      

-----------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |  1001 |   121K|     6 |
|   1 |  CONCATENATION                |       |       |       |       |
|*  2 |   FILTER                      |       |       |       |       |
|*  3 |    TABLE ACCESS FULL          | T1    |  1000 |   121K|     4 |
|*  4 |   FILTER                      |       |       |       |       |
|   5 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |   124 |     2 |
|*  6 |     INDEX UNIQUE SCAN         | T1_PK |     1 |       |     1 |
-----------------------------------------------------------------------      

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:N1 IS NULL)
   3 - filter("T1"."N1" IS NOT NULL)
   4 - filter(:N1 IS NOT NULL)
   6 - access("T1"."N1"=:N1)      

Note how you can see two access paths embedded in the one plan – one is a full tablescan, the other is an index access path, and each”sub-plan” has a filter parent, and the results of the two filter operations are simply concatenated in line 1 of the plan.

When we check the predicate information section of the report to see what’s going on in the two filter operations. We see that one filter operates the predicate “:N1 IS NULL”, and the other operates the predicate “:N1 IS NOT NULL”. Clearly only one of those two filters can be true. So what’s the optimizer trying to tell us with this plan?

It’s just another example of a “Non-execution plan”. At run-time, when Oracle gets to the filter at operation 2 it decides whether or not to run the sub-plan starting at operation 3; similarly when it gets to operation 4 it will decide whether or not to run the sub-plan starting at operation 5. Clearly only one of the sub-plans will actually be run.

Effectively the optimizer has generated an ‘if-then-else’ execution plan:

“if :n1 is null then do a tablescan else do an index range scan”

In (much) older versions of Oracle the only safe option was always to do the tablescan.

The same trick appears for predicates like: “n1 = decode(:n1, null, n1, :n1)”, although it doesn’t (currently) appear for the (apparently) equivalent “n1 = nvl2(:n1, :n1, n1)”, or the more extensive coalesce({expression1}, {expresssion2}, …) even in its shortest two-expression form.

There are all sorts of ramifications and interesting little side effects to this feature (which appeared somewhere in the middle of 8i, I think, when the parameter _or_expand_nvl_predicate was set to true), and I’ll talk about a couple of them in a later posting. In the meantime, I’ll leave you with just one thought.

I said at the top of the page that screen painters often generate code of this sort because they want to return either “the requested items” or “all the data”. Did that statement seem to make sense when you read it?  Look very carefully at the predicate information section for operation 3.

When you do the full tablescan, Oracle checks that “n1 is not null” – in other words the query may “lose” some rows unless n1 has been declared as not null. This isn’t something new, by the way, but in the improved output, with the cunning plan, it’s suddenly very explicit. Maybe your screen painter has been giving you wrong results for years!

It’s an interesting oddity – perhaps one of those “generic code” details – that this “n1 is not null” predicate is redundant in this example – I’ve declared n1 to be the primary key of the table so it’s implicitly guaranteed to be not null anyway and in other circumstances the optimizer would have eliminated the predicate if you had supplied it manually.

Update Feb 2018

This posting is still relevant for 12.2.0.1 – including all the comments about the cases where the “conditional plan” strategy does not appear. A key difference in 12.2, though, is that Oracle replaces the CONCATENATION operator with a new “OR expansion” mechanism which mean you see a VIEW operation on an object with a name like ‘VW_ORE_xxxxxxxx’   and a UNION ALL operator inside the view.

E.g:

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |  1001 |   110K|     6   (0)| 00:00:01 |
|   1 |  VIEW                          | VW_ORE_BA8ECEFB |  1001 |   110K|     6   (0)| 00:00:01 |
|   2 |   UNION-ALL                    |                 |       |       |            |          |
|*  3 |    FILTER                      |                 |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1              |     1 |   125 |     2   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | T1_PK           |     1 |       |     1   (0)| 00:00:01 |
|*  6 |    FILTER                      |                 |       |       |            |          |
|   7 |     TABLE ACCESS FULL          | T1              |  1000 |   122K|     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:N1 IS NOT NULL)
   5 - access("T1"."N1"=:N1)
   6 - filter(:N1 IS NULL)

It’s a funny little quirk that the two branches of the UNION ALL always seem to appear in the opposite order they did for the CONCATENATION. You’ll also notice that the redundant “n1 is not null” predicate has not been added to the transformed SQL at operation 7.