12c Invisible Columns (The Invisible Man) November 19, 2013
Posted by Richard Foote in 12c, Invisible Columns, Oracle Indexes.trackback
Oracle 12c introduced the ability to make user specified columns “Invisible” such that they’re invisible by default and have to be explicitly referenced to be accessed. So a simple SELECT * won’t display columns defined as invisible, but they will be displayed if explicitly referenced in the SELECT column list.
From an indexing perspective, columns can still be indexed and considered by the cost based optimizer regardless of whether the column is invisible or not. So don’t be confused by an invisible index with an index on an invisible column, they’re two entirely different concepts.
Unless specified otherwise, a column is visible by default. So the following BOWIE table is defined with three visible columns:
SQL> create table bowie (id number, code number, name varchar2(30)); Table created. SQL> insert into bowie select rownum, mod(rownum,10000), 'DAVID BOWIE' from dual connect by level <=100000; 100000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'BOWIE', method_opt=>'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed.
Let’s now create an index on the CODE column:
SQL> create index bowie_code_i on bowie(code); Index created.
Each column in the table is defined with a unique COLUMN_ID, which determines the logical ordering of the column within the table:
SQL> desc bowie
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
CODE NUMBER
NAME VARCHAR2(30)
SQL> select column_id, column_name from user_tab_columns where table_name='BOWIE';
COLUMN_ID COLUMN_NAME
---------- ---------------
1 ID
2 CODE
3 NAME
SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column
from user_tab_cols where table_name = 'BOWIE1';
COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME HID VIR
---------- ----------------- ------------------ --------------- --- ---
1 1 1 ID NO NO
2 2 2 CODE NO NO
3 3 3 NAME NO NO
If we now run a simple query on BOWIE, we notice the index is used by the CBO and the columns are listed in the order of COLUMN_ID:
SQL> select * from bowie where code=42;
ID CODE NAME
---------- ---------- ------------------------------
90042 42 DAVID BOWIE
70042 42 DAVID BOWIE
80042 42 DAVID BOWIE
42 42 DAVID BOWIE
10042 42 DAVID BOWIE
20042 42 DAVID BOWIE
30042 42 DAVID BOWIE
60042 42 DAVID BOWIE
50042 42 DAVID BOWIE
40042 42 DAVID BOWIE
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 210 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE | 10 | 210 | 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BOWIE_CODE_I | 10 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Let’s now make the CODE column INVISIBLE:
SQL> alter table bowie modify code invisible; Table altered.
We notice the CODE column no longer has a COLUMN_ID defined and that all columns that follow the CODE column (in this case just the NAME column) have the COLUMN_ID value decremented by 1:
SQL> desc bowie
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(30)
SQL> select column_id, column_name from user_tab_columns where table_name='BOWIE';
COLUMN_ID COLUMN_NAME
---------- ---------------
2 NAME
CODE
1 ID
SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column
from user_tab_cols where table_name = 'BOWIE1';
COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME HID VIR
---------- ----------------- ------------------ --------------- --- ---
1 1 1 ID NO NO
2 2 CODE YES NO
2 3 3 NAME NO NO
If we now re-run the same SELECT * query:
SQL> select * from bowie where code=42;
ID NAME
---------- ------------------------------
20042 DAVID BOWIE
90042 DAVID BOWIE
70042 DAVID BOWIE
80042 DAVID BOWIE
30042 DAVID BOWIE
60042 DAVID BOWIE
50042 DAVID BOWIE
40042 DAVID BOWIE
42 DAVID BOWIE
10042 DAVID BOWIE
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 210 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE | 10 | 210 | 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BOWIE_CODE_I | 10 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
We notice the CODE column is no longer displayed even though it’s still explicitly reference in the WHERE condition and that the index on the now invisible CODE column is still used by the CBO.
To display the CODE column, we need to set colinvisible on in sql*plus, explicitly reference it in the column list, explicitly reference it when inserting a row with a CODE value, etc. etc. :
SQL> set colinvisible on
SQL> desc bowie
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(30)
CODE (INVISIBLE) NOT NULL NUMBER
SQL> select id, code, name from bowie where code = 42;
ID CODE NAME
---------- ---------- ------------------------------
20042 42 DAVID BOWIE
90042 42 DAVID BOWIE
70042 42 DAVID BOWIE
10042 42 DAVID BOWIE
80042 42 DAVID BOWIE
30042 42 DAVID BOWIE
60042 42 DAVID BOWIE
50042 42 DAVID BOWIE
40042 42 DAVID BOWIE
42 42 DAVID BOWIE
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 210 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE | 10 | 210 | 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BOWIE_CODE_I | 10 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
SQL> insert into bowie (id,code,name) values (100001, 42, 'ZIGGY STARDUST');
1 row created.
SQL> commit;
Commit complete.
So a column is only “invisible” by default, it can still be referenced and accessed by those who have knowledge of its existence.
If we now make the column VISIBLE again:
SQL> alter table bowie modify code visible;
Table altered.
SQL> select column_id, column_name from user_tab_columns where table_name='BOWIE';
COLUMN_ID COLUMN_NAME
---------- ---------------
2 NAME
3 CODE
1 ID
SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column
from user_tab_cols where table_name = 'BOWIE1';
COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME HID VIR
---------- ----------------- ------------------ --------------- --- ---
2 3 3 NAME NO NO
3 2 2 CODE NO NO
1 1 1 ID NO NO
We notice the CODE column has been assigned a COLUMN_ID again but not the same one as it was previously. Basically, when a column is made visible, it’s assigned the next highest available COLUMN_ID, in this case the value 3.
Note the original, internal physical column order is still maintained via INTERNAL_COLUMN_ID (e.g. a value of 2 for the CODE column) as Oracle of course still needs a way to determine which column physically belongs where within the table.
So by making the previously non-last column invisible and then visible again, we have effectively changed the logical order of the column within the table:
SQL> desc bowie
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(30)
CODE NUMBER
SQL> select * from bowie where code = 42;
ID NAME CODE
---------- ------------------------------ ----------
90042 DAVID BOWIE 42
100001 ZIGGY STARDUST 42
70042 DAVID BOWIE 42
80042 DAVID BOWIE 42
42 DAVID BOWIE 42
10042 DAVID BOWIE 42
20042 DAVID BOWIE 42
30042 DAVID BOWIE 42
60042 DAVID BOWIE 42
50042 DAVID BOWIE 42
40042 DAVID BOWIE 42
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 210 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE | 10 | 210 | 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BOWIE_CODE_I | 10 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
We notice the CODE column is now displayed last by default.
They are some limitations to invisible columns, in that they can’t be used for external, cluster or index organized tables.
Making columns invisible is a neat way to now hide the existence columns within a table. It’s also a neat way to now easily change the default ordering of columns within a table …
Richard Foote's Oracle Blog 
hi Richard, Nicely explained article as usual! However a big question here…”Why would we be required to hide the column? What’s a point/benefit in using this feature? ”
Is this feature of any significance from Administration point of view or just a so-called feature by Oracle corp ?!!
LikeLike
Hi Pratik
It might have some use when you want to make changes to a table without disrupting existing applications (by adding an invisible column, you’re not going to stuff anything up) and then make sure anything new that does need the column explicitly references it.
LikeLike