Oracle11新特性——虚拟列

陈晓龙 · 发表于 2020-3-15 21:11:36

Oracle11g增加了表的虚拟列，这个列的数据并没有存储在数据文件中，而是Oracle通过列数据的生成放到了数据字典中。虚拟列的数值是通过真实列中的数据计算而来的。虚拟列的位置可以放在它参考的列的前面，也可以包括多个实际列的值，但是不能引用其他的虚拟列：

SQL> CREATE TABLE yu_test2 (c_vl_1 NUMBER, c_vl_2 AS (c_vl_1+1) );

Table created

SQL> CREATE TABLE yu_test2 (c_vl_1 NUMBER, c_vl_2 AS (c_vl_1+1), c_vl_3 AS (c_vl_2+1) );

CREATE TABLE yu_test2 (c_vl_1 NUMBER, c_vl_2 AS (c_vl_1+1), c_vl_3 AS (c_vl_2+1) )

ORA-54012: 在列表达式中引用了虚拟列

以上只是一个简单的虚拟列的例子，实际上虚拟列的完整写法应该包括列名、数据类型、GENERATED ALWAYS关键字、AS加列表达式和VIRTUAL关键字。其中GENERATED ALWAYS和VIRTUAL为可选关键字，主要用于描述虚拟列的特性，写与不写没有本质区别。而列的数据类型如果忽略，那么Oracle会根据AS后面的表达式最终结果的数据类型来确定虚拟列的数据类型：
SQL> CREATE TABLE yu_test1 ( v_cl_1 VARCHAR2(30),  v_cl_2 CHAR(50) GENERATED ALWAYS AS (LOWER(v_cl_1)) VIRTUAL );

Table created

SQL> desc yu_test1;
Name Type       Nullable Default       Comments
------ ------------ -------- --------------- --------
V_CL_1 VARCHAR2(30) Y
V_CL_2 CHAR(50)    Y       LOWER("V_CL_1")

虚拟列可以使用Oracle自带的函数或用户定义的函数，不过对于用户定义的函数要求必须声明函数的确定性（DETERMINISTIC），虚拟列必须是对实际列进行操作后的结果，不能使用没有实际列当做入参的函数，也就是说，虚拟列必须和表字段有关联：

SQL> CREATE OR REPLACE FUNCTION FUN_TEST1 RETURN NUMBER AS
  2  BEGIN
  3 RETURN 1;
  4  END;
  5  /
Function created

SQL> drop table yu_test1 purge;

Table dropped

SQL> CREATE TABLE yu_test2 (c_vl_1 NUMBER, c_vl_2 AS (FUN_TEST1) );

CREATE TABLE yu_test2 (c_vl_1 NUMBER, c_vl_2 AS (FUN_TEST1) )

ORA-54016: 指定了无效的列表达式

SQL> CREATE OR REPLACE FUNCTION FUN_TEST1 (c_in number) RETURN NUMBER AS
  2  BEGIN
  3 RETURN 1;
  4  END;
  5  /

Function created

SQL> CREATE TABLE yu_test2 (c_vl_1 NUMBER, c_vl_2 AS (FUN_TEST1(c_vl_1)) );

CREATE TABLE yu_test2 (c_vl_1 NUMBER, c_vl_2 AS (FUN_TEST1(c_vl_1)) )

ORA-30553: 函数不能确定

SQL>
SQL> CREATE OR REPLACE FUNCTION FUN_TEST1 (c_in number) RETURN NUMBER DETERMINISTIC AS
  2  BEGIN
  3 RETURN 1;
  4  END;
  5  /

Function created

SQL> CREATE TABLE yu_test2 (c_vl_1 NUMBER, c_vl_2 AS (FUN_TEST1(c_vl_1)) );

Table created

SQL>

DETERMINISTIC是必须的。
不过Oracle虽然在创建创建的时候会检查函数的确定性，在表建立之后，却可以将函数替换为非确定性函数：
SQL> insert into yu_test2 (c_vl_1) values(1);

1 row inserted

SQL> commit;

Commit complete

SQL> DROP FUNCTION FUN_TEST1;

Function dropped

SQL> select * from yu_test2;

select * from yu_test2

ORA-00904: "YUZH"."FUN_TEST1": 标识符无效

SQL> CREATE OR REPLACE FUNCTION FUN_TEST1 (c_in number) RETURN NUMBER AS
  2  BEGIN
  3 RETURN 2;
  4  END;
  5  /

Function created

SQL> select * from yu_test2;

C_VL_1    C_VL_2
---------- ----------
      1       2


建立了虚拟列可以有效的减少数据的存储，简化查询语句中对列进行的处理，而且还可以利用虚拟列进行分区。不过虚拟列还会带来其他问题。首先包含了虚拟列的表在INSERT INTO语句中不能省略COLUMN列表。由于虚拟列的值是由其他列的值计算得出的，且Oracle并不存储虚拟列的值，因此无论是INSERT还是UPDATE都不能对虚拟列进行修改：
SQL> insert into yu_test2 values(1);

insert into yu_test2 values(1)

ORA-00947: 没有足够的值

SQL> insert into yu_test2 values(1,1);

insert into yu_test2 values(1,1)

ORA-54013: 不允许对虚拟列执行 INSERT 操作

SQL> insert into yu_test2 (c_vl_1,c_vl_2) values(1,1);

insert into yu_test2 (c_vl_1,c_vl_2) values(1,1)

ORA-54013: 不允许对虚拟列执行 INSERT 操作

SQL> insert into yu_test2 (c_vl_1) values(1);

1 row inserted

SQL> commit;

Commit complete

SQL> update yu_test2 set c_vl_1=2;

2 rows updated

SQL> update yu_test2 set c_vl_2=2;

update yu_test2 set c_vl_2=2

ORA-54017: 不允许对虚拟列执行 UPDATE 操作

SQL> commit
2 ;

Commit complete

如果程序选择使用了一些工具来自动生成表的INSERT、UPDATE语句，那么遇到包含虚拟列的表就会报错。出于同样的原因，无法使用CREATE TABLE AS SELECT创建一个包含虚拟列的表。解决方法是CREATE TABLE AS SELECT结束后通过ALTER TABLE添加虚拟列。虚拟列还存在一个问题，当虚拟列的值一旦被实体化，那么虚拟列表达式发生变化会造成实体化结果与虚拟列不一致。简单的说就是虚拟列的结果是在查询的时候确定的，如果修改了虚拟列的表达式，下次执行查询时，虚拟列的值就会发生变化。但是一旦对虚拟列建立了索引，或者对包含虚拟列的表建立了物化视图，那么虚拟列的数值就被实际的存储下来，当虚拟列的表达式发生修改后，会导致索引或物化视图中已有的数据与目前虚拟列结果不一致。这个问题的解决方法只有删除索引并重建，或者将物化视图完全刷新。

SQL> create index YU_TEST2_IDX on YU_TEST2 (c_vl_2);

Index created

SQL> ALTER TABLE YU_TEST2 MODIFY c_vl_2 AS (UPPER(c_vl_1));

ALTER TABLE YU_TEST2 MODIFY c_vl_2 AS (UPPER(c_vl_1))

ORA-54022: 无法更改虚拟列表达式, 因为在列上定义了索引

虽然建立了索引后Oracle会禁止虚拟列发生修改，但是Oracle并不禁止虚拟列参考的函数的修改，修改方式见前面。