12c - Silly little trick with invisibility...
- by noreply(at)blogger.com (Thomas Kyte)
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!