Category: Oracle Indexing

Oracle Invisible Columns

Oracle 12C has a new feature called invisible columns. This feature helps us to create invisible columns which cannot be viewed under normal situations, SELECT *, or the DESCRIBE command doesn’t list these columns. But we can create indexes or constraints on these columns. Optimizer uses these columns in the execution plans based on the value of an initialization parameter ‘optimizer_use_invisible_indexes’.

Let’s see how this feature works.

SQL> create table test_tbl
2 (
3 name           varchar2(100 char),
4 age              integer ,
5 emp_code   integer invisible
6 );

Table created.

We had created a table with an invisible column emp_code, now lets check how oracle commands treat this column.

SQL> desc test_tbl;
Name                                                     Null?      Type
—————————————– ——– —————————-
NAME                                                                 VARCHAR2(100 CHAR)
AGE                                                                     NUMBER(38)

Describe command skips the invisible column. lets insert some values and check how the select statement displays the content.

SQL> insert into test_tbl values (‘David’, 33);
1 row created.

SQL> insert into test_tbl (name, age, emp_code) values (‘Sam’, 42, 2144);
1 row created.

Now SELECT data from the table.

SQL> column name format a15
SQL> select * from test_tbl;

NAME            AGE
————— ———-
David               33
Sam                 42

SQL Plus has an environment variable which will display invisible columns.

SQL> SET COLINVISIBLE ON

SQL> desc test_tbl;
Name                                                       Null? Type
—————————————– ——– —————————-
NAME                                                                 VARCHAR2(100 CHAR)
AGE                                                                     NUMBER(38)
EMP_CODE (INVISIBLE)                                  NUMBER(38)

SQL> select * from test_tbl;

NAME AGE
———————— ———-
David                             33
Sam                                42

SQL> select name, age, emp_code from test_tbl;

NAME                         AGE         EMP_CODE
———————— ———- ———-
David                            33
Sam                              42             2144

We can create index on the invisible column.

SQL> create index my_idx on test_tbl (emp_code);

Index created.

 

 

 

Advertisements