Site icon Voina Blog (a tech warrior's blog)

#Oracle 12c invisible columns

Advertisements

“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

Exit mobile version