Tuesday, July 02, 2013

12c - Invisible Columns...

Remember when 11g first came out and we had "invisible indexes"?  It seemed like a confusing feature - indexes that would be maintained by modifications (hence slowing them down), but would not be used by queries (hence never speeding them up).  But - after you looked at them a while, you could see how they can be useful.  For example - to add an index in a running production system, an index used by the next version of the code to be introduced later that week - but not tested against the queries in version one of the application in place now.  We all know that when you add an index - one of three things can happen - a given query will go much faster, it won't affect a given query at all, or... It will make some untested query go much much slower than it used to.  So - invisible indexes allowed us to modify the schema in a 'safe' manner - hiding the change until we were ready for it.

Invisible columns accomplish the same thing - the ability to introduce a change while minimizing any negative side effects of that change.  Normally when you add a column to a table - any program with a SELECT * would start seeing that column, and programs with an INSERT INTO T VALUES (...) would pretty much immediately break (an INSERT without a list of columns in it).  Now we can add a column to a table in an invisible fashion, the column will not show up in a DESCRIBE command in SQL*Plus, it will not be returned with a SELECT *, it will not be considered in an INSERT INTO T VALUES statement.  It can be accessed by any query that asks for it, it can be populated by an INSERT statement that references it, but you won't see it otherwise.

For example, let's start with a simple two column table:

ops$tkyte%ORA12CR1> create table t
  2  ( x int,
  3    y int
  4  )
  5  /
Table created.

ops$tkyte%ORA12CR1> insert into t values ( 1, 2 );
1 row created.

Now, we will add an invisible column to it:

ops$tkyte%ORA12CR1> alter table t add 
                    ( z int INVISIBLE );
Table altered.

Notice that a DESCRIBE will not show us this column:

ops$tkyte%ORA12CR1> desc t
 Name              Null?    Type
 ----------------- -------- ------------
 X                          NUMBER(38)
 Y                          NUMBER(38)

and existing inserts are unaffected by it:

ops$tkyte%ORA12CR1> insert into t values ( 3, 4 );
1 row created.

A SELECT * won't see it either:

ops$tkyte%ORA12CR1> select * from t;

         X          Y
---------- ----------
         1          2
         3          4

But we have full access to it (in well written programs! The ones that use a column list in the insert and select - never relying on "defaults":

ops$tkyte%ORA12CR1> insert into t (x,y,z) 
                        values ( 5,6,7 );
1 row created.

ops$tkyte%ORA12CR1> select x, y, z from t;
         X          Y          Z
---------- ---------- ----------
         1          2
         3          4
         5          6          7

and when we are sure that we are ready to go with this column, we can just modify it:

ops$tkyte%ORA12CR1> alter table t modify z visible;
Table altered.

ops$tkyte%ORA12CR1> select * from t;

         X          Y          Z
---------- ---------- ----------
         1          2
         3          4
         5          6          7


I will say that a better approach to this - one that is available in 11gR2 and above - would be to use editioning views (part of Edition Based Redefinition - EBR ).  I would rather use EBR over this approach, but in an environment where EBR is not being used, or the editioning views are not in place, this will achieve much the same.

Read these for information on EBR:


POST A COMMENT

9 Comments:

Anonymous Sokrates said....

one could argue: what's the point with visible columns at all ?
Wouldn't it be nicer if all columns were invisible ?
( I'm not kidding, we would have to use som kind of
@desc instead of
desc ,
but every select and every insert would have to be explicit )
but unfortunately

ORA-54039: table must have at least one column that is not invisible.

Wed Jul 03, 02:51:00 AM EDT  

Anonymous Lucian said....

Just a thought while the 12c is installing: will these columns be visible in USER_TAB_COLS? How will we know they are there?

Wed Jul 03, 08:18:00 AM EDT  

Blogger Thomas Kyte said....

@Lucian

yes, they are visible:

ops$tkyte%ORA12CR1> create table t ( x int, y int );

Table created.

ops$tkyte%ORA12CR1> alter table t add z int invisible;

Table altered.

ops$tkyte%ORA12CR1> select column_name, hidden_column from user_tab_cols where table_name = 'T';

COLUMN_NAM HID
---------- ---
Z YES
Y NO
X NO

Wed Jul 03, 11:18:00 AM EDT  

Blogger Danilo Piazzalunga said....

"[Adding an index] will make some untested query go much much slower than it used to."

This would be quite surprising. Excluding pathological cases, shouldn't good statistics prevent that?

Fri Jul 05, 04:23:00 AM EDT  

Blogger Thomas Kyte said....

@Danilo

shouldn't "good" statistics prevent that? yes, in a perfect world. But - what are "good statistics"

all it takes is a predicate difficult enough for the optimizer to come up with a slightly or really bad cardinality estimate for whatever reason - pick the wrong index and bamm - horrible response time.

Yes, it would be the edge cases mostly - but that is what things like "introducing change SAFELY" like this are all about.

It isn't often, it isn't the expected case, but it happens - and this allows us to introduce change with a much less reduced chance of that happening...

Fri Jul 05, 11:11:00 AM EDT  

Anonymous Anonymous said....

What would happen if I add the "NOT NULL" constraint on the invisible column. Can this be done?

I am assuming this cannot be done as inserts on only the visible columns would then fail.

Wed Jul 10, 02:00:00 PM EDT  

Blogger Thomas Kyte said....

@Anonymous

depends on how you add it. NOT NULL with a default would not break inserts. but yes, you can add a not null invisible column. If you didn't default it then yes, it would break inserts that didn't supply a value for it...

ops$tkyte%ORA12CR1> create table t ( x int );

Table created.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> alter table t add y int invisible default 5 not null;

Table altered.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA12CR1> select x, y from t;

X Y
---------- ----------
1 5

that shows NOT NULL default would be OK

and

ops$tkyte%ORA12CR1> create table t ( x int );

Table created.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> alter table t add y int invisible not null;

Table altered.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("OPS$TKYTE"."T"."Y")


ops$tkyte%ORA12CR1> select x, y from t;

no rows selected


shows NOT NULL without a default (or a trigger to supply a default) would "break"

Wed Jul 10, 03:02:00 PM EDT  

Blogger Eric Yen said....

Can you disable invisible columns?

Wed Jan 29, 10:34:00 PM EST  

Blogger Thomas Kyte said....

@Eric,

tell me what it means to "disable" a column first?

Thu Jan 30, 08:54:00 AM EST  

POST A COMMENT

<< Home