Oracle11新特性——虚拟列

原文出自：http://yangtingkun.itpub.net/post/468/409211

 

Oracle11g增加了表的虚拟列，这个列的数据并没有存储在数据文件中，而是Oracle通过列数据的生成放到了数据字典中。


看一个简单的虚拟列的例子：

PHP code:



SQL> CREATE OR REPLACE FUNCTION F_GETTYPE(P_TYPE IN VARCHAR2) RETURN NUMBER


  2   DETERMINISTIC AS


  3  BEGIN


  4   IF P_TYPE IN ('TABLE', 'INDEX', 'LOB', 'TABLE PARTITION', 'INDEX PARTITION', 'LOB PARTITION',


  5    'TABLE SUBPARTITON', 'INDEX SUBPARTITION', 'LOB SUBPARTITION', 'CLUSTER') THEN


  6    RETURN 1;


  7   ELSE


  8    RETURN 0;


  9   END IF;


 10  END;


 11  /


函数已创建。


SQL> CREATE TABLE T_VIRTUAL_COLUMN 


  2  (


  3   ID NUMBER PRIMARY KEY, 


  4   V_LENGTH AS (CEIL(LENGTH(TO_CHAR(ID)) / 2) + 1 + LENGTH(NAME) + LENGTH(TYPE)), 


  5   NAME VARCHAR2(30), 


  6   V_NAME CHAR(50) GENERATED ALWAYS AS (LOWER(NAME)) VIRTUAL, 


  7   TYPE VARCHAR2(30), 


  8   V_TYPE AS (F_GETTYPE(TYPE))


  9  );


表已创建。





.

上面例子中，V_LENGTH、V_NAME和V_TYPE都是虚拟列，虚拟列的数值是通过真实列中的数据计算而来的。
虚拟列的位置可以放在它参考的列的前面，也可以包括多个实际列的值，但是不能引用其他的虚拟列：

PHP code:



SQL> CREATE TABLE T_VIRTUAL_COLUMN_ERR 


  2  (ID NUMBER, 


  3  V_ID1 AS (ID * 5), 


  4  V_ID2 AS (V_ID1 + 45)


  5  );


V_ID1 AS (ID * 5),


*


第 3 行出现错误:


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





.

虚拟列的完整写法如上面例子中V_NAME列，包括列名、数据类型、GENERATED ALWAYS关键字、AS加列表达式和VIRTUAL关键字。其中GENERATED ALWAYS和VIRTUAL为可选关键字，主要用于描述虚拟列的特性，写与不写没有本质区别。而列的数据类型如果忽略，那么Oracle会根据AS后面的表达式最终结果的数据类型来确定虚拟列的数据类型。
虚拟列可以使用Oracle自带的函数，也可以使用用户定义的函数，不过对于用户定义的函数要求必须声明函数的确定性：

PHP code:



SQL> CREATE OR REPLACE FUNCTION F_TEST RETURN NUMBER AS


  2  BEGIN


  3   RETURN 1;


  4  END;


  5  /


函数已创建。


SQL> CREATE TABLE T_VIRTUAL_COLUMN_DETER 


  2  (ID NUMBER, VID AS (F_TEST));


(ID NUMBER, VID AS (F_TEST))


                    *


第 2 行出现错误:


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





.

虚拟列必须是对实际列进行操作后的结果，不能像上面这样直接写一个返回常数的函数，换句话说，表不能只包括虚拟列：

PHP code:



SQL> CREATE OR REPLACE FUNCTION F_TEST(P_IN IN NUMBER) RETURN NUMBER AS


  2  BEGIN


  3   RETURN 1;


  4  END;


  5  /


函数已创建。


SQL> CREATE TABLE T_VIRTUAL_COLUMN_DETER


  2  (


  3   ID NUMBER, 


  4   V_ID AS (F_TEST(ID))


  5  );


 V_ID AS (F_TEST(ID))


          *


