Virtual Columns In Oracle Database 11g

Virtual columns show to be standard board columns, but their values are consequently quite than being stored on disc. The syntax for important a effective column is listed below.

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

If the data type is omitted, it is strong minded based on the consequence of the appearance. The generate forever and virtual keywords are provided for simplicity single. The handwriting under create and populate an employees table with two levels of commission. It includes two virtual columns to exhibit the commission based salary. The first uses the majority condensed syntax while the next uses the most verbose form.

CREATE TABLE employees (

id          NUMBER,

first_name  VARCHAR2(10),

last_name   VARCHAR2(10),

salary      NUMBER(9,2),

comm1       NUMBER(3),

comm2       NUMBER(3),

salary1     AS (ROUND(salary*(1+comm1/100),2)),

salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,

CONSTRAINT employees_pk PRIMARY KEY (id)


INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)

VALUES (1, ‘JOHN’, ‘DOE’, 100, 5, 10);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)

VALUES (2, ‘JAYNE’, ‘DOE’, 200, 10, 20);


Querying the table shows the insert data plus the resultant commission based salaries.

SELECT * FROM employees;


———- ———- ———- ———- ———- ———- ———- ———-

1 JOHN       DOE               100          5         10        105        110

2 JAYNE      DOE               200         10         20        220        240

2 rows selected.


The appearance used to create the virtual column is planned in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views.

COLUMN data_default FORMAT A50

SELECT column_name, data_default

FROM   user_tab_columns

WHERE  table_name = ‘EMPLOYEES’;

COLUMN_NAME                    DATA_DEFAULT

—————————— ————————————————–







SALARY1                        ROUND(“SALARY”*(1+”COMM1″/100),2)

SALARY2                        ROUND(“SALARY”*(1+”COMM2″/100),2)

8 rows selected.


Virtual columns include:

[1] Indexes define against virtual column are equal to function based indexes.

[2] Virtual column can be referenced in the where clause of update and delete, but they can not be manipulate by DML.

[3] Tables contain effective columns can still be suitable for result caching.

[4] Functions in words must be deterministic at the instance of table formation, but can consequently be recompiled and complete non deterministic without invalidating the virtual column. In such cases the next steps must be taken after the function is recompiled:

[5] Constraint on the virtual column should be disabled and re enabled.

[6] Indexes on the virtual column must be rebuilt.

[7] Materialized views that access the virtual column must be fully refreshed.

[8] The result cache must be flushed if cached queries have accessed the virtual column.

[9]  Table statistics must be regathered.

[10] Virtual columns are not supported for index organized, external, object, cluster, or temporary tables.

[11] The look used in the virtual column description has the following limits:

[12] It cannot pass on to a new virtual column by name.

[13] It can only pass on to columns defined in the equivalent table.

[14] If it refers to a deterministic user defined function, it cannot be used as a partitioning key column.

[15] The output of the expression must be a scalar value. It cannot return an Oracle supplied data type, a user-defined type, or LOB or LONG RAW.

IcanXplore is from San Francisco bay area, in Silicon Valley, CA, USA, focused on providing Linux training around the world at own time, pace, 24/7. Only e-mail skill can renovate person into a successful Linux system administrator. Information technology professionals from Silicon Valley, CA, USA reveal effectual Linux training system to train person with basic knowledge of computer.

This entry was posted in Linux Training | Online Linux Training | Linux and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s