Tuesday, July 02, 2013

12c - Silly little trick with invisibility...

This is interesting, if you hide and then unhide a column - it will end up at the "end" of the table.  Consider:

ops$tkyte%ORA12CR1> create table t ( a int, b int, c int );
Table created.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> desc t;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 A                                                              NUMBER(38)
 B                                                              NUMBER(38)
 C                                                              NUMBER(38)

ops$tkyte%ORA12CR1> alter table t modify (a invisible);
Table altered.

ops$tkyte%ORA12CR1> alter table t modify (a visible);
Table altered.

ops$tkyte%ORA12CR1> desc t;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 B                                                              NUMBER(38)
 C                                                              NUMBER(38)
 A                                                              NUMBER(38)


Now, that means you can add a column or shuffle them around.  What if we had just added A to the table and really really wanted A to be first.  My first approach would be "that is what editioning views are great at".  If I couldn't use an editioning view for whatever reason - we could shuffle the columns:

ops$tkyte%ORA12CR1> alter table t modify (b invisible);
Table altered.

ops$tkyte%ORA12CR1> alter table t modify (c invisible);
Table altered.

ops$tkyte%ORA12CR1> alter table t modify (b visible);
Table altered.

ops$tkyte%ORA12CR1> alter table t modify (c visible);
Table altered.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> desc t;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 A                                                              NUMBER(38)
 B                                                              NUMBER(38)
 C                                                              NUMBER(38)


Note: that could cause some serious invalidations in your database - so make sure you are a) aware of that b) willing to pay that penalty and c) really really really want A to be first in the table!
POST A COMMENT

8 Comments:

Blogger Sergey11g said....

If I did it with the 815th column of table on Exadata, would Storage Indexes become aware of this column?

Tue Jul 02, 02:18:00 PM EDT  

Blogger Thomas Kyte said....

@Sergey11g - you know what, I don't know. We'll have to wait for Exadata with 12c's full capabilities to see

Tue Jul 02, 02:19:00 PM EDT  

Anonymous Jeff Jaccobs said....

Is this really moving the column in the underlying data blocks, or is this simply an artifact of manipulation of the column meta-data in the data dictionary? (I would hope it's the latter, i.e. invisible adjust column ids, making visible uses last id + 1 for column).

Tue Jul 02, 03:10:00 PM EDT  

Blogger Thomas Kyte said....

it is metadata magic, no block reformatting or anything.

Tue Jul 02, 03:12:00 PM EDT  

Blogger Muhammad Abdul Halim said....

Really Interesting. thanks Tom.

Regards
Halim

Tue Jul 02, 03:54:00 PM EDT  

Blogger SYSDBA said....

This behavior is described in the official 12c documentation under "Invisible Columns and Column Ordering" >> http://docs.oracle.com/cd/E16655_01/server.121/e17636/tables.htm#ADMIN13867

Tue Jul 02, 04:09:00 PM EDT  

Blogger Hoek said....

"Metadata magic"...oh dear, now it's just waiting for the first cowboy who will try to see what happens when we change the order of datadictionary tables ;)

On the other hand, this one from now on has another new answer : http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2171590030349

Tue Jul 02, 07:06:00 PM EDT  

Blogger Hoek said....

It's a documented 'trick' ;)
http://docs.oracle.com/cd/E16655_01/server.121/e17636/tables.htm#sthref2239

Fri Jul 12, 03:48:00 AM EDT  

POST A COMMENT

<< Home