Introduction

Oracle Database 12c has brought a never ending list of new features and today I would like to talk about another new feature from this list. Oracle had introduced invisible indexes in Oracle 11g (11.1) which gave us the power to create an index in INVISIBLE mode and then evaluate its functioning before exposing it to database queries.

Oracle has extended that feature of invisibility one step further with the introduction of Oracle database 12c. We can now even create table columns in the INVISIBLE mode, preventing them being exposed to database queries unless explicitly mentioned.

Lets walk through this feature and explore, what it has to offer.

Making columns invisible

We can define a table column in invisible mode either while creating the table using CREATE TABLE statement or later using ALTER TABLE statement. The syntax for defining a column for both of these cases are as follows:

----//
----// syntax to define invisible column with CREATE TABLE //----
----//
CREATE TABLE table_name
(
column_name data_type INVISIBLE column_properties
)

----//
----// syntax to make an existing column invisible //----
----//
ALTER TABLE table_name MODIFY column_name INVISIBLE

In the following example, I am creating a table called TEST_TAB_INV with two invisible columns with column name CONTACT and ADDRESS respectively.

----//
----// creating table TEST_TAB_INV with two invisible columns //----
----//
SQL> create table TEST_TAB_INV
  2  (
  3  id number not null,
  4  name varchar2(15) not null,
  5  join_date date not null,
  6  contact number invisible not null, ----// invisible column, but defined as mandatory //----
  7  address varchar(200) invisible ----// invisible column, defined as optional //----
  8  );

Table created.

SQL> alter table TEST_TAB_INV add constraint PK_TEST_TAB_INV primary key (id);

Table altered.

SQL>

As you can observe, I have defined one of the invisible column (CONTACT) as MANADATORY using the NOT NULL option, while defined the other one (ADDRESS) as optional. The intention behind creating two different type of invisible columns is to test the behaviour of this new feature in case of MANADATORY and OPTIONAL column values.

Listing invisible columns

In general we use the DESCRIBE command to list the columns defined for a table. Lets see, what DESC command shows when we create a table with invisible columns.

----//
----// DESC command doesn't show invisible columns by default //----
----//
SQL> desc TEST_TAB_INV
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(15)
 JOIN_DATE                                 NOT NULL DATE

The DESC[RIBE] command is not showing the invisible columns that we had defined during table creation. This is the default behaviour of invisible columns and we need to set COLINVISIBLE to ON to be able to view the invisible columns using DESC command as show below

----//
----// set COLINVISIBLE to ON to be able to list invisible columns with DESC command //----
----//
SQL> SET COLINVISIBLE ON

----//
----// DESC now lists the invisible columns as well //----
----//
SQL> desc TEST_TAB_INV
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(15)
 JOIN_DATE                                 NOT NULL DATE
 CONTACT (INVISIBLE)                       NOT NULL NUMBER
 ADDRESS (INVISIBLE)                                VARCHAR2(200)

We can alternatively query DBA/ALL/USER_TAB_COLS views to find the invisible columns defined for a table as shown below. If a column is marked as YES for the hidden_column property, it is treated as a invisible column.

----//
----// querying invisible columns from dictionary views //----
----//
SQL> select table_name,column_name,column_id,hidden_column from dba_tab_cols where table_name='TEST_TAB_INV';

TABLE_NAME                COLUMN_NAME           COLUMN_ID HID
------------------------- -------------------- ---------- ---
TEST_TAB_INV              ID                            1 NO
                          NAME                          2 NO
                          JOIN_DATE                     3 NO
                          CONTACT                         YES
                          ADDRESS                         YES

As we can observe, Oracle has not allocated any COLUMN_ID for the invisible columns and that is why invisible columns doesn't qualify for column ordering. However, Oracle keeps track of the invisible columns using an internal ID as shown below.

----//
----// Oracle maintains only internal column IDs for invisible columns //----
----//
SQL> select table_name,column_name,column_id,internal_column_id,hidden_column from dba_tab_cols where table_name='TEST_TAB_INV';

