Default

SOLUTION: A Strange Condition in the Where Clause

The strange condition is automatically generated by the database.
The cause is the DDL optimization, which are available starting with version 11G.
When you insert an additional column in a table, the database will not necessarily create a physical column.
It may generate a “DDL optimized” column instead. When specifying a default, this column can also be defined not null.
That way the database saves the effort to enhance each record by one column.
It only generates an entry is in the dictionary, which of course is much faster.
Each row of data may contain a real value for the “DDL optimized” column if a value was inserted.
If no value was inserted, the default value is used instead.
Since it is possible that no value exists, the database needs to replace the column names by the formula.
Here is a simple example:

create table x (y number);
insert into x select rownum from dual connect by rownum < 1000000;
commit;
alter table x add ( z number default 1 not null);
select 1 from x where z=1;

If we look at the execution plan of the query, we notice that the column name Z was replaced by the formula:


(NVL("Z",1)=1)

Here the link to Carlos’s Blog: Interesting case where a full table scan is chosen instead of an index

A Strange Condition in the Where Clause

It makes me proud to learn that Carlos Sierra is following my blog. I knew Carlos from my time at Oracle, even though we only recently met for the first time. I see Carlos as a man who acts rather than complains. One of the people that make a difference.
With my next example I will show that it is also an astute analyst.
I recently saw a condition in an execution plan that was not present in the sql statement. I wanted to know how the condition came from.
I give a simple example: Consider a table x that looking like this:


SQL> desc x
Name Null? Typ
----------------------------------------- -------- -----------------
Y NUMBER
Z NOT NULL NUMBER

Let us do a query on x:


select count(*) from x where z=1;

The execution plan looks like this:


Plan hash value: 989401810

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   420 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| X    |  1144K|    14M|   420   (2)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter(NVL("Z",1)=1)

Why is does the execution plan show this strange condition? You will find the answer in Carlos Sierra’s blog.
One more hint: It has something to do with default values. Good luck.