Oracle 11g - Virtual Table Columns - Coluna calculada em tempo de execução

Pessoal,

Bom dia!

Quero falar de um recurso fantástico que podemos utilizar no banco de dados Oracle 11g!

Coluna Virtual: Uma nova feature do Oracle 11g, que nos permite utilizar colunas em tabelas como expressões que são calculadas em tempo de execução na instrução select.

Syntax: column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

No exemplo abaixo criamos uma tabela que terá: uma coluna virtual: VALOR_TOTAL Ela será calculada em tempo de execução, multiplicando a quantidade x o preço unitário.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 

SQL> 
SQL> create table tb_vendas_virtual(id_produto number(5) primary key,
2                                 quantidade number(3),
3                                 unitario number(6,2),
4                                 valor_total number(10,2) generated always as ((quantidade * unitario)) virtual
5                                 );

Table created

SQL> select * from tb_vendas_virtual;

ID_PRODUTO QUANTIDADE UNITARIO VALOR_TOTAL
---------- ---------- -------- -----------

Em colunas virtuais não podemos inserir registros, pois e calculada em tempo de execução. Se tentar inserir algum valor na coluna virtual o Oracle te devolverá um erro:

SQL> insert into tb_vendas_virtual(id_produto, quantidade, unitario, valor_total) values (1, 2, 3.5, 7);

insert into tb_vendas_virtual(id_produto, quantidade, unitario, valor_total) values (1, 2, 3.5, 7)

ORA-54013: A operação INSERT não é permitida em colunas virtuais

Inserimos alguns registros desconsiderando a coluna virtual:

SQL> insert into tb_vendas_virtual(id_produto, quantidade, unitario) values (1, 2, 3.5);

1 row inserted

SQL> insert into tb_vendas_virtual(id_produto, quantidade, unitario) values (2, 50, 20);

1 row inserted

SQL> insert into tb_vendas_virtual(id_produto, quantidade, unitario) values (3, 10, 25);

1 row inserted

SQL> insert into tb_vendas_virtual(id_produto, quantidade, unitario) values (4, 3, 2.10);

1 row inserted

SQL> commit;

Commit complete

Quando selecionamos algum registro com a instrução SELECT, o Oracle executará em memória a expressão para a coluna virtual e retornará o valor para a coluna em questão:

SQL> select * from tb_vendas_virtual;

ID_PRODUTO QUANTIDADE UNITARIO VALOR_TOTAL
---------- ---------- -------- -----------
1          2     3,50           7
2         50    20,00        1000
3         10    25,00         250
4          3     2,10         6,3

SQL> select valor_total from tb_vendas_virtual;

VALOR_TOTAL
-----------
7
1000
250
6,3

Importante: De exemplo: Se para cada linha que trouxer no seu SELECT, existam 4 colunas virtuais, isso tudo deverá ser gerado em tempo de execução, em memória gerando processamento. Então, selecione colunas virtuais somente se precisar delas no select, evitando assim desperdício de processamento.

Algumas Limitações de Colunas Virtuais:

+ Colunas virtuais não podem ser criadas a partir de outras colunas virtuais.

+ Colunas virtuais não são suportadas em tabelas temporárias ou cluster.

+ Indexes nas colunas virtuais, são iguais a function-based indexes.

+ Colunas virtuais podem ser usadas na clausula WHERE em updates e deletes, selects, mais não podem ser manipuladas com DML.

Abraço,

Ronaldo