Difference between revisions of "Virtual Column"
Jump to navigation
Jump to search
Line 1: | Line 1: | ||
+ | == What is Virtual Column? == | ||
+ | * Virtual Column values are derived by the evaluation of: | ||
+ | ** Columns from the same table | ||
+ | ** Constants | ||
+ | ** Function calls (user-defined or SQL functions) | ||
+ | * Virtual Columns are not physically stored but are evaluated on demand. | ||
+ | * Virtual Columns can be indexed (== function-based), enforced using constraints and used in queries, DML, and DDL statements like other table column types | ||
+ | * Table can be partitioned on a virtual column and even statistics can be gathered upon them. | ||
+ | |||
+ | == Demo == | ||
+ | |||
* Create a sample table with a VIRTUAL column. | * Create a sample table with a VIRTUAL column. | ||
<pre> | <pre> |
Latest revision as of 19:11, 14 March 2011
What is Virtual Column?
- Virtual Column values are derived by the evaluation of:
- Columns from the same table
- Constants
- Function calls (user-defined or SQL functions)
- Virtual Columns are not physically stored but are evaluated on demand.
- Virtual Columns can be indexed (== function-based), enforced using constraints and used in queries, DML, and DDL statements like other table column types
- Table can be partitioned on a virtual column and even statistics can be gathered upon them.
Demo
- Create a sample table with a VIRTUAL column.
create table EMP_COMP ( empno NUMBER(4) not null, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, salary NUMBER(7,2), comm_pct NUMBER(3,2), deptno NUMBER(2), total_comp as (salary * (1 + comm_pct)) ); Table created
- Virtual Column can be indexed
SQL> create index EMP_TCOMP_IDX on EMP_COMP(total_comp); Index created
- Virtual Column can be used as partition key.
create table EMP_COMP ( empno NUMBER(4) not null, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, salary NUMBER(7,2), comm_pct NUMBER(3,2), deptno NUMBER(2), total_comp as (salary * (1 + comm_pct)) ) partition by range (total_comp) ( partition p1 values less than (50000), partition p2 values less than (100000) : : );
See Also
- http://jonathanlewis.wordpress.com/2009/04/16/virtual-columns/
- 11g Virtual Columns and Fast Refreshable Materialized Views (What In The World) http://richardfoote.wordpress.com/2010/11/24/11g-virtual-columns-and-fast-refreshable-materialized-views-what-in-the-world/