Oracle11新特性——虛拟列

  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并不禁止虛拟列參考的函數的修改，修改方式見前面。