Oracle11新特性——虛擬列

文章 出處：http://yangtingkun.itpub.net/post/468/409211

打算寫一系列的文章介紹11g的新特性和變化。

Oracle11g增加了表的虛擬列，這個列的資料並沒有儲存在資料檔案中，而是Oracle透過列資料的生成放到了資料字典中。

看一個簡單的虛擬列的例子：

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都是虛擬列，虛擬列的數值是透過真實列中的資料計算而來的。

虛擬列的位置可以放在它參考的列的前面，也可以包括多個實際列的值，但是不能引用其他的虛擬列：

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自帶的函式，也可以使用使用者定義的函式，不過對於使用者定義的函式要求必須宣告函式的確定性：

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: 指定了無效的列表示式

虛擬列必須是對實際列進行操作後的結果，不能像上面這樣直接寫一個返回常數的函式，換句話說，表不能只包括虛擬列：

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: 函式不能確定

現在錯誤資訊顯示，函式沒有宣告確定性：

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雖然在建立建立的時候會檢查函式的確定性，在表建立之後，卻可以將函式替換為非確定性函式：

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都不能對虛擬列進行修改：

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新增虛擬列。

虛擬列還存在一個檔案，當虛擬列的值一旦被實體化，那麼虛擬列表示式發生變化會造成實體化結果與虛擬列不一致。

簡單的說就是虛擬列的結果是在查詢的時候確定的，如果修改了虛擬列的表示式，下次執行查詢時，虛擬列的值就會發生變化。

但是一旦對虛擬列建立了索引，或者對包含虛擬列的表建立了物化檢視，那麼虛擬列的數值就被實際的儲存下來，當虛擬列的表示式發生修改後，會導致索引或物化檢視中已有的資料與目前虛擬列結果不一致。這個問題的解決方法只有刪除索引並重建，或者將物化檢視完全重新整理。

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並不禁止虛擬列參考的函式的修改：

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)