Monday, 29 August 2016

Invisible Columns in Oracle Database 12c Release 1

Invisible columns can be useful during application migrations. Making new columns invisible means they won't be seen by any existing applications, but can still be referenced by any new applications, making the online migration of applications much simpler.
  • Invisible Columns
  • Invisible Columns and Column Ordering
  • Mandatory Invisible Columns

Invisible Columns


Making a column invisible means it is no longer seen by SELECT * FROM, SQL*Plus or OCI describes and %ROWTYPE attributes.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id          NUMBER,
  description VARCHAR2(50) INVISIBLE
);

DESC tab1;
 Name   Null?    Type
 ---------- -------- -----------------
 ID               NUMBER

SQL>

INSERT INTO tab1 VALUES (1);
COMMIT;

SELECT * FROM tab1;

ID
------
 1

SQL>

Invisible columns are still available for all actions, provided they are named explicitly.

INSERT INTO tab1 (id, description) VALUES (2, 'TWO');
COMMIT;

SELECT id, description
FROM   tab1;

ID     DESCRIPTION
-----  -------------------
1
2  TWO

SQL>

Some miscellaneous facts about invisible columns include the following.
  • Virtual columns can be made invisible.
  • A table can be partitioned by an invisible column, either during or after table creation.
  • External, cluster and temporary tables can not have invisible columns.
  • User-defined types can not contain invisible attributes.
  • You can not make system generated hidden columns visible.

Invisible Columns and Column Ordering


Invisible columns are not assigned a column order, so if an invisible column is made visible it is listed as the last column of the table.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  a NUMBER,
  b NUMBER,
  c NUMBER INVISIBLE
);

COLUMN column_name FORMAT A15

SELECT column_id,
       column_name,
       hidden_column
FROM   user_tab_cols
WHERE  table_name = 'TAB1'
ORDER BY column_id;

 COLUMN_ID COLUMN_NAME    HID
------------- ---------------           ---
 1               A             NO
 2               B             NO
                  C             YES

SQL>

ALTER TABLE tab1 MODIFY b INVISIBLE;
ALTER TABLE tab1 MODIFY c VISIBLE;

SELECT column_id,
       column_name,
       hidden_column
FROM   user_tab_cols
WHERE  table_name = 'TAB1'
ORDER BY column_id;

 COLUMN_ID COLUMN_NAME    HID
------------- ---------------           ---
 1               A             NO
 2               C             NO
                  B             YES

SQL>

ALTER TABLE tab1 MODIFY b VISIBLE;

SELECT column_id,
       column_name,
       hidden_column
FROM   user_tab_cols
WHERE  table_name = 'TAB1'
ORDER BY column_id;

 COLUMN_ID COLUMN_NAME   HID
------------- ---------------           ---
1               A             NO
2               C             NO
3               B             NO

SQL>

Mandatory Invisible Columns


Making a column invisible does not affect its mandatory/optional status, as shown in the example below.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id          NUMBER NOT NULL,
  description VARCHAR2(50) NOT NULL,
  created_date DATE INVISIBLE NOT NULL
);

COLUMN column_name FORMAT A20

SELECT column_id,
       column_name,
       nullable,
       hidden_column
FROM   user_tab_cols
WHERE  table_name = 'TAB1'
ORDER BY column_id;

 COLUMN_ID COLUMN_NAME             N  HID
-------------- ---------------               -- ------    
1             ID            N NO
2             DESCRIPTION             N NO
                CREATED_DATE    N YES

SQL>

INSERT INTO tab1 VALUES (1, 'ONE');
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."TAB1"."CREATED_DATE")

SQL>

ALTER TABLE tab1 MODIFY created_date NULL;
INSERT INTO tab1 VALUES (1, 'ONE');

1 row created.

SQL>