8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Virtual Columns in Oracle Database 11g Release 1
When queried, virtual columns look like normal table columns, but their values are derived rather than being stored on disc.
Realted articles.
Creating Virtual Columns
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.
Altering Virtual Columns
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>
Views
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>
Restrictions
Notes and restrictions on virtual columns include:
- Indexes defined against virtual columns are equivalent to function-based indexes.
- Virtual columns can be referenced in the
WHERE
clause of updates and deletes, but they cannot be manipulated by DML. - Tables containing virtual columns can still be eligible for result caching.
- Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled:
- Constraint on the virtual column must be disabled and re-enabled.
- Indexes on the virtual column must be rebuilt.
- Materialized views that access the virtual column must be fully refreshed.
- The result cache must be flushed if cached queries have accessed the virtual column.
- Table statistics must be regathered.
- Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
- The expression used in the virtual column definition has the following restrictions:
- It cannot refer to another virtual column by name.
- It can only refer to columns defined in the same table.
- If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
- The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.
Here is an example of Virtual Column-Based Partitioning.
For more information see:
Hope this helps. Regards Tim...