Colunas Invisíveis. Novas características do Oracle Database 12c que você deveria saber para salvar tempo e melhorar o seu trabalho.

Por Alex Zaballa e Daniel Da Meda (OCM)
Postado em Junho 2014

No Oracle Database 12c, as colunas podem ser criadas como invisíveis diretamente na instrução CREATE TABLE ou posteriormente usando uma instrução ALTER TABLE. Por padrão, as colunas são sempre visíveis. Colunas invisíveis podem ser visíveis novamente usando a instrução ALTER TABLE.
Algumas operações que não veem colunas invisíveis:

  • SELECT * FROM em SQL
  • DESCRIBE no SQL*Plus
  • Atributo %ROWTYPE em declarações PL/SQL
  • DESCRIBES em Oracle Call Interface (OCI)

Do ponto de vista dos índices, as colunas invisíveis ainda podem ser indexadas e consideradas pelo otimizador.
O exemplo a seguir cria uma tabela com uma coluna invisível:

SQL> CREATE TABLE tabela_col_inv (
coluna1 NUMBER,    
coluna2 NUMBER,    
coluna3 NUMBER INVISIBLE,    
coluna4 NUMBER );

Table created. 

Colunas invisíveis por default, não aparecem no comando DESCRIBE:

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

O SQL* Plus pode mostrar opcionalmente colunas invisíveis, utilizando a opção SET COLINVISIBLE ON:

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

Mesmo que a coluna seja invisível, os valores desta coluna ainda podem ser exibidos e modificados:

SQL> INSERT INTO tabela_col_inv (coluna1,coluna2,coluna3,coluna4) VALUES (100,200,300,400);  
1 row created. 
SQL> SELECT  coluna1,coluna2,coluna3,coluna4 FROM tabela_col_inv;     

COLUNA1    COLUNA2    COLUNA3    COLUNA4 
---------- ---------- ---------- ----------        
100        200         300       400 

Inserindo os valores na tabela sem informar a lista de colunas:

SQL> INSERT INTO tabela_col_inv VALUES (101,102,103,104); 

INSERT INTO tabela_col_inv VALUES (101,102,103,104)             
* ERROR at line 1: 
ORA-00913: too many values 

Inserindo os valores na tabela sem informar a lista de colunas, mas agora somente com as colunas visíveis:

SQL> INSERT INTO tabela_col_inv VALUES (101,102,104); 
1 row created. 
SQL> SELECT * FROM tabela_col_inv;

COLUNA1    COLUNA2    COLUNA4 
---------- ---------- ----------        
100        200        400
101        102        104

Ao tornar a coluna visível, podemos verificar que ela aparece no final da tabela:

SQL> ALTER TABLE tabela_col_inv MODIFY coluna3 VISIBLE;  

Table altered. 
SQL> SELECT * FROM tabela_col_inv;

COLUNA1    COLUNA2    COLUNA4    COLUNA3 
---------- ---------- ---------- ----------        
100        200        400        300
101        102        104

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

É 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_COL_INV') 

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_col_inv 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_COL_INV'; 

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_COL_INV'
);
  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

SQL> ALTER TABLE tabela_col_inv MODIFY coluna4 VISIBLE;   
Table altered. 

SQL> ALTER TABLE tabela_col_inv MODIFY coluna3 INVISIBLE;  
Table altered.

SQL> desc tabela_col_inv  
Name                                    Null?    Type  
----------------------------------------- -------- ----------------------------
COLUNA1                                          NUMBER  
COLUNA2                                          NUMBER  
COLUNA4                                          NUMBER  
COLUNA3 (INVISIBLE)                              NUMBER

SQL> truncate table tabela_col_inv;  
Table truncated

SQL> INSERT INTO tabela_col_inv (coluna1,coluna2,coluna3,coluna4) VALUES (100,200,null,400);  
1 row created. 

SQL> SELECT  coluna1,coluna2,coluna3,coluna4 FROM tabela_col_inv;

COLUNA1    COLUNA2    COLUNA3    COLUNA4 
---------- ---------- ---------- ----------        
100        200                   400

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

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

FILE#      BLOCK# 
---------- ----------     
6          335
SQL> alter system dump datafile 6 block 335;

System altered.


data_block_dump,data header at 0x7f0b49520064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x7f0b49520064
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x16
fseo=0x1f79
avsp=0x1f63
tosp=0x1f63
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f89
0x14:pri[1]     offs=0x1f79
block_row_dump:
tab 0, row 0, @0x1f79
tl: 16 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [ 3]  c2 02 02
col  1: [ 3]  c2 02 03
col  2: *NULL*   <<<<<<< nossa coluna invisível
col  3: [ 3]  c2 02 05
end_of_block_dump
End dump data blocks tsn: 4 file#: 6 minblk 335 maxblk 335
SQL> select dump(coluna1,16) col1, dump(coluna2,16) col2, 
dump(coluna3,16) col3, dump(coluna4,16) col4 from tabela_col_inv;
COL1                 COL2                 COL3                 COL4 
-------------------- -------------------- -------------------- -------------------- 
Typ=2 Len=2: c2,2    Typ=2 Len=2: c2,3    NULL                 Typ=2 Len=2: c2,5 

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_col_inv2 (coluna1 NUMBER not null,   
coluna2 NUMBER INVISIBLE not null  );  

Table created. 

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

SQL>insert into tabela_col_inv2 values(1);

insert into tabela_col_inv2  values(1) 
* 
ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."TABELA_COL_INV2"."COLUNA2")  

Colunas virtuaise invisíveis também são permitidas no 12C:

SQL> create tabletabela_col_inv3 ( coluna1  number, coluna2 
INVISIBLE generated always as (coluna1+1) virtual); 

Table created. 

Particionamento em colunas invisíveis também são suportadas:

SQL> create table tabela_col_inv4 (coluna1  number, coluna2 
INVISIBLE generated always as (coluna1+1) virtual) 
partition by range(coluna2) (partition part1 values less than(100), 
partition part2 values less than(maxvalue) ); 

Table created. 



Alex Zaballa, formado em Análise de Sistemas, é especialista em Banco de Dados Oracle com sólidos conhecimentos em Servidores de Aplicação e Sistemas Operacionais; trabalha com Oracle há 14 anos, é ORACLE ACE, certificado OCM Database 11G e conta com mais de 100 outras certificações em produtos da Oracle. Desde 2007 é funcionário da empresa Júpiter em Angola, alocado em um projeto no Ministério das Finanças. Alex também é fundador do Grupo de Usuários Oracle de Angola (GUOA) e membro do time OraWorld.

Daniel Da Meda,  possui mais de 16 anos de experiência com tecnologias Oracle. Trabalhou como DBA senior para multiplas empresas na Europa onde morou por 7 anos. Dentre as empresas que atuou, destacam-se TimeWarner, Johnson&Johnson e British Film Institute. Dentre as certificações Oracle que possui, destaca-se o OCM 11g. Atualmente, Daniel esta trabalhando em Angola/África onde atua como DBA para o Ministério das Finanças de Angola. É especialista em alta disponibilidade, escalabilidade e Performance Tuning. Juntamente com o Alex Zaballa, fundou em 2013, o grupo de Usuários Oracle de Angola (GUOA)