Kris Kringle the Database – Why NVL is smart – Learning is not a spectator sport

Kris Kringle the Database – Why NVL is smart

December 13, 2024

3–5 minutes

NVL smarts

A common issue for reports and any query where users can pass parameters is how to handle the “optional” parameter. Here’s a typical example:

“Table CP can be queried where column X is equal to optional parameter P.”

Should we code:


select *
from CP
where ( X = :P or :P is null)


select *
from CP
where X = NVL(:P,X)

(As always) the best way to find this out, is with a test case – first some test data


SQL> create table CP ( x number not null, y number);

Table created.

SQL> insert into cp select rownum,rownum,rownum
  2  from dba_objects;

88659 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index cp1 on cp (x );

Index created.

Now lets see what happens with each scenario


SQL> variable p number
SQL> set autotrace traceonly explain
SQL> select *
  2  from cp
  3  where ( x = :p or :p is null )
  4  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3362165439

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5291 |   661K|   411   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CP   |  5291 |   661K|   411   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:P IS NULL OR "X"=TO_NUMBER(:P))

SQL> select *
  2  from cp
  3  where x = nvl(:p,x);

Execution Plan
----------------------------------------------------------
Plan hash value: 2938069865

----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 | 89816 |    10M|   418   (1)| 00:00:01 |
|   1 |  VIEW                                  | VW_ORE_B2E8B0CB | 89816 |    10M|   418   (1)| 00:00:01 |
|   2 |   UNION-ALL                            |                 |       |       |            |          |
|*  3 |    FILTER                              |                 |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CP              |   889 |   111K|     7   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | CP1             |   356 |       |     1   (0)| 00:00:01 |
|*  6 |    FILTER                              |                 |       |       |            |          |
|   7 |     TABLE ACCESS FULL                  | CP              | 88927 |    10M|   411   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(:P IS NOT NULL)
   5 - access("X"=:P)
   6 - filter(:P IS NULL)

At first glance, the second plan looks worse because it looks like a full scan and an index scan, but it is not. In the second case, the optimizer is doing is some smarts, where it will defer the decision on the whether to do the full scan or the index scan based on whether the parameter is actually provided or not. Notice the FILTER information in the Predicate section – we are deciding which half of the UNION ALL to execute based on the value of the parameter. We can prove this “smart UNION ALL” with some timing results:


SQL> set autotrace traceonly statistics
SQL> exec :p := 123;

PL/SQL procedure successfully completed.

SQL> select *
  2  from cp
  3  where x = nvl(:p,x);

Statistics
--------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          1  physical reads
          0  redo size
        809  bytes sent via SQL*Net to client
         52  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

So we used the index to quickly get to the row, but when we null out the parameter


SQL> exec :p := null;

PL/SQL procedure successfully completed.

SQL> select *
  2  from cp
  3  where x = nvl(:p,x);

88659 rows selected.


Statistics
----------------------------------------------------
          0  recursive calls
          0  db block gets
       7278  consistent gets
          0  physical reads
          0  redo size
   11108359  bytes sent via SQL*Net to client
      65062  bytes received via SQL*Net from client
       5912  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      88659  rows processed

And here we did the full scan (the best option because no parameter was given).

You’ll find that if you use the other syntax, you will get a full tablescan every time. You might be thinking that this is some sort of optimizer flaw, but it is not. The reason for this is that the two query styles could actually return a different result. It all all depends if the column being queried does not contain any nulls. If the column could contain nulls, then of course, the check:


where x = nvl(:p,x)

will not pick up any rows for which X is null (whereas the other query will). As always, care is needed around nulls.

Now before you get too excited and think “Hey, I am going to NVL everything in my search application“, be aware that we apply this optimization for ONE of the NVLs. Let me add another index and another NVL


SQL> create index cp2 on cp ( y );

Index created.

SQL> variable q number
SQL> set autotrace traceonly explain
SQL> select *
  2  from cp
  3  where x = nvl(:p,x)
  4  and y = nvl(:q,y);

Execution Plan
----------------------------------------------------------
Plan hash value: 1963558809

