Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle 11g function-based virtual columns


Oracle11g Tips by Burleson Consulting

Oracle 11g has introduced a new feature that allows you to create a "virtual column", an empty column that contains a function upon other table columns (the function itself is stored in the data dictionary).

For example, assume that we have a table column named gross_pay which is defined as a function of the (hours_worked * hourly_pay_rate) columns. In this example, you cannot attempt to insert anything into the virtual gross_pay column or you will get the new error:

ORA-54013: INSERT operation disallowed on virtual columns

Traditionally this sort of "process logic" would be stored inside the application code and computed on an as-needed basis.  However, there are some benefits on placing data transformation rules inside the database itself, where it can be managed and controlled without touching the application code.  However, past attempts by Oracle to place process logic inside the database have not been met with widespread acceptance (see the Oracle8 member methods).  Possible benefits of virtual columns include:

  • Automatic re-computation of derived columns for ad-hoc query tools
     

  • Reduction in redundant disk space for columns that must be derived from other columns (e.g. a MONTH column that is derived from another DATE column).
     

  • Easier interval partitioning

 

Features and limitations of 11g virtual columns

Virtual computations based on other virtual columns - Laurent Schneider also notes a limitation whereby virtual columns may not reference other virtual column values:

create table
   t
(
   x number,
   x1 as (x+1),
   x2 as (x1+1)
);

create table t(x number, x1 as (x+1), x2 as (x1+1))
*
ERROR at line 1:
ORA-54012: virtual column is referenced in a column expression

Also, it's important to note that 11g virtual columns only work within the specified table, and you cannot reference columns within other tables.

Virtual columns

Virtual columns are expressions that are based on one or more existing columns in the table. When using Virtual Column-Based Partitioning, a virtual column in a table provides advantages in disk space utilization. A virtual column is only stored as metadata. It does not consume physical space, but it can be indexed. The virtual column also contains optimizer statistics and histograms.

Using a virtual column also simplifies the use of derived columns. Transparently derived values do not require the application to calculate and insert an additional value. This also prevents the need to use a trigger on the table to provide an alternate implementation of this functionality. Using virtual columns in tables also eliminates the need to use views to display derived column values.

A new table with a virtual column can be derived using the following syntax:

create table <table_name>(
<column_name> <data_type>,
?
<column_name> [<data_type>] [generated always] as (<column_expression>) [virtual]
);

Adding a virtual column to an existing table is accomplished with the following alter table syntax:

alter table <table_name>

add (<column_name> [<data_type>] [generated always] as (<column_expression>) [virtual]);

When defining a virtual column in a table, it is possible to either include the datatype or let the database determine the datatype based on the expression.

Optionally, the phrases ?generated always? and ?virtual? can be used to help clarify the syntax. The column expression must reference columns defined on the same table; however, the column expression can refer to a PL/SQL function if the function is designated DETERMINISTIC during its creation.

For example, a virtual column might be helpful in a table that stores employee information:

SQL> create table employees(
2 employee_name varchar2(30),
3 start_date date,
4 end_date date,
5 hourly_rate generated always as (annual_salary/2080),
6 annual_salary number,
7 active as (case when end_date is null then 'Y' else 'N' end));

Table created.

SQL> insert into employees
2 (employee_name,
3 start_date,
4 end_date,
5 annual_salary)
6 values
7 ('C. TESTER', '01-JAN-2011', NULL, 100000);

1 row created.

SQL> select * from employees
2 /

EMPLOYEE_NAME START_DATE END_DATE HOURLY_RATE ANNUAL_SALARY ACTIVE
--------------- ---------- --------- ----------- ------------- ----------
C. TESTER 01-JAN-11 48.0769231 100000 Y

The INSERT statement required to create a new record in this table only requires four values, but the query of this table displays all six values. This includes the two virtual columns derived from other columns. The hourly rate virtual column is an expression of the annual salary divided by 2080 working hours per year. The second virtual column displays if the employee is active by examining the row?s end date.

Virtual columns can be used for partitioning, indexing, constraints and foreign keys. However, virtual columns cannot be used for index-organized, external, object, cluster or temporary tables.

There are several error related to virtual columns, including the ORA-12996 error.

The oerr utility shows this for the ORA-12996 error:

ORA-12996: cannot drop system-generated virtual column

Cause: An attempt was made to drop a virtual column generated by the system.

Action: None

 

Virtual columns and 11g partitioning

However, the concept of virtual columns has the nice side effect of assisting in streamlining partitioning.  For example, assume that we have a table that is partitioned by year-month (i.e. 2007-07).  With 11g virtual columns, we can simply compute the partition key virtually, using a DATE column.  

 

 

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

  

 
  
 
 
 

 
 
 

Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.