Month: May 2016

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;
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:


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