Oracle 11g New Feature: Virtual Column
As defined by Oracle, Tables can now include "Virtual Columns". The value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. In some cases, a virtual column eliminates the need to create a separate view. You can create an index on a virtual column, and you can use a virtual column as a partition or sub-partition key.
Virtual columns enable application developers to define computations and transformations as the column (metadata) definition of tables without space consumption. This makes application development easier and less error-prone, as well as enhances query optimization by providing additional statistics to the optimizer for these virtual columns.
Prior to 11g, database modelers and developers usually use view or physical table to perform the business requirement. Therefore, this dilemma is eager to be solved when we consider query optimization, data exposure, unnecessary disk space consumption. The key points are:
- physical table exposes data in viewable way
- physical table requires unnecessary disk space
- View eliminates above two disadvantage, but it can’t be conducted with optimizer statistics (may opt for Function Based Index in solving this with additional cost of adding database object)
“The introduction of virtual columns in Oracle 11g has provided data modelers with an optimal design for logical attributes. Exposed as table columns, virtual column values are not actually stored on disk but are computed on the fly during SQL execution. They actually share much of the same code path as FBIs but offer visibility. In addition to not being physically stored, virtual columns have other significant advantages over views. Optimizer statistics can be collected on virtual columns and these columns can be indexed, constrained and even used to partition tables. This enables the optimizer to make fact-based intelligent decisions around query access. Virtual columns achieve the design goal of exposing logical attributes without additional storage or object costs.” –- cited from below article.
Please refer to the article “Exposing logical attributes with virtual columns” for more information.
Popularity: 10% [?]
Tags:11g
