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)