Invisible Columns in Oracle Database 12 c

Introduction

In Oracle Database 12c, table columns can be defined as invisible either during its creation with the CREATE TABLE command or by modifying existing table columns via the ALTER TABLE statement. By default, table columns are always visible. Once a column has been set to invisible, it can be reverted back to visible using the ALTER TABLE statement.

Among other operations, the following ones will not display or work with invisible table columns:

• SELECT * FROM in SQL instructions
• The DESCRIBE statement when used in either SQL*PLUS or via Oracle Call Interface (OCI)
• %ROWTYPE attribute in PL/SQL variable declarations

From the table indexes standpoint, invisible columns are still available for indexing and such indexes and are available to the optimizer during the access path selection.

The following example shows the creation of a table where column3 is defined as invisible using the INVISIBLE keyword:

SQL> CREATE TABLE tabela_col_inv (
coluna1 NUMBER,    
coluna2 NUMBER,    
coluna3 NUMBER INVISIBLE,    
coluna4 NUMBER  );
   
Table created.

By Default, invisible columns do not show up when the DESCRIBE statement is issued against the table:

SQL> desc tabela_col_inv   
Name		                          Null?    Type  
---------------------------------------- -------- ----------------------------
COLUNA1			 	                    NUMBER  
COLUNA2			 	                    NUMBER  
COLUNA4		 		                    NUMBER

A SQL*PLUS session can be set to display invisible columns by setting the new switch COLINVISIBLE to ON as follows:

SQL> SET COLINVISIBLE ON 
SQL> desc tabela_col_inv  
Name			                   Null?    Type  
----------------------------------------- -------- ----------------------------  
COLUNA1					            NUMBER  
COLUNA2					            NUMBER  
COLUNA4					            NUMBER  
COLUNA3 (INVISIBLE)		                    NUMBER

While being invisible, the column can still be accessed via DML and DDL statements as follows:

SQL> INSERT INTO tabela_col_inv (coluna1,coluna2,coluna3,coluna4) VALUES (100,200,300,400);  
1 row created. 
SQL> SELECT  coluna1,coluna2,coluna3,coluna4 FROM tabela_col_inv;     
COLUNA1    COLUNA2    COLUNA3    COLUNA4 
---------- ---------- ---------- ----------        
100	   200	      300	 400

However, when performing inserts into tables containing invisible columns, the column list must be defined in the statement. Failing to do so, will result in an ORA-00913 error as follows:

SQL> INSERT INTO tabela_col_inv VALUES (101,102,103,104); 
INSERT INTO tabela_col_inv VALUES (101,102,103,104)             
* ERROR at line 1: 
ORA-00913: too many values

However, inserts without column lists are possible if no value is passed to the invisible column and if the it is either nullable or it has a DEFAULT clause value:

SQL> INSERT INTO tabela_col_inv VALUES (101,102,104); 
1 row created.  
SQL> SELECT * FROM tabela_col_inv;
     
COLUNA1       COLUNA2	    COLUNA4 
----------    ----------    ----------        
100	      200	    400
101           102           104

Invisible Columns and Check Constraints

Check Constraints defined on invisible columns will continue to work just as in visible columns:

SQL> CREATE TABLE tabela_col_inv2 (coluna1 NUMBER not null,   
coluna2 NUMBER INVISIBLE not null  );  
Table created. 
SQL> desc tabela_col_inv2  
Name			                  Null?    Type  
----------------------------------------- -------- ----------------------------
COLUNA1				          NOT NULL NUMBER  
SQL>insert into tabela_col_inv2 values(1);
 
insert into tabela_col_inv2  values(1) 
* 
ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."TABELA_COL_INV2"."COLUNA2")

Creating Invisible Virtual Columns

It is also possible to create an invisible virtual column by combining both features as follows:

SQL> create table tabela_col_inv3 ( coluna1  number, coluna2 INVISIBLE generated always 
  as (coluna1+1) virtual); 
 
Table created.

Column Ordering

As the column is reset to visible, it will be displayed as the last column of the table:

SQL> ALTER TABLE tabela_col_inv MODIFY coluna3 VISIBLE;  
Table altered. 
SQL> SELECT * FROM tabela_col_inv;
     
COLUNA1    COLUNA2    COLUNA4    COLUNA3 
---------- ---------- ---------- ----------        
100	   200	      400	      300
101        102        104
SQL> desc tabela_col_inv  
Name				          Null?    Type  
----------------------------------------- -------- ----------------------------
COLUNA1					           NUMBER  
COLUNA2					           NUMBER  
COLUNA4					           NUMBER  
COLUNA3					           NUMBER

By querying the view SYS.COL$, it becomes clear that the column COL# is modified so that it becomes the last column of the table:

SQL> SELECT name,col#,intcol#,segcol#,
TO_CHAR (property,'XXXXXXXXXXXX') property     
FROM sys.col$   
WHERE obj# = (SELECT obj# FROM sys.obj$ WHERE name = 'TABELA_COL_INV') 
NAME		      COL#	 INTCOL#    SEGCOL#    PROPERTY 
-------------------- ---------- ---------- ---------- ------------- 
COLUNA1 		1         1	      1		  0 
COLUNA2 		2 	  2	      2		  0 
COLUNA3 		4 	  3	      3		  0 
COLUNA4 		3 	  4	      4		  0 
SQL> ALTER TABLE tabela_col_inv MODIFY coluna4 INVISIBLE;  
Table altered.

Checking the Database Dictionary

SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, 
virtual_column from user_tab_cols where table_name ='TABELA_COL_INV'; 
COLUMN_ID  SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID  COLUMN_NAME	    HID   VIR 
---------- ----------------- ------------------ -------------------- ---  ---
1          1                   1 		  COLUNA1 	     NO   NO
2	   2		       2 		  COLUNA2 	     NO   NO
3 	   3		       3                  COLUNA3 	     NO   NO
 	   4 		       4 		  COLUNA4 	     YES  NO

When a table column is set to invisible, the content of the column property from table SYS.COL$ is set to the following value:

SQL> SELECT name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') property
FROM sys.col$
WHERE obj# =
(
  SELECT obj# FROM sys.obj$ WHERE name = 'TABELA_COL_INV'
);
  2    3    4    5    6  
NAME		   COL#    	INTCOL#   SEGCOL# 	PROPERTY
-------------------- ---------- ---------- ---------- -------------
COLUNA1 		      1 	 1	    1		  0
COLUNA2 		      2 	 2	    2		  0
COLUNA3 		      3 	 3	    3		  0
COLUNA4 		      0 	 4	    4  SQL> ALTER TABLE tabela_col_inv MODIFY coluna4 VISIBLE;   
Table altered. 
SQL> ALTER TABLE tabela_col_inv MODIFY coluna3 INVISIBLE;  
Table altered.
SQL> desc tabela_col_inv  
Name			                   Null?    Type  
----------------------------------------- -------- ----------------------------  
COLUNA1					            NUMBER  
COLUNA2					            NUMBER  
COLUNA4					            NUMBER  
COLUNA3 (INVISIBLE)			            NUMBER
SQL> truncate table tabela_col_inv;  
Table truncated
SQL> INSERT INTO tabela_col_inv (coluna1,coluna2,coluna3,coluna4) VALUES (100,200,null,400);  
1 row created. 
SQL> SELECT  coluna1,coluna2,coluna3,coluna4 FROM tabela_col_inv;
     
COLUNA1    COLUNA2    COLUNA3    COLUNA4 
---------- ---------- ---------- ----------        
100	   200			 400