A virtual column is essentially an expression that provides a value at query time (which thus differs from a default value which provides a value at insert time).
Because the value is computed, it does not take disk space.
In many respects, virtual columns can be treated like ordinary columns, notably, they
can be used for indexes (which is equivalent to a function based index) and
It is possible to use the keyword default where the value of a virtual column is specified. The following statement inserts a record into tq84_virt:
insert into tq84_virt values (4, default);
Updating a virtual value results in ORA-54017: UPDATE operation disallowed on virtual columns:
update tq84_virt
set
num = 5,
dbl = 10;
Unlike with insert statements, the default keyword does not solve the problem when used in the update's set clause, the following statement also throws an ORA-54017 error message.
update tq84_virt
set
num = 5,
dbl = default;
Cleaning up:
drop table tq84_virt;
Non deterministic functions/expressions are not allowed
create table tq84_virtual_col (
a number,
b number,
c as (a + b)
);
insert into tq84_virtual_col (a, b) values (1,1);
insert into tq84_virtual_col (a, b) values (4,3);
insert into tq84_virtual_col (a, b) values (2,7);
select * from tq84_virtual_col;
alter table tq84_virtual_col modify c as (a*b);
select * from tq84_virtual_col;
drop table tq84_virtual_col purge;
Virtual columns cannot reference other virtual columns
drop table tq84_virt_of_virt;
--
-- Virtual columns cannot be referenced in
-- other virtual columns:
--
create table tq84_virt_of_virt (
num number,
num_squared as (num * num),
-- num_cubed as (num * num_squared) -- ORA-54012: virtual column is referenced in a column expression
num_cubed as (num * num * num)
);
insert into tq84_virt_of_virt (num) values (1);
insert into tq84_virt_of_virt (num) values (2);
insert into tq84_virt_of_virt (num) values (3);
select * from tq84_virt_of_virt;
Create a table implementing the four basic arithmetic operations as virtual columns:
create table tq84_virtual_test (
c1 number,
c2 number,
------------
addition as (c1+c2),
substraction as (c1-c2),
multiplication as (c1*c2),
division as (c1/c2)
);
Fill a few values:
begin
insert into tq84_virtual_test values (18, 13, default, default, default, default);
insert into tq84_virtual_test (c1, c2) values ( 2, 42);
insert into tq84_virtual_test (c1, c2) values (13, null);
insert into tq84_virtual_test (c1, c2) values (14, 0);
commit;
end;
/
The following select statements run ok:
select c1, c2, addition, substraction, multiplication from tq84_virtual_test;
select c1, c2, addition, substraction, multiplication, division from tq84_virtual_test where c2 is null or c2 != 0;
select
c1, c2,
addition,
substraction,
multiplication,
case when c2 = 0 then null else division end division
from
tq84_virtual_test;
Cleaning up:
drop table tq84_virtual_test;
Deterministic tests
create or replace package tq84_virtual_test_pck as
function f_non_deterministic (c1_ in number) return number;
function f_deterministic (c1_ in number) return number deterministic;
end tq84_virtual_test_pck;
/
create or replace package body tq84_virtual_test_pck as
function f_non_deterministic (c1_ in number) return number as
begin
return dbms_random.value(0, c1_);
end f_non_deterministic;
function f_deterministic (c1_ in number) return number deterministic as
begin
return c1_ * 42;
end f_deterministic;
end tq84_virtual_test_pck;
/
-- TODO tq84_virtual_test_pck defined in test_01.sql
create table tq84_virtual_test_2 (
c1 number,
-- oh, oh, ORA-30553: The function is not deterministic:
-- f_ as (tq84_virtual_test_pck.f_non_deterministic(c1)),
f_ as (tq84_virtual_test_pck.f_deterministic (c1))
);
insert into tq84_virtual_test_2 (c1) values ( 1);
insert into tq84_virtual_test_2 (c1) values ( 10);
insert into tq84_virtual_test_2 (c1) values (100);
select * from tq84_virtual_test_2;
drop table tq84_virtual_test_2;
drop package tq84_virtual_test_pck;
create or replace package tq84_virtual_test_pck_3 as
function not_really_deterministic(c1_ in number) return date deterministic;
end tq84_virtual_test_pck_3;
/
create or replace package body tq84_virtual_test_pck_3 as
function not_really_deterministic(c1_ in number) return date deterministic is
begin
return sysdate; /* Now, that's sick! */
end not_really_deterministic;
end tq84_virtual_test_pck_3;
/
create table tq84_virtual_test_3 (
c1 number,
dt as (tq84_virtual_test_pck_3.not_really_deterministic(c1))
);
create index tq84_virtual_test_3_ix on tq84_virtual_test_3 (dt);
insert into tq84_virtual_test_3 (c1) values (1);
exec dbms_lock.sleep(10);
insert into tq84_virtual_test_3 (c1) values (2);
exec dbms_lock.sleep(10);
insert into tq84_virtual_test_3 (c1) values (3);
exec dbms_lock.sleep(10);
insert into tq84_virtual_test_3 (c1) values (4);
exec dbms_lock.sleep(10);
insert into tq84_virtual_test_3 (c1) values (5);
exec dbms_lock.sleep(10);
insert into tq84_virtual_test_3 (c1) values (6);
exec dbms_lock.sleep(10);
insert into tq84_virtual_test_3 (c1) values (7);
exec dbms_lock.sleep(10);
insert into tq84_virtual_test_3 (c1) values (8);
exec dbms_lock.sleep(10);
insert into tq84_virtual_test_3 (c1) values (9);
exec dbms_lock.sleep(10);
with m as (
-- Find max insert statement...
select /*+ index(t dt) */ max(dt) ax from tq84_virtual_test_3 t
)
select /*+ index(t dt) */ c1 from
tq84_virtual_test_3 t, m
where t.dt > m.ax - 45 / 24/60/60;
drop table tq84_virtual_test_3;
create table tq84_virtual_test_4 (
c1 number
);
insert into tq84_virtual_test_4 (c1) values (1);
insert into tq84_virtual_test_4 (c1) values (2);
insert into tq84_virtual_test_4 (c1) values (3);
insert into tq84_virtual_test_4 (c1) values (4);
commit;
create or replace package tq84_virtual_test_pck_4 as
function r(c1_ in number) return number deterministic;
end tq84_virtual_test_pck_4;
/
create or replace package body tq84_virtual_test_pck_4 as
function r(c1_ in number) return number deterministic is
v number;
begin
select sum(c1)
into v
from tq84_virtual_test_4
where c1 != c1_;
return v;
end r;
end tq84_virtual_test_pck_4;
/
alter table tq84_virtual_test_4 add (c2 as (tq84_virtual_test_pck_4.r(c1)));
select * from tq84_virtual_test_4;
drop table tq84_virtual_test_4;
drop package tq84_virtual_test_pck_4;