“Invisible columns”: This is a new feature introduced in Oracle 12c that got me all excited.
In short you can now have hidden columns in a table that are not visible to the standard SELECT, INSERT clauses if you do not want to.
Some usage for this:
Legacy application queries
I hate how you have to jump the loops, make weird JOIN clauses and maybe create a new table just to avoid adding a new column to a table used by lots of legacy queries. If you work on big applications that do a lots of queries you really hate to change 30 queries just because you might need an extra column. Now you can actually add a column without changing the old queries. How cool is that.
Invisible column partitioning
This is another great use. Do you need to partition a huge table and you lack a right column to do it ? Just add an invisible column and partition with it.
It can be that you have a table of operation entries OPERATION but the time when the operation was done is stored in an AUDIT table. There was no simple way to partition OPERATION after a timestamp until now.
Add a new invisible column:
SQL> ALTER TABLE OPERATION ADD operation_timestamp TIMESTAMP INVISIBLE; Then populate the new column with the data from AUDIT. Basically you can do an update:
SQL> UPDATE table OPERATION set operation_timestamp=...Then partition the table using the new column.
ALTER TABLE OPERATION MODIFY
PARTITION BY RANGE (operation_timestamp) INTERVAL (@interval)
( PARTITION P1 VALUES LESS THAN (@p1max),
PARTITION P2 VALUES LESS THAN (@p2max)
) ONLINE
);See a great Oracle article here Tips and Tricks: Invisible Columns in Oracle Database 12c