12c - Invisible Columns Alex Zaballa - DBA Oracle - ex-Oracle ACE Director, OCM, OCA, OCP, OCE, OCS

No 12c, agora é possível criar tabelas/views com colunas invisíveis, ou alterar colunas existentes para serem invisíveis.

CREATE TABLE tabela_teste
(
coluna1 NUMBER,
coluna2 NUMBER,
coluna3 NUMBER INVISIBLE,
coluna4 NUMBER
);

SQL> desc tabela_teste
Name Null? Type
----------------------------------------- -------- ----------------------------
COLUNA1 NUMBER
COLUNA2 NUMBER
COLUNA4 NUMBER

Para mostrar a coluna invisível, utilizamos o comando do SQL*PLUS SET COLINVISIBLE ON

SQL> SET COLINVISIBLE ON
SQL> desc tabela_teste
Name Null? Type
----------------------------------------- -------- ----------------------------
COLUNA1 NUMBER
COLUNA2   NUMBER
COLUNA4   NUMBER
COLUNA3   (INVISIBLE) NUMBER

Mesmo a coluna estando invisível, é possível inserir e modificar os valores dela, basta especificar a coluna:

SQL> INSERT INTO tabela_teste (coluna1,coluna2,coluna3,coluna4) VALUES (1,2,3,4);

1 row created.

Caso não seja especificada na lista de colunas:

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

1 row created.

SQL> select * from tabela_teste;

COLUNA1 COLUNA2 COLUNA4
---------- ---------- ----------
1 2 4
1 2 4

Colocando a coluna como visível novamente na tabela:

SQL> ALTER TABLE tabela_teste MODIFY coluna3 VISIBLE;

Table altered.

É possível verificar que ela foi posicionada no final da tabela:

SQL> select * from tabela_teste;

COLUNA1 COLUNA2 COLUNA4 COLUNA3
---------- ---------- ---------- ----------
1 2 4 3
1 2 4

É possível verificar que a coluna COL# é modificada ao alterar a visibilidade da coluna:

SQL> SELECT name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') property
FROM sys.col$
WHERE obj# =
(
SELECT obj# FROM sys.obj$ WHERE name = 'TABELA_TESTE'
);
2 3 4 5 6

NAME COL# INTCOL# SEGCOL# PROPERTY
-------------------- ---------- ---------- ---------- -------------
COLUNA1 1 1 1   0
COLUNA2   2      2   2      0
COLUNA3      4      3      3      0
COLUNA4      3   4      4      0

SQL> ALTER TABLE tabela_teste MODIFY coluna4 INVISIBLE;

Table altered.

Verificando o dicionário de dados:

SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column from user_tab_cols where table_name = 'TABELA_TESTE';

COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME HID VIR
---------- ----------------- ------------------ -------------------- --- ---
1 1 1 COLUNA1 NO NO
2   2      2 COLUNA2   NO NO
3   3      3   COLUNA3   NO NO
4   4         COLUNA4   YES NO

Quando a coluna da tabela está invisível, a coluna property do dicionário é modificada para o valor abaixo:

SQL> SELECT name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') property
FROM sys.col$
WHERE obj# =
(
SELECT obj# FROM sys.obj$ WHERE name = 'TABELA_TESTE'
);
2 3 4 5 6
NAME COL# INTCOL# SEGCOL# PROPERTY
-------------------- ---------- ---------- ---------- -------------
COLUNA1 1 1 1 0
COLUNA2   2   2   2   0
COLUNA3   3   3   3   0
COLUNA4   0   4   4   400000020

Mesmo invisível, especificando a coluna no SELECT, ela é mostrada:

SQL> select coluna1,coluna2,coluna3,coluna4 from tabela_teste;

COLUNA1 COLUNA2 COLUNA3 COLUNA4
---------- ---------- ---------- ----------
1 2 3 4
1 2 4

SQL> ALTER TABLE tabela_teste MODIFY coluna4 VISIBLE;

Table altered.

SQL> desc TABELA_TESTE

Name Null? Type
----------------------------------------- -------- ----------------------------
COLUNA1               NUMBER
COLUNA2               NUMBER
COLUNA3               NUMBER
COLUNA4               NUMBER

