从Oracle11g开始,提供了虚拟列(Virtual Column)功能。和传统的数据列差异在于,虚拟列在数据库中并不存在实际保存的数值,而是通过计算公式,进行计算获取列值。
我们从Oracle官方文档中,找到下面对于虚拟列技术的描述。
“Tables can also include virtual columns. A virtual column is like any other table column, except that its value is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. You cannot explicitly write to a virtual column.”
从上面的内容中,我们可以打出关于虚拟列的下面即使要点:
ü对数据表,我们是可以添加虚拟列的;
ü虚拟列的使用和一般列在使用上没有过多的区别,只是通过表达式计算出的值;
ü在虚拟列的表达式中,可以包括同表的其他列、常量、SQL函数,甚至可以包括一些用户自定义的PL/SQL函数;
ü同一般列的区别,在进行insert操作的时候,我们不能直接进行该列的赋值操作;
下面,我们通过一系列的实验来验证虚拟列特性。
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
SQL> create table t (tes varchar2(10));
Table created
//定义添加一个虚拟列;
SQL> alter table t add (u_tes varchar2(10) as (upper(tes)));
Table altered
SQL> desc t;
NameTypeNullable DefaultComments
----- ------------ -------- ------------ --------
TESVARCHAR2(10) Y
U_TES VARCHAR2(10) YUPPER("TES")
虚拟列效果
我们通过添加数据,来探究虚拟列的使用。
SQL> insert into t (tes) values ('kew');
1 row inserted
SQL> insert into t (tes) values ('kEfsET3');
1 row inserted
SQL> commit;
Commit complete
//检索全部行
SQL> select * from t;
TESU_TES
---------- ----------
kewKEW
kEfsET3KEFSET3
当设置上虚拟列之后,虚拟列的取值会按照自动设置的公式计算而成。首先,我们注意虚拟列的定义方式,格式为:
SQL> alter table t add (u_tes varchar2(10) as (upper(tes)));
Table altered
此外,对虚拟列的类型,也可以不进行显示赋值。
//可以不指定类型信息
SQL> alter table t add (u_tes2 as (length(tes)));
Table altered
SQL> desc t;
NameTypeNullable DefaultComments
------ ------------ -------- ------------- --------
TESVARCHAR2(10) Y
U_TESVARCHAR2(10) YUPPER("TES")
U_TES2 NUMBERYLENGTH("TES")
Oracle会根据生成列值的表达式进行计算,同时估算出可能的类型。
显示赋值不允许
虚拟列的赋值是Oracle自动依据表达式进行的。如果我们强制赋值,会提示错误信息。
SQL> update t set u_tes='k' where u_tes2=3;
update t set u_tes='k' where u_tes2=3
ORA-54017:不允许对虚拟列执行UPDATE操作
SQL> insert into t (u_tes) values ('LI');
insert into t (u_tes) values ('LI')
ORA-54013:不允许对虚拟列执行INSERT操作
虚拟列本质分析
我们检查数据字典的相关信息,分析列情况。
SQL> select object_id from dba_objects where wner='SYS' and object_name='T';
OBJECT_ID
----------
74889
SQL> select col#, segcol#,name, type#, default$ from col$ where obj#=74889;
COL#SEGCOL# NAMETYPE# DEFAULT$
---------- ---------- ---------------------
11 TES1
20 U_TES1 UPPER("TES")
30 U_TES22 LENGTH("TES")
说明两个列在数据字典底层存在。
索引特性
对虚拟列我们可以加入索引和约束。
//加入控制约束
SQL> alter table T modify U_TES not null;
Table altered
SQL> alter table T modify U_TES2 not null;
Table altered
SQL> desc t;
NameTypeNullable DefaultComments
------ ------------ -------- ------------- --------
TESVARCHAR2(10) Y
U_TESVARCHAR2(10)UPPER("TES")
U_TES2 NUMBERLENGTH("TES")
SQL> insert into t (tes) values (null);
insert into t (tes) values (null)
ORA-01400:无法将NULL插入("SYS"."T"."U_TES2")
索引情况呢?
//重新构建实验环境
SQL> create table t as select object_id, owner, object_name from dba_objects;
Table created
SQL> alter table t add (name_length as (length(object_name)));
Table altered
SQL> create index idx_t_leng on t(name_length);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
检查索引本质内容:
SQL> select index_type from dba_indexes where index_name='IDX_T_LENG';
INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL
类型为基于函数的正常索引。
总结:
虚拟列是一种用时间换空间的技术,通过消耗计算时间来换回保存的空间。