第 4 行出现错误:


ORA-30553: 函数不能确定





.

现在错误信息显示，函数没有声明确定性：

PHP code:



SQL> CREATE OR REPLACE FUNCTION F_TEST(P_IN IN NUMBER) RETURN NUMBER DETERMINISTIC AS


  2  BEGIN


  3   RETURN 1;


  4  END;


  5  /


函数已创建。


SQL> CREATE TABLE T_VIRTUAL_COLUMN_DETER


  2  (


  3   ID NUMBER, 


  4   V_ID AS (F_TEST(ID))


  5  );


表已创建。





.

Oracle虽然在创建创建的时候会检查函数的确定性，在表建立之后，却可以将函数替换为非确定性函数：

PHP code:



SQL> INSERT INTO T_VIRTUAL_COLUMN_DETER (ID) VALUES (1);


已创建 1 行。


SQL> SELECT * FROM T_VIRTUAL_COLUMN_DETER;


        ID       V_ID


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


         1          1


SQL> DROP FUNCTION F_TEST;


函数已删除。


SQL> SELECT * FROM T_VIRTUAL_COLUMN_DETER;


SELECT * FROM T_VIRTUAL_COLUMN_DETER


*


第 1 行出现错误:


ORA-00904: "YANGTK"."F_TEST": 标识符无效





SQL> CREATE OR REPLACE FUNCTION F_TEST(P_IN IN NUMBER) RETURN NUMBER AS


  2  BEGIN


  3   RETURN 2;


  4  END;


  5  /


函数已创建。


SQL> SELECT * FROM T_VIRTUAL_COLUMN_DETER;


        ID       V_ID


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


         1          2





.

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

PHP code:



SQL> INSERT INTO T_VIRTUAL_COLUMN 


  2  SELECT ROWNUM ID, NULL, OWNER, OBJECT_NAME, NULL, OBJECT_TYPE, NULL 


  3  FROM DBA_OBJECTS;


INSERT INTO T_VIRTUAL_COLUMN


            *


第 1 行出现错误:


ORA-00913: 值过多





SQL> INSERT INTO T_VIRTUAL_COLUMN 


  2  SELECT ROWNUM ID, NULL, OBJECT_NAME, NULL, OBJECT_TYPE, NULL 


  3  FROM DBA_OBJECTS;


INSERT INTO T_VIRTUAL_COLUMN


            *


第 1 行出现错误:


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





SQL> INSERT INTO T_VIRTUAL_COLUMN


  2  SELECT ROWNUM ID, OBJECT_NAME, OBJECT_TYPE 


  3  FROM DBA_OBJECTS;


INSERT INTO T_VIRTUAL_COLUMN


            *


第 1 行出现错误:


ORA-00947: 没有足够的值





SQL> INSERT INTO T_VIRTUAL_COLUMN (ID, NAME, TYPE)


  2  SELECT ROWNUM ID, OBJECT_NAME, OBJECT_TYPE 


  3  FROM DBA_OBJECTS;


已创建68587行。


SQL> COMMIT;


提交完成。





.

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

PHP code:



SQL> CREATE INDEX IND_T_VIRTUAL_COLUMN_VNAME ON T_VIRTUAL_COLUMN(V_NAME);


索引已创建。


SQL> ALTER TABLE T_VIRTUAL_COLUMN MODIFY V_NAME AS (UPPER(NAME));


ALTER TABLE T_VIRTUAL_COLUMN MODIFY V_NAME AS (UPPER(NAME))


                                    *


第 1 行出现错误:


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





.

一旦建立了索引，Oracle会禁止虚拟列发生修改，但是前面提到了，Oracle并不禁止虚拟列参考的函数的修改：

PHP code:



SQL> CREATE INDEX IND_T_VIRTUAL_COLUMN_VTYPE ON T_VIRTUAL_COLUMN(V_TYPE);


索引已创建。