SQL> SELECT name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') property
FROM sys.col$
WHERE obj# =
(
SELECT obj# FROM sys.obj$ WHERE name = 'TABELA_TESTE'
);
2 3 4 5 6
NAME COL# INTCOL# SEGCOL# PROPERTY
-------------------- ---------- ---------- ---------- -------------
COLUNA1 1   1   1   0
COLUNA2   2   2   2   0
COLUNA3   3   3   3   0
COLUNA4   4   4   4   0

É possível criar uma view com a coluna invisível:

SQL> ALTER TABLE tabela_teste MODIFY coluna4 INVISIBLE;

Table altered.

SQL> create or replace view VIEW_TESTE (COLUNA1, COLUNA2, COLUNA3, COLUNA4 INVISIBLE) as select COLUNA1, COLUNA2, COLUNA3, COLUNA4 from TABELA_TESTE;

View created.

SQL> DESC VIEW_TESTE

Name Null? Type
----------------------------------------- -------- ----------------------------
COLUNA1   NUMBER
COLUNA2   NUMBER
COLUNA3   NUMBER

SQL> SET COLINVISIBLE ON

SQL> DESC VIEW_TESTE

Name Null? Type
----------------------------------------- -------- ----------------------------
COLUNA1 NUMBER
COLUNA2   NUMBER
COLUNA3   NUMBER
COLUNA4   (INVISIBLE) NUMBER

SQL> ALTER TABLE tabela_teste MODIFY coluna4 VISIBLE;

Table altered.

Fazendo um dump do bloco para confirmar que a ordem das colunas é definida a nível de dicionário:

SQL> ALTER TABLE tabela_teste MODIFY coluna3 INVISIBLE;

Table altered.

SQL> select dbms_rowid.rowid_relative_fno(rowid) File#, dbms_rowid.rowid_block_number(rowid) Block# from tabela_teste;

FILE# BLOCK#
---------- ----------
1 97257
1 97257

SQL> alter system dump datafile 1 block 97257;

System altered.

*** 2013-12-11 14:47:17.860

data_block_dump,data header at 0x7f81a0c82a5c

===============

tsiz: 0x1fa0

hsiz: 0x16

pbl: 0x7f81a0c82a5c

76543210

flag=--------

ntab=1

nrow=2

frre=-1

fsbo=0x16

fseo=0x1f84

avsp=0x1f6e

tosp=0x1f6e

0xe:pti[0]      nrow=2  offs=0

0x12:pri[0]     offs=0x1f91

0x14:pri[1]     offs=0x1f84

block_row_dump:

tab 0, row 0, @0x1f91

tl: 15 fb: --H-FL-- lb: 0x1  cc: 4

col  0: [ 2]  c1 02

col  1: [ 2]  c1 03

col  2: [ 2]  c1 04      <<<<<<< nossa coluna invisível

col  3: [ 2]  c1 05

tab 0, row 1, @0x1f84

tl: 13 fb: --H-FL-- lb: 0x1  cc: 4

col  0: [ 2]  c1 02

col  1: [ 2]  c1 03

col  2: *NULL*           <<<<<<< nossa coluna invisível

col  3: [ 2]  c1 05

end_of_block_dump

End dump data blocks tsn: 0 file#: 1 minblk 97257 maxblk 97257

Outro ponto observado, é que mesmo a coluna sendo invisível, o Oracle vai fazer a validação das constraints de check:

SQL> CREATE TABLE tabela_teste2
(
coluna1 NUMBER not null,
coluna2 NUMBER not null
);

Table created.

SQL> ALTER TABLE tabela_teste2 MODIFY coluna2 INVISIBLE;

Table altered.

SQL> desc tabela_teste2
Name Null? Type
----------------------------------------- -------- ----------------------------
COLUNA1 NOT NULL NUMBER

SQL> insert into tabela_teste2 values(1);
insert into tabela_teste2 values(1)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."TABELA_TESTE2"."COLUNA2")

“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.” By Richard Foote

Em um próximo artigo irei abordar a possibilidade de utilização Virtual INVISIBLE columns e Partitioning on INVISIBLE columns.

Abraço

Alex Zaballa