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)