When queried, virtual columns look like normal table columns, but their values are derived rather than being stored on disc.
Realted articles.
The syntax for defining a virtual column is listed below.
column_name [datatype] [generated always] as (expression) [virtual]
If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS
and VIRTUAL
keywords are options, and provided for clarity only.
The script below creates and populates a table to hold products. It includes a virtual column to display the price with tax included. In this case we've increased the price value by 20%, and rounded the result to 2 decimal places. The expression can include functions, but it must be deterministic.
create table t1 ( id number, product varchar2(50), price number(10,2), price_with_tax number(10,2) generated always as (round(price*1.2,2)) virtual );
We describe the table, and the PRICE_WITH_TAX
column looks like a normal column.
SQL> desc t1; Name Null? Type -------------- ----- ------------ ID NUMBER PRODUCT VARCHAR2(50) PRICE NUMBER(10,2) PRICE_WITH_TAX NUMBER(10,2) SQL>
We insert data into the table. Notice we've not referenced the PRICE_WITH_TAX
column.
insert into t1 (id, product, price) values (1, 'computer', 1500); insert into t1 (id, product, price) values (2, 'bike', 1000); commit;
We query the table, and we can see the PRICE_WITH_TAX
column displays the correct value. The PRICE
column value plus 20%.
column product format a15 select * from t1; ID PRODUCT PRICE PRICE_WITH_TAX ---------- --------------- ---------- -------------- 1 computer 1500 1800 2 bike 1000 1200 SQL>
There is an example of adding a virtual column to an existing table below.
We can alter the virtual column expression using the ALTER TABLE MODIFY
command. In the following example we increase the tax to 30%. Notice we don't include any of the optional keywords. When we query the table, the PRICE_WITH_TAX
column reflects the change.
alter table t1 modify ( price_with_tax number(10,2) as (round(price*1.3,2)) ); select * from t1; ID PRODUCT PRICE PRICE_WITH_TAX ---------- --------------- ---------- -------------- 1 computer 1500 1950 2 bike 1000 1300 SQL>
We'll change the tax back to 20% using a different method. We drop the virtual column using the ALTER TABLE ... DROP COLUMN
command, then add a new virtual column to the existing table using the ALTER TABLE ... ADD
command. When we query the table, the PRICE_WITH_TAX
column reflects the change.
alter table t1 drop column price_with_tax; alter table t1 add ( price_with_tax number(10,2) as (round(price*1.2,2)) ); select * from t1; ID PRODUCT PRICE PRICE_WITH_TAX ---------- --------------- ---------- -------------- 1 computer 1500 1800 2 bike 1000 1200 SQL>
The [DBA|ALL|USER]_TAB_COLS
views includes columns called VIRTUAL_COLUMN
and DATA_DEFAULT
. We can see the PRICE_WITH_TAX
column is marked as a virtual column, and the DATA_DEFAULT
column displays the expression used to derive the value.
column column_name format a20 column virtual_column format a14 column data_default format a20 select column_id, column_name, virtual_column, data_default from user_tab_cols where table_name = 'T1' order by column_id; COLUMN_ID COLUMN_NAME VIRTUAL_COLUMN DATA_DEFAULT ---------- -------------------- -------------- -------------------- 1 ID NO 2 PRODUCT NO 3 PRICE NO 4 PRICE_WITH_TAX YES ROUND("PRICE"*1.2,2) SQL>
Notes and restrictions on virtual columns include:
WHERE
clause of updates and deletes, but they cannot be manipulated by DML.Here is an example of Virtual Column-Based Partitioning.
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/11g/virtual-columns-11gr1