12c - Silly little trick with invisibility...
Posted
by noreply(at)blogger.com (Thomas Kyte)
on Oracle Blogs
See other posts from Oracle Blogs
or by noreply(at)blogger.com (Thomas Kyte)
Published on Tue, 2 Jul 2013 18:03:37 +0000
Indexed on
2013/07/02
23:12 UTC
Read the original article
Hit count: 270
Filed under:
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)
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!
© Oracle Blogs or respective owner
