12c : Invisible Columns

In 12c, you can now create tables / views with invisible speakers, or change existing columns to be invisible.

CREATE TABLE Table_test
(
Column1 NUMBER,
Column2 NUMBER,
Column3 NUMBER INVISIBLE,
coluna4 NUMBER
);

SQL> desc Table_test
Name Null? Type
----------------------------------------- -------- ----------------------------
column1 NUMBER
Column2 NUMBER

COLUNA4 NUMBER                                         
                                           

To show the invisible column, use the SQL * PLUS SET ON COLINVISIBLE

SQL> SET COLINVISIBLE ON
SQL> desc Table_test
Name Null? Type
----------------------------------------- -------- --------------------------
column1 NUMBER
Column2 NUMBER

COLUNA4 NUMBER

column3 (INVISIBLE) NUMBER                                         
                                         
                                           

Even the column being invisible, you can enter and modify the values ​​of it, just specify the column:

SQL> INSERT INTO Table_test (Column1, Column2, Column3, coluna4) VALUES (1,2,3,4);

1 row created.

If not specified in the column list:

SQL> INSERT INTO Table_test VALUES (1,2,4);

1 row created.

SQL> select * from Table_test;

Column2 Column1 COLUNA4
---------- ---------- ----------
1           2     4
1           2     4

Placing the post as visible again in the table:

SQL> ALTER TABLE Table_test MODIFY Column3 VISIBLE;

Table altered.

You can check that it has been positioned at the end of the table:

SQL> select * from Table_test;

Column1 Column2 COLUNA4 Column3
---------- ---------- ---------- ----------
1           2     3     4
1           2     4

Checking the data dictionary:

SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column

from user_tab_cols

where table_name = ‘Table_test’;

Column_id SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME HID   VIR
---------- ----------------- ----------------- - --- --- --------------------
1           1                 1                 column1      NO    NO
2           2                 2                 Column2        ON    ON

3           3                 3                 column3        NO    NO

4           4                                   COLUNA4        YES   NO

" From an indexing perspective, columns can still be indexed by Considered and the cost based optimizer regardless of Whether the column is invisible or not.

So, do not be confused by an  invisible index with an index on an invisible column, they're two entirely different concepts. " By Richard Foote