TABLE_NAME        COLUMN_NAME           COLUMN_ID INTERNAL_COLUMN_ID HID
----------------- -------------------- ---------- ------------------ ---
TEST_TAB_INV      ID                            1                  1 NO
TEST_TAB_INV      NAME                          2                  2 NO
TEST_TAB_INV      JOIN_DATE                     3                  3 NO
TEST_TAB_INV      CONTACT                                          4 YES
TEST_TAB_INV      ADDRESS                                          5 YES

Inserting records without column reference

Lets try to insert a record in the table TEST_TAB_INV that we had created earlier without referring the column names. In the following example, I am not passing values for the invisible columns CONTACT and ADDRESS.

----//
----// insert record without column_list when one of the invisible column is defined as mandatory //----
----// However, value is not passed for mandatory invisible column //---
----//
SQL> insert into TEST_TAB_INV values (1,'abbas',sysdate);
insert into TEST_TAB_INV values (1,'abbas',sysdate)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("MYAPP"."TEST_TAB_INV"."CONTACT")

Oracle did not allow me to insert a record, as the column CONTACT was defined as a mandatory column (NOT NULL) even though it was defined as invisible. Ok, lets pass a value for CONTACT column too.

----//
----// insert record without column_list when one of the invisible column is defined as mandatory //----
----// and value passed for the mandatory invisible column //----
----// 
SQL>  insert into TEST_TAB_INV values (1,'abbas',sysdate,999999999);
 insert into TEST_TAB_INV values (1,'abbas',sysdate,999999999)
             *
ERROR at line 1:
ORA-00913: too many values

We are still not allowed to insert a record. Lets pass the values for all the table columns

----//
----// insert record without column_list but values passed for all columns (visible and invisible) //----
----//
SQL> insert into TEST_TAB_INV values (2,'fazal',sysdate,888888888,'bangalore');
insert into TEST_TAB_INV values (2,'fazal',sysdate,888888888,'bangalore')
            *
ERROR at line 1:
ORA-00913: too many values

We are still not allowed to insert a record. The reason is, when we try to insert a record without explicitly referring the table columns; Oracle only considers the columns that are visible by default.

In the first case of insert statement, we had passed values for all the visible columns. However, since the invisible column CONTACT was defined as mandatory; Oracle did not allow us to insert that record and threw the error ORA-01400: cannot insert NULL into ("MYAPP"."TEST_TAB_INV"."CONTACT")

In the second and third case of insert statements, although we had passed additional values for CONTACT and ADDRESS columns; Oracle did not recognize those columns (as those are invisible) and threw the error ORA-00913: too many values. This error indicates that Oracle was expecting less number of column values than what is supplied in the insert statement.

Lets change the invisible column CONTACT from mandatory (NOT NULL) to optional (NULL) and check if we are allowed to insert a record without column reference.

----//
----// making all the invisible columns as optional //----
----//
SQL> alter table TEST_TAB_INV modify CONTACT NULL;

Table altered.

SQL> set COLINVISIBLE ON

SQL> desc TEST_TAB_INV
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 ID                                  NOT NULL NUMBER
 NAME                                NOT NULL VARCHAR2(15)
 JOIN_DATE                           NOT NULL DATE
 CONTACT (INVISIBLE)                          NUMBER		---> Invisible and optional (NULL)
 ADDRESS (INVISIBLE)                          VARCHAR2(200)	---> Invisible and optional (NULL)

Now, lets insert a record without column reference and without passing any values for invisible columns

----//
----// insert record without column_list when all invisible columns are optional //---- 
----//
SQL> insert into TEST_TAB_INV values (1,'john',sysdate);

1 row created.

SQL> commit;

Commit complete.

Yes, we are now allowed to insert record without column reference. This was possible as all of the invisible columns (CONTACT and ADDRESS) are now allowed to have NULL.

Inserting records with column reference

When we insert records in a table by referring the table columns, we are allowed to insert data in the invisible columns as well as shown below.

