#Oracle 12c invisible columns

By | October 1, 2017

“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:

Then populate the new column with the data from AUDIT. Basically you can do an update:

Then partition the table using the new column.

See a great Oracle article here Tips and Tricks: Invisible Columns in Oracle Database 12c

Advertisements

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.