Tuesday, June 25, 2013

Oracle 12c New Features - Invisible Columns in Tables and Indexes

In 12c it is now possible to create tables with INVISIBLE columns, or make existing columns invisible.  This will avoid having to modify tables at a later date to add new columns, while hiding columns from developers so they don't include incomplete data in reports.

Example - create a table with columns ID, TEXT

SQL> CREATE TABLE TEST_TABLE (ID NUMBER, TEXT VARCHAR2(100));

Table created.

SQL> desc test_table;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 ID                                                 NUMBER
 TEXT                                               VARCHAR2(100)

The database knows about the TEXT column, which causes the following insert to fail.

SQL> insert into test_table values ('1');
insert into test_table values ('1')
            *
ERROR at line 1:
ORA-00947: not enough values

Now we set the TEXT column to be invisible.

SQL> alter table TEST_TABLE modify (TEXT invisible);

Table altered.

When we describe the table we do not see the TEXT column.

SQL> desc test_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 ID                                                 NUMBER

And the insert also works.

SQL> insert into test_table values ('1');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_table;

ID
--
 1

The invisible columns are still available for compiling views and code.

SQL> create view TEST_VIEW as select ID, TEXT from TEST_TABLE;

View created.

SQL> desc test_view
 Name                                     Null?    Type
 ---------------------------------------- -------- -------------
 ID                                                NUMBER
 TEXT                                              VARCHAR2(100)

SQL> select * from test_view;

ID TEXT
-- --------------------------------------------------------------
 1

SQL>

So if you make a table column invisible, you should probably do the same for views based on the table.

SQL> create or replace view TEST_VIEW (ID, TEXT INVISIBLE) as select ID, TEXT from TEST_TABLE;

View created.

SQL> desc test_view
 Name                                          Null?    Type
 --------------------------------------------- -------- -----
 ID                                                     NUMBER

SQL>