----//
----// insert record in to invisible columns with explicit column reference //----
----//
SQL> insert into TEST_TAB_INV (id,name,join_date,contact) values (2,'mike',sysdate,999999999);

1 row created.

----//
----// insert record in to invisible columns with explicit column reference //----
----//
SQL>  insert into TEST_TAB_INV (id,name,join_date,contact,address) values (3,'peter',sysdate,888888888,'bangalore');

1 row created.

SQL> commit;

Commit complete.

As we can see, even though if a column is defined as invisible; we would still be allowed to populate it with data provided the column is explicitly referred in the insert statements.

Query table having invisible columns

When we select without column reference (SELECT * FROM) from a table having invisible columns, Oracle only returns the result from the visible columns as show below.

----//
----// select from table having invisible columns, without column reference //----
----//

SQL>  select * from TEST_TAB_INV;

        ID NAME            JOIN_DATE
---------- --------------- ---------
         1 john            24-SEP-15
         2 mike            24-SEP-15
         3 peter           24-SEP-15

Oracle internally transforms this query to include only the visible columns

#/----
#/---- Oracle transformed the select query to exclude invisible columns -----/
#/----
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST_TAB_INV"."ID" "ID","TEST_TAB_INV"."NAME" "NAME","TEST_TAB_INV"."JOIN_DATE" "JOIN_DATE" FROM "MYAPP"."TEST_TAB_INV" "TEST_TAB_INV"
kkoqbc: optimizing query block SEL$1 (#0)

        :
    call(in-use=1136, alloc=16344), compile(in-use=67704, alloc=70816), execution(in-use=2784, alloc=4032)

kkoqbc-subheap (create addr=0x2b89a1d1fb78)
****************
QUERY BLOCK TEXT
****************
select * from TEST_TAB_INV
---------------------

However, we can still query the data from invisible columns by explicitly referring the column names in the SELECT clause as show below.

----//
----// selecting data from invisible with explicit column reference //----
----//

SQL> select id,name,join_date,contact,address from TEST_TAB_INV;

        ID NAME            JOIN_DATE    CONTACT ADDRESS
---------- --------------- --------- ---------- --------------------
         1 john            24-SEP-15
         2 mike            24-SEP-15  999999999
         3 peter           24-SEP-15  888888888 bangalore

Statistics on Invisible columns

Oracle maintains statistics for all the table columns even if a column is defined as invisible as shown below. Invisible columns also qualify for all type of statistics (histograms, extended statistics, etc.)

----//
----// collecting statistics for table with invisible columns //----
----//
SQL> exec dbms_stats.gather_table_stats('MYAPP','TEST_TAB_INV');

PL/SQL procedure successfully completed.


----//
----// Oracle maintains statistics for invisible columns as well //----
----//
SQL> select owner,table_name,column_name,num_distinct,density,last_analyzed
  2  from dba_tab_col_statistics where table_name='TEST_TAB_INV';

OWNER      TABLE_NAME           COLUMN_NAME          NUM_DISTINCT    DENSITY LAST_ANAL
---------- -------------------- -------------------- ------------ ---------- ---------
MYAPP      TEST_TAB_INV         ADDRESS                         1          1 24-SEP-15
MYAPP      TEST_TAB_INV         CONTACT                         2         .5 24-SEP-15
MYAPP      TEST_TAB_INV         JOIN_DATE                       3 .333333333 24-SEP-15
MYAPP      TEST_TAB_INV         NAME                            3 .333333333 24-SEP-15
MYAPP      TEST_TAB_INV         ID                              3 .333333333 24-SEP-15

Making columns visible

We can convert a invisible to visible by modifying the column property using ALTER TABLE statement. The syntax for making a column visible is

----//
----// Syntax for changing a column from INVISIBLE to VISIBLE //----
----//
ALTER TABLE table_name MODIFY column_name VISIBLE;

Lets make the column CONTACT visible in our table TEST_TAB_INV and observer what changes the operation brings along.

----//
----// changing column CONTACT in table TEST_TAB_INV to VISIBLE //----
----//
SQL> alter table TEST_TAB_INV modify CONTACT visible;

Table altered.
 
----//
----// DESC command now lists the changed column //----
----//
SQL> desc TEST_TAB_INV
Name                                Null?    Type
----------------------------------- -------- ------------------------
ID                                  NOT NULL NUMBER
NAME                                NOT NULL VARCHAR2(15)
JOIN_DATE                           NOT NULL DATE
CONTACT                             NOT NULL NUMBER


SQL> SET COLINVISIBLE ON

SQL> desc TEST_TAB_INV
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 ID                                  NOT NULL NUMBER
 NAME                                NOT NULL VARCHAR2(15)
 JOIN_DATE                           NOT NULL DATE
 CONTACT                             NOT NULL NUMBER
 ADDRESS (INVISIBLE)                          VARCHAR2(200)

When we make a column visible, it gets listed with the DESCRIBE command. Further, the column is assigned a column ID as well as the column is marked as NOT HIDDEN which can be verified from DBA/ALL/USER_TAB_COLS view as shown below.

 
----//
----// column changed to visible, is allocated a column ID  //----
----// and marked as NO for hidden_column flag //----
----//
SQL>  select table_name,column_name,column_id,hidden_column from dba_tab_cols where table_name='TEST_TAB_INV';

TABLE_NAME                COLUMN_NAME           COLUMN_ID HID
------------------------- -------------------- ---------- ---
TEST_TAB_INV              ADDRESS                         YES
                          CONTACT                       4 NO
                          JOIN_DATE                     3 NO
                          NAME                          2 NO
                          ID                            1 NO

As we can observe, when we change a invisible column to visible, it is placed as the last column in the visible column list. Since the column CONTACT is now made visible, it is exposed to SELECT queries (without column reference) as shown below.

 
----//
----// new visible column is now exposed to SELECT queries (without column reference) //----
----//
SQL> select * from TEST_TAB_INV;

        ID NAME                 JOIN_DATE    CONTACT
---------- -------------------- --------- ----------
         1 abbas                21-SEP-15  999999999
         2 fazal                21-SEP-15  888888888

Indexing Invisible columns

We are also allowed to create index on invisible columns the same way we create index for a generic column.

 		 
----//
----// creating index on invisible columns //----
----//
SQL> create index idx_TEST_TAB_INV on TEST_TAB_INV (name,contact,address);

Index created.

Lets check if Oracle is able to use that index.

 		 
----//
----// checking if index would be used by optimizer //----
----//
SQL> explain plan for select * from TEST_TAB_INV where address='bangalore';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 3483268732

--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TAB_INV     |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | IDX_TEST_TAB_INV |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ADDRESS"='bangalore')
       filter("ADDRESS"='bangalore')

15 rows selected.

SQL> 

As we can observe, Oracle can utilize an index defined on invisible columns. From the above example, we can also conclude that invisible columns can also be used as query predicates

Conclusion

We have explored Oracle 12c's new feature of defining a column in Invisible mode. Following are the conclusions derived from the observations.

  • Invisible column's are not returned while using SELECT * FROM TABLE statement
  • Data can be still queried from invisible column, provided the column names are explicitly referred in the SELECT clause
  • Records can be inserted in table having invisible columns with INSERT INTO table_name VALUES statement, provided none of the invisible columns are defined as mandatory (NOT NULL)
  • Data can be populated in to invisible columns provided the invisible columns are explicitly referred in the insert statement like INSERT INTO table_name (column_list) VALUES
  • Oracle maintains statistics on invisible columns
  • Invisible columns can be be indexed as well as used as query predicates
  • Invisible columns are not allocated a column ID and are tracked by an internal ID
  • When a invisible column is made visible, it is placed as the last visible column and gets a column ID in that order

or in other words....

  • Invisible column inherits all the properties of that of a visible column with just one exception that it is not visible unless referenced explicitly.

Invisible columns can be useful to test the impact of column addition on the application, before actually exposing the column to application queries. Invisible columns can also be used as a trick to change column ordering for tables, we shall explore that area in an upcoming article

Reference

http://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN14217