8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

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:

Here is an example of Virtual Column-Based Partitioning.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.