SQL> SET AUTOT ON EXP


SQL> SELECT COUNT(*) FROM T_VIRTUAL_COLUMN WHERE V_TYPE = 1;


COUNT(*)


--------


    6914





执行计划


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


Plan hash value: 4264298180


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


| Id  | Operation             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |


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


|   0 | SELECT STATEMENT      |                            |     1 |    42 |    53  (33)| 00:00:01 |


|   1 |  SORT AGGREGATE       |                            |     1 |    42 |            |          |


|*  2 |   INDEX FAST FULL SCAN| IND_T_VIRTUAL_COLUMN_VTYPE | 34294 |  1406K|    53  (33)| 00:00:01 |


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


Predicate Information (identified by operation id):


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


   2 - filter("V_TYPE"=1)


SQL> CREATE OR REPLACE FUNCTION F_GETTYPE(P_TYPE IN VARCHAR2) RETURN NUMBER


  2   DETERMINISTIC AS


  3  BEGIN


  4   IF P_TYPE IN ('TABLE', 'INDEX', 'LOB', 'TABLE PARTITION', 'INDEX PARTITION', 'LOB PARTITION', 


  5    'TABLE SUBPARTITON', 'INDEX SUBPARTITION', 'LOB SUBPARTITION', 'CLUSTER') THEN


  6    RETURN -1;


  7   ELSE


  8    RETURN 0;


  9   END IF;


 10  END;


 11  /


函数已创建。


SQL> SELECT COUNT(*) FROM T_VIRTUAL_COLUMN WHERE V_TYPE = 1;


COUNT(*)


--------


    6914





执行计划


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


Plan hash value: 4264298180


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


| Id  | Operation             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |


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


|   0 | SELECT STATEMENT      |                            |     1 |    42 |    53  (33)| 00:00:01 |


|   1 |  SORT AGGREGATE       |                            |     1 |    42 |            |          |


|*  2 |   INDEX FAST FULL SCAN| IND_T_VIRTUAL_COLUMN_VTYPE | 34294 |  1406K|    53  (33)| 00:00:01 |


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


Predicate Information (identified by operation id):


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


   2 - filter("V_TYPE"=1)


SQL> SELECT /*+ FULL(A) */ COUNT(*) FROM T_VIRTUAL_COLUMN A WHERE V_TYPE = 1;


COUNT(*)


--------


       0





执行计划


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


Plan hash value: 3215935171


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


| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |


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


|   0 | SELECT STATEMENT   |                  |     1 |    42 |   154  (12)| 00:00:02 |


|   1 |  SORT AGGREGATE    |                  |     1 |    42 |            |          |


|*  2 |   TABLE ACCESS FULL| T_VIRTUAL_COLUMN | 34294 |  1406K|   154  (12)| 00:00:02 |


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


Predicate Information (identified by operation id):


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


   2 - filter("V_TYPE"=1)


SQL> DROP INDEX IND_T_VIRTUAL_COLUMN_VTYPE;


索引已删除。


SQL> CREATE INDEX IND_T_VIRTUAL_COLUMN_VTYPE ON T_VIRTUAL_COLUMN(V_TYPE);


索引已创建。


SQL> SELECT COUNT(*) FROM T_VIRTUAL_COLUMN WHERE V_TYPE = 1;


COUNT(*)


--------


       0





执行计划


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


Plan hash value: 4264298180


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


| Id  | Operation             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |


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


|   0 | SELECT STATEMENT      |                            |     1 |    42 |    53  (33)| 00:00:01 |


|   1 |  SORT AGGREGATE       |                            |     1 |    42 |            |          |


|*  2 |   INDEX FAST FULL SCAN| IND_T_VIRTUAL_COLUMN_VTYPE | 34294 |  1406K|    53  (33)| 00:00:01 |


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


Predicate Information (identified by operation id):


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


   2 - filter("V_TYPE"=1)





.