11g Vitual Columns howto and performance

Let's do some basic testing on Virtual columns.


1 create table employees
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100),
6 loweremail as (lower(email)),
7 emp_full_name as (firstname || ' ' || lastname)
8* )
SQL> /

Table created.

Elapsed: 00:00:00.15

And try to insert some values....

insert into employees values (1,'ALLEN', 'BECK','AllenBeck@aol.com')
*
ERROR at line 1:
ORA-00947: not enough values


Elapsed: 00:00:00.00

Bamm...it failed. So it needs values for virtual columns too ??


insert into employees values (2,'Blah', 'Jlah', 'blah.jlah@msn.com', 'blah.jlah@msn.com','Blah Jlah')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns


Elapsed: 00:00:00.01

Not really. Then ?? Why did it fail ? Let's try doing something different...


SQL> insert into employees (empno,firstname, lastname, email) values (1,'ALLEN', 'BECK','AllenBeck@aol.com');

1 row created.

Elapsed: 00:00:00.00

So we know using virtual columns puts some limitations on how we do inserts.


Let's retrieve this data back..


1* select * from employees
SQL> /

EMPNO FIRSTNAME LASTNAME EMAIL LOWEREMAIL EMP_FULL_NAME
---------- ---------- ------------------------- -------------------- -------------------- ------------------------------
1 ALLEN BECK AllenBeck@aol.com allenbeck@aol.com ALLEN BECK

Elapsed: 00:00:00.00
SQL>

How to make sure the column is Virtual ?


1 select table_name, column_name, data_type, hidden_column
2 from dba_tab_cols
3 where table_name = 'EMPLOYEES'
4* and virtual_column = 'YES'
SQL> /

TABLE_NAME COLUMN_NAME DATA_TYPE HID
--------------- ------------------------------ --------------- ---
EMPLOYEES EMP_FULL_NAME VARCHAR2 NO
EMPLOYEES LOWEREMAIL VARCHAR2 NO

SQL>


Now, let's look at some performance metrics between this table and a table with no virtual columns.

SQL> create table employees_nonv
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100)
6 );

Table created.

SQL> create table employees_v
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100),
6 loweremail as (lower(email)),
7 emp_full_name as (firstname || ' ' || lastname)
8 );

Table created.

SQL>

Let's insert 100000 records in a table with no virtual columns.

set timing on

declare
i number:=0;
begin
for i in 1..100000
loop
insert into employees_nonv values (i,'Fredreck', 'Herbert','Fredreck.Herbert@myemailaddress.com');
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:29.74
SQL>


Now let's insert 100000 records in a table with virtual columns.

declare
i number:=0;
begin
for i in 1..100000
loop
insert into employees_v (empno, firstname, lastname, email) values (i,'Fredreck', 'Herbert','Fredreck.Herbert@myemailaddress.com');
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:29.79
SQL>

Pretty much the same results. Now, let's try to select from them.


Let's do a test by fetching a column from the table without virtual columns.

declare
i number:=0;
j number;
begin
for i in 1..100000
loop
select empno into j
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.

Let's do the test by fetching a single column from the table with virtual columns.

Elapsed: 00:00:22.26
SQL> SQL>

declare
i number:=0;
j number;
begin
for i in 1..100000
loop
select empno into j
from employees_v
where empno = i;
end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:22.47

Another one...

declare
i number:=0;
email varchar2(100);
begin
for i in 1..100000
loop
select email into email
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.

Elapsed: 00:00:24.06


declare
i number:=0;
loweremail varchar2(100);
begin
for i in 1..100000
loop
select loweremail into loweremail
from employees_v
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.

Elapsed: 00:00:24.36


Let's do another test by fetching multiple columns from the tables without virtual columns.

declare
i number:=0;
firstname varchar2(100);
lastname varchar2(100);
full_name varchar2(100);
email varchar2(100);
begin
for i in 1..100000
loop
select firstname, lastname, firstname || ' ' || lastname full_name, email into firstname, lastname, full_name, email
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.

Elapsed: 00:00:24.73

Now, let's try to select multiple columns from the tables with virtual columns.

declare
i number:=0;
firstname varchar2(100);
lastname varchar2(100);
full_name varchar2(100);
email varchar2(100);
begin
for i in 1..100000
loop
select firstname, lastname, full_name, loweremail into firstname, lastname, full_name, email
from employees_v
where empno = i;
end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:24.79

So, it is clear there is no apparent degradation for using Virtual columns. If there is an index created on a virtual which is a Function based index on the underlying column, may have some performance issues. That is true for any Function based index created. But generally if it's a pseudo column which is used by a PK or other indexed column, there shouldn't be any issues.