Pular para o conteúdo

Descubra como criar colunas invisíveis no Oracle

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

 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

Quão útil foi este post ?

Clique em uma estrela para classificar o post

nota média / 5. Contagem de votos:

Sem votos ! Seja o primeiro a classificar !

Marcações:

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

plugins premium WordPress