Invisible Columns in Oracle Database 12c

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 400000020 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

	We go ahead and dump the block containing the row in order to look for information regarding 
the column ordering.


SQL> select dbms_rowid.rowid_relative_fno(rowid) File#, dbms_rowid.rowid_block_number(rowid) 
Block# from tabela_col_inv;
 
      
FILE#   	  BLOCK# 
----------    ---------- 	 
6	          335

SQL> alter system dump datafile 6 block 335;

System altered.

data_block_dump,data header at 0x7f0b49520064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x7f0b49520064
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x16
fseo=0x1f79
avsp=0x1f63
tosp=0x1f63
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f89
0x14:pri[1]     offs=0x1f79
block_row_dump:
tab 0, row 0, @0x1f79
tl: 16 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [ 3]  c2 02 02
col  1: [ 3]  c2 02 03
col  2: *NULL*   <<<<<<< INVISIBLE COLUMN
col  3: [ 3]  c2 02 05
end_of_block_dump


SQL> select dump(coluna1,16) col1, dump(coluna2,16) col2, dump(coluna3,16) col3, dump(coluna4,16)
 col4 from tabela_col_inv;

COL1		      COL2		   COL3		        COL4 
-------------------- -------------------- -------------------- -------------------- 
Typ=2 Len=2: c2,2    Typ=2 Len=2: c2,3    NULL		        Typ=2 Len=2: c2,5 





	Partitioning




	Table partitioning based on invisible columns is also supported as follows:

SQL> create table tabela_col_inv4 (coluna1  number, coluna2 INVISIBLE generated always as (coluna1+1) 
 virtual) partition by range(coluna2) (partition part1 values less than(100), partition part2 values less 
 than(maxvalue) ); 
   
Table created. 


By Alex Zaballa, Oracle Ace and Daniel Da Meda (OCM)