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.

Invisible Column Business Justification?

Why would you make a column invisible? There are not many reasons why you would suddenly make a column invisible, but one situation that comes to mind is where you might want to test the waters before dropping the column from table – to figure out if something breaks or someone yells. Oracle provides an option to mark a column as UNUSED before you DROP, and do ALTER TABLE … DROP UNUSED COLUMNS at a later time. Once you mark a column as UNUSED, there is no going back to undo the action. So, marking it INVISIBLE before drop is a good idea. Another use could be that you have a running application used by many teams – before you collaborate with everyone on a table change, you could test the changes in the table by creating the new column as invisible, do your basic tests, then talk to the other teams and make the column visible to all.

Invisible Column Behaviour:

Let me show you the behaviour of INVISIBLE column and UNUSED column in the data dictionary. Create a table with the following characteristics:

• Columns 1, 4 & 7 are regular columns with no specialty
• x2 is invisible
• x3 is virtual
• x5 is invisible and virtual
• x6 is identity

Query the column properties of the table you just created:

Notice that all columns have USER_GENERATED as YES, and that the invisible columns are marked as HIDDEN_COLUMN=YES. Now, mark x4 for drop and x2 visible.

Query the properties again…

The x4 column name marked to be dropped was renamed, and was given a system-generated name. The rename is to facilitate adding a column with the same name to the table. The column x4 also got property changes – USER_GENERATED became NO and HIDDEN_COLUMN changed to YES. Also, the COLUMN_ID is released, so the column will not be visible in “SELECT *” and “DESCRIBE”. The UNUSED column still maintains the same INTERNAL_COLUMN_ID.

When x2 column is made VISIBLE, it got a new COLUMN_ID assigned (the highest available, thus the column becomes the last column in “SELECT *” and “DESCRIBE”). Its hidden status changed to NO.

Impact of dropping UNUSED Columns:

What happens to the ID columns, when the UNUSED column is dropped?

The COLUMN_ID was reordered after the unused column is dropped (X4 was dropped, hence X6 and X7 got new column ids – X5 never had a column id assigned as it is INVISIBLE). The internal column id, which includes id for INVISIBLE columns was also adjusted. After the column was dropped, the INTERNAL_COLUMN_ID was also adjusted.

Tip: By default SQL*Plus DESCRIBE will not show the invisible columns in a table. If you want to see the invisible columns in DESCRIBE, use SET COLINVISIBLE ON.

Query table with invisible Column:

When we SELECT * FROM without reference columns from a table with invisible columns, only the visible columns are returned as per the below screenshot.

However, we can still query invisible column(s) by explicitly mentioning them in the SELECT statement as per the below screenshot:

Invisible Column Statistics:

Oracle 12c also maintains statistics on invisible columns as with generic visible columns.

Indexing Invisible Columns:

Indexing on invisible columns is similar to indexing on a generic visible column.

Let’s check if Oracle is able to use the INDEX created above, named “HIDDENT_COL_TEST_INDX”.

We can see from the above screenshot that Oracle can utilise the index defined on an invisible column.

Conclusion:

We have explored the new Oracle 12c feature of defining invisible columns and derived the following conclusions::

• Invisible columns in Oracle 12c provide the flexibility of adding columns to existing tables without the fear of breaking an application.
• Making a column invisible is a neat way to hide the existing columns within a table.
• Invisible columns are not returned while using SELECT * FROM statement unless explicitly referred to in the SELECT statement.
• Invisible columns are indexable, and can be used as part of the cost optimiser.
• Invisible columns don’t get a column ID but are tracked by the Internal Column ID.

In a nutshell, an invisible column inherits all the features of a normal visible column with the exception of not being visible unless it is explicitly referenced.