Invisible Columns en Oracle Database 12C

[ 2016-12-20 ]

Invisible Columns en Oracle Database 12C

In Oracle Database 12c, you can set a column in table as invisible either during CREATE TABLE or modifying existing table via ALTER TABLE command. By default, table columns are always visible. When you make it invisible, the COL# column in COL$ dictionary is updated to 0, and so is not included in the “SELECT *” or “INSERT INTO VALUES” statements unless specifically selected – it can be reverted back to visible by using ALTER TABLE command.

When you change an invisible column in Oracle 12c database to visible, the COL# assigned will be the highest available, so the column becomes the last column in the table (not storage, only display). So, if you accidentally make a column invisible and correct this by changing it to visible, the column order changes. Therefore, if the application uses “SELECT *” or “INSERT” without column names, they might break!

Once a table’s column is set to invisible, the following key statements will not work for the invisible column:

SELECT * FROM in SQL Statement
DESCRIBE statement
%ROWTYPE in PL/SQL variable declaration
Invisible columns will still available for indexing and such indexes can be used for cost-based optimizer purposes. Don’t confuse an invisible index with an index on invisible column, as they are entirely different concepts.