Invisible Columns in Oracle Database 12c

Invisible Columns in 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 table’s column is set to invisible, following key statements will not work for invisible column:

• SELECT * FROM in SQL Statement
• DESCRIBE statement
• %ROWTYPE in PL/SQL variable declaration

For indexes purpose, invisible columns will still available for indexing and such indexes can be used for cost based optimizer purposes. So, don’t be confused by 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 for drop got renamed, and got 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 table having invisible columns than only visible columns(s) is returned as per below screenshot

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

Invisible column Statistic:

Oracle 12c also maintain statistic on invisible column like generic visible column

Indexing Invisible Column:

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

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

Conclusion:

We have explored Oracle 12c new feature of defining invisible column(s). Following are the conclusion derived from the above:

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

In nutshell, Invisible column inherits all the features of the normal visible column with an exception of not visible unless referenced explicitly.