Invisible Columns
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 NUMBERPara 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) NUMBERMesmo 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 4Colocando 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 NOQuando 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 400000020Mesmo 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 97257Outro 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
);
2 3 4 5
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