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.
