Author: R. Singh

Page: 1 2 3

Consider a scenario where you have a sales table with Gross_Amount and Discount_Amount fields. Now Net_Amount = Gross_Amount - Discount Amount; This formula can be used everywhere. However if due to some reason you need to change the formula then you will need to change the formula everywhere.

Separate Views:

One option to handle this situation is to create the separate view. The formula will be embedded in the view and everybody can use that view everywhere. So if formula changes then we just need to alter the view .

Trigger Level calculation:

Another option is that you add a new column called Gross_Amount and calculate it at trigger level . However this approach can result in wastage of hard disk space .

Oracle Virtual Column:

Oracle 11g has introduced a new feature called Virtual Column to handle such situations. With help of Virtual Column feature we can just add one virtual column based on the formula in the existing table and if formula changes then we just need to alter this column expression or formula.

• In Oracle 11g, a table can contain a virtual column which derives the values by evaluating expressions specified by user. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions.



• A virtual column does not consume any space on disk as its value is calculated only when it is queried based on its column expression.



• A virtual column can be defined at the time of table creation or modification.



• A virtual column can be treated much as normal columns. You can index virtual columns. You can collect statistics on virtual columns and can create integrity constraints.



• One cannot explicitly write to a virtual column.

Oracle Virtual Column for performance improvement:

Using Oracle Virtual Column you can make application development easier and less error-prone. It enhances query optimization by providing additional statistics to the optimizer for these virtual columns.

Page: 1 2 3

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   DBA Tips: Switching Oracle Users and Back!!
   DBA Tips for Oracle 10g Lost SYSOPER Password!!
   Enhance performance of Oracle Data Load
   Oracle Replication, Some Concerns