Colunas virtuais

Colunas virtuais

Coluna Virtual é uma nova feature do Oracle 11g que permite utilizar colunas puramente como expressões que são armazenadas no dicionário de dados. Desde a versão 8i temos ao nosso alcance a possibilidade de armazenar expressões utilizando function-based indexes.

Agora com a versão 11g temos a possibilidade de armazenar expressões diretamente em tabelas através das colunas virtuais.

Nesse artigo vou demonstrar usos básicos das colunas virtuais, algumas considerações sobre performance e suas limitações.

Como criar um coluna virtual?

SYNTAX

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

 SQL> create table triangulo
2  (
3     base number,
4     altura number,
5     area number generated always as ((base * altura) / 2) virtual
6  )
7  /

Table created.

A tabela criada, é responsável por armazenas bases e alturas de triângulos, onde a coluna area armazenará a expressão matemática ((base x altura) / 2).

Agora vamos inserir um triângulo com base 5 e altura 6. Observe que não inserimos nada na coluna area, ela será gerada automaticamente. O primeiro insert gerará um erro propositalmente, aonde estou querendo mostrar que não podemos adicionar colunas virtual na syntax insert (e update).

SQL> insert into triangulo (base, altura, area) values (5, 6, 15);
insert into triangulo (base, altura, area) values (5, 6, 15)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

Insert correto:
SQL> insert into triangulo (base, altura) values (5, 6);

 1 row created.

SQL> select * from triangulo;

BASE           ALTURA               AREA
---------- ---------- ----------
             5                 6               15

A expressão pode ser visualizada pela coluna DATA_DEFAULT na view [DBA|ALL|USER]_TAB_COLUMNS.

SQL> select column_name, data_default
2  from dba_tab_columns
3  where table_name = 'TRIANGULO';
COLUMN_NAME                       DATA_DEFAULT
-------------- -----------------------
BASE
ALTURA
AREA                                     "BASE"*"ALTURA"/2

SQL> select column_name, data_type
2  from dba_tab_cols
3  where table_name = 'TRIANGULO'
4  and virtual_column = 'YES';

COLUMN_NAME          DATA_TYPE
-------------------- --------------------------------
AREA                  NUMBER

Algumas considerações sobre performance

Embora muito já se falou sobre o famoso “SELECT * FROM”, com colunas virtuais irá punir ainda mais a performance para quem emprega essa prática. Imagine que para cada linha que trouxer do seu SELECT, existam 3 colunas virtuais, isso tudo deverá ser gerado em runtime, ou seja, somente no momento da execução do SELECT que os dados serão “criados”, implicações significativas de desempenho são possíveis nesses casos. É extremamente importante, qualquer aplicação que utilize das colunas virtuais que use explicitamente as colunas que deseja utilizar, evitando assim desperdicio de processamento.

Limitações

• Colunas virtuais não podem ser baseadas em outras colunas virtuais.
• Elas não suportadas em index-organized, external, object, tabelas temporarias ou cluster.
• Indexes nas colunas virtuais, são iguais a function-based indexes.
• Eles podem ser usadas na clausula WHERE em updates e deletes, mais não podem ser manipuladas como DML.