----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |   898 |   112K|   418   (1)| 00:00:01 |
|   1 |  VIEW                                  | VW_ORE_B2E8B0CB |   898 |   112K|   418   (1)| 00:00:01 |
|   2 |   UNION-ALL                            |                 |       |       |            |          |
|*  3 |    FILTER                              |                 |       |       |            |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CP              |     9 |  1152 |     7   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | CP2             |   356 |       |     1   (0)| 00:00:01 |
|*  6 |    FILTER                              |                 |       |       |            |          |
|*  7 |     TABLE ACCESS FULL                  | CP              |   889 |   111K|   411   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(:Q IS NOT NULL)
   4 - filter("X"=NVL(:P,"X"))
   5 - access("Y"=:Q)
   6 - filter(:Q IS NULL)
   7 - filter("Y" IS NOT NULL AND "X"=NVL(:P,"X"))

Notice we didn’t carve this up in multiple UNION ALL phases for each NVL permutation. This benefit really comes if you have a single NVL that will yield good selectivity

Moral of the story:

– Ensure that any columns that will not be null are defined as such in the database

– Look at using the NVL clause when you have a single column with excellent selectivity to handle optional parameters

– If the column can contain nulls or you are building the SQL dynamically, then consider dissolving the SQL into separate queries:


if :p is null then
  select *
  from CP
else
  select *
  from CP
  where x = :p
end if;

to at least get the index benefit when the parameter is actually given.

Ho Ho Ho! Merry Christmas.

3 responses to “Kris Kringle the Database – Why NVL is smart”

rvkstudent

December 13, 2024 at 2:23 pm

Hi, Kris. “but look at the cost for the full scan. This looks a little odd – the cost is “1” whereas the previous explain plan thinks the full scan costs “411”.

But I see cost TABLE ACCESS FULL = “411” in both cases of explain plan. The cost is “1” for INDEX RANGE SCAN. Or do I get something wrong?

Reply
Rajeshwaran Jeyabal

December 13, 2024 at 6:19 pm

couple of things..

first your test case got some syntax errors….table got created with two columns, but insert were trying to insert three columns …uff…

SQL> create table CP ( x number not null, y number);
Table created.
SQL> insert into cp select rownum,rownum,rownum
2 from dba_objects;

I’m on 23.5 and the OR predicates works nicely. what version the above demo was run from ?

demo@FREEPDB1> create table t( x number not null, y number );

Table created.

demo@FREEPDB1> insert into t(x,y) select rownum, rownum from dba_objects;

77508 rows created.

demo@FREEPDB1> create index t_idx on t(x);

Index created.

demo@FREEPDB1> variable n number
demo@FREEPDB1> set autotrace traceonly exp
demo@FREEPDB1> select * from t where x = :n or :n is null ; Execution Plan

Plan hash value: 2478446931 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 80402 | 2041K| 70 (0)| 00:00:01 |
| 1 | VIEW | VW_ORE_1B35BA0F | 80402 | 2041K| 70 (0)| 00:00:01 |
| 2 | UNION-ALL | | 80402 | 2041K| 70 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS FULL | T | 79606 | 2021K| 68 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 796 | 20696 | 2 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | T_IDX | 318 | | 1 (0)| 00:00:01 | Predicate Information (identified by operation id):

3 – filter(:N IS NULL)
5 – filter(LNNVL(:N IS NULL))
7 – access(“X”=TO_NUMBER(:N))

Reply
oracleteam

January 9, 2025 at 9:12 pm

Interesting situation: when you change this to UNION ALL query – INDEX SCAN will be the same, but full scan will be faster (db time drops from 40 to 7 ) The question is why 😮

script:

drop table cp;

create table CP ( x number not null, y number);

insert into cp

with data as (select level lvl from dual connect by level <= 100)

select rownum,rownum

from dba_objects, data;

commit;

create index cp1 on cp (x );

set autotrace traceonly STATISTICS

set timing on

— sys

— alter system flush buffer_cache

select count(y)

from cp

where x = nvl(:p,x);

— sys

— alter system flush buffer_cache

select * from (

select count(y) cnt,

case when :p is not null then ‘Y’ else ‘N’ end test

from cp

where x = :p and :p is not null

union all

select count(y),

case when :p is null then ‘Y’ else ‘N’ end test

from cp

where :p is null

)

where test = ‘Y’;

Reply