Oracle11新特性——虚拟列

coolsooner

浏览: 1314864 次

最近访客更多访客>>

lwvermds

神乎其技1412

枫叶灬

jacket233

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (1948)

社区版块

存档分类

2012-02 ( 3)
2012-01 ( 250)
2011-12 ( 113)
更多存档...

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> SELECT COUNT(*) FROM T_VIRTUAL_COLUMN WHERE V_TYPE = 1;

COUNT(*)
--------
6914

执行计划
----------------------------------------------------------
Plan hash value: 4264298180

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("V_TYPE"=1)

虽然虚拟列不能参考其他的虚拟列，但是可以通过其他的方法来变相实现：

SQL> CREATE TABLE T_V_COL_P
2 (
3 ID NUMBER PRIMARY KEY,
4 NAME VARCHAR2(30),
5 V_NAME AS (LOWER(NAME)),
6 V_COL AS (LENGTH(V_NAME))
7 );
V_NAME AS (LOWER(NAME)),
*
第 5 行出现错误:
ORA-54012: 在列表达式中引用了虚拟列

由于虚拟列并不存储数据，而且数据的生成是在查询的时候，因此可以先建立表，然后将表的主键作为参数传递给虚拟列参考的函数：

SQL> CREATE OR REPLACE FUNCTION F_TEST_VIRTUAL (P_IN NUMBER) RETURN NUMBER DETERMINISTIC AS
2 BEGIN
3 FOR I IN (SELECT LENGTH(V_NAME) LEN FROM T_V_COL_P WHERE ID = P_IN) LOOP
4 RETURN I.LEN;
5 END LOOP;
6 END;
7 /
函数已创建。
SQL> INSERT INTO T_V_COL_P (ID, NAME) VALUES (1, 'TABLE');
已创建 1 行。
SQL> INSERT INTO T_V_COL_P (ID, NAME) VALUES (2, 'INDEX');
已创建 1 行。
SQL> SELECT * FROM T_V_COL_P;
ID NAME V_NAME V_COL
---------- ------------------------------ ------------------------------ ----------
1 TABLE table 5
2 INDEX index 5

通过这个变相的方法，就可以实现虚拟列参考其他的虚拟列。当前，由于虚拟列的数值本身就来自其他的实际列，因此虚拟列参考虚拟列的意义不大。
但是上面给出的方法还是很有意义的，可以利用这个方法实现很多的功能。
举个简单的例子，刚才建立的是主表，有一个子表引用主表：

SQL> CREATE TABLE T_V_COL_F
2 (
3 ID NUMBER,
4 FID NUMBER,
5 NAME VARCHAR2(30),
6 FOREIGN KEY (FID) REFERENCES T_V_COL_P
7 );
表已创建。
SQL> INSERT INTO T_V_COL_F SELECT 100000 + ROWNUM, 1, TABLE_NAME FROM DBA_TABLES;
已创建2493行。
SQL> INSERT INTO T_V_COL_F SELECT 200000 + ROWNUM, 2, INDEX_NAME FROM DBA_INDEXES;
已创建3945行。

如果想查询主表记录的同时查询参考当前主表ID的子表记录数：

SQL> SELECT ID, NAME, V_NAME, (SELECT COUNT(*) FROM T_V_COL_F WHERE FID = A.ID) NUM
2 FROM T_V_COL_P A;
ID NAME V_NAME NUM
---------- ------------------------------ ------------------------------ ----------
1 TABLE table 2493
2 INDEX index 3945

这是常规的写法，而使用虚拟列可以在一张表上实现这个功能：

SQL> CREATE OR REPLACE FUNCTION F_TEST_VIRTUAL (P_IN NUMBER) RETURN NUMBER DETERMINISTIC AS
2 BEGIN
3 FOR I IN (SELECT COUNT(*) NUM FROM T_V_COL_F WHERE FID = P_IN) LOOP
4 RETURN I.NUM;
5 END LOOP;
6 END;
7 /
函数已创建。
SQL> SELECT * FROM T_V_COL_P;
ID NAME V_NAME V_COL
---------- ------------------------------ ------------------------------ ----------
1 TABLE table 2493
2 INDEX index 3945

采用这种方法可以简化很多的问题，而且如果不访问虚拟列，并不会引发对子表的访问。
这种方法唯一需要注意一点，不要造成循环引用：

SQL> CREATE OR REPLACE FUNCTION F_TEST_VIRTUAL (P_IN NUMBER) RETURN NUMBER DETERMINISTIC AS
2 BEGIN
3 FOR I IN (SELECT V_COL FROM T_V_COL_P WHERE ID = P_IN) LOOP
4 RETURN I.V_COL;
5 END LOOP;
6 END;
7 /
函数已创建。
SQL> SELECT * FROM T_V_COL_P;
SELECT * FROM T_V_COL_P
*
第 1 行出现错误:
ORA-00036: 超过递归 SQL 级别的最大值 50
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YANGTK.F_TEST_VIRTUAL", line 3
ORA-06512: 在 "YA