虚拟列（Virtual Column）——时间换空间

 

从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;

Name  Type         Nullable Default      Comments

----- ------------ -------- ------------ --------

TES   VARCHAR2(10) Y                             

U_TES VARCHAR2(10) Y        UPPER("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;

TES        U_TES

---------- ----------

kew        KEW

kEfsET3    KEFSET3

 

 

当设置上虚拟列之后，虚拟列的取值会按照自动设置的公式计算而成。首先，我们注意虚拟列的定义方式，格式为：

 

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;

Name   Type         Nullable Default       Comments

------ ------------ -------- ------------- --------

TES    VARCHAR2(10) Y                              

U_TES  VARCHAR2(10) Y        UPPER("TES")          

U_TES2 NUMBER       Y        LENGTH("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# NAME                                TYPE# DEFAULT$

---------- ---------- ---------------------

         1          1 TES                                     1

         2          0 U_TES                                   1 UPPER("TES")

         3          0 U_TES2                                  2 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;

Name   Type         Nullable Default       Comments

------ ------------ -------- ------------- --------

TES    VARCHAR2(10) Y                              

U_TES  VARCHAR2(10)          UPPER("TES")          

U_TES2 NUMBER                LENGTH("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

 

 

类型为基于函数的正常索引。

 

 

 

总结：

 

虚拟列是一种用时间换空间的技术，通过消耗计算时间来换回保存的空间。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-695171/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-695171/