Oracle 11g 新特性---虚拟列

概要

Virtual Columns 是Oracle 11g引进的一种新功能
Virtual Columns 的定义与使用
Virtual Columns 对分区表的支持

概述与限制

Oracle 在11g数据库中新增了此功能；它通过使用表达式或函数来计算并定义在数据字典中,实际数据并不存储在数据文件中。可以在表创建和修改时定义它；同时你可以对虚拟列进行索引创建和收集统计数据等,具有其他普通数据列具有的功能。可以在Query,DML,DDL语句中使用它。
虚拟列的使用有以下一些限制:

1. 虚拟列不容许直接存储数据.
2. 虚拟列不支持Index-Organized, External, Object, Cluster, Temporary Tables.
3. 虚拟列不支持Oracle- supplied Datatypes,Uer-Defined Datatypes,LOBs and LONG RAWs.

Virtual Columns 定义使用

计算定义列

创建表时，通过对其他实际列进行计算得到虚拟列。虚拟列不可以参考其他虚拟列来定义,即不可以直接在计算表达式里引用其他虚拟列来定义虚拟列。
SQL> create table vcol_ts_tab(
2 empno integer,
3 ename varchar2(8),
4 hire_date date,
5 sal number(8,2),
6 comm as (sal*0.2+200),
7 deptno integer);

函数定义列

创建表时，通过使用Oracle 内置函数或自定义函数来定义虚拟列。
自定义函数须在虚拟列定义前声明.定义完虚拟列后，还可以修改自定义的函数定义。

先定义函数

SQL> create or replace function initcap_name_fun(ename varchar2)
2 return varchar2
3 as
4 begin
5 return initcap(ename);
6 end;
7

使用函数定义列

创建表时，使用自定义和内置函数来定义虚拟列。
SQL> create table vcol_fun_ts_tab(
2 empno integer,
3 name varchar2(8),
4 deptno integer,
5 initcap_name_u varchar2(8) as (initcap_name_fun(ename)) virtual,
6 initcap_name_o generated always as (initcap(ename))
7 );

修改表/重定义

修改表定义时，可以添加或重定义表列为虚拟列.对虚拟列列创建了索引或在物化视图引用了此列，不可以修改此虚拟列的定义。

修改虚拟列定义

SQL> alter table vcol_ts_tab modify comm as (sal*0.3+100);

添加虚拟列定义

SQL> alter table vcol_ts_tab add (curr_comm as (sal*0.2+300));

插入数据限制

表定义后，向表插入数据时，不能直接插入数据到虚拟列;可以使用default关键字进行操作。
SQL> insert into vcol_ts_tab values(1111,‘liming’,sysdate,default,10,default);

虚拟列索引

虚拟列上可以创建索引。创建索引后的虚拟列定义不可以修改，但可以修改虚拟列定义引用的函数的定义。
SQL> create index ix_vcol_fun_ts_tab_vname_u
2 on vcol_fun_ts_tab(initcap_name_u);

6. 检测虚拟列的定义
   SQL> select table_name, column_name, data_default
   2 from user_tab_columns
   3 where table_name=‘VCOL_TS_TAB’
   4 and column_name=‘COMM’;

TABLE_NAME COLUMN_NAME DATA_DEFAULT

---

VCOL_TS_TAB COMM “SAL”*0.3+100

Virtual Columns 对分区表的支持

虚拟列可以在分区表中充当分区入口关键字来使用,如下我们创建一个范围分区表。
SQL> create table emp_part_tab
2 (empno integer primary key,
3 ename varchar2(8),
4 hire_date date,
5 sal number(8),
6 comm as (sal*0.3+300)
7 partition by range (comm)
8 ( partition low_comm values less than (500) tablespace users,
9 partition med_comm values less than (1000) tablespace tools,
10 partition hig_comm values less than (maxvalue) tablespace users);