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

2 thoughts on “SOLUTION: A Strange Condition in the Where Clause

  1. Lothar,
    a nice example and certainly a feature with interesting (and sometimes strange) effects. But I am not sure if I would use the term “virtual” in this context since – if my memory serves me well – the column is technically not a virtual column. Though I would certainly agree that storing the defaults only in the dictionary for rows that have been there before the addition of the column makes these values somehow virtual. Mohamed Houri has written an instructive OTN article containing some additional details:



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s