Oracle 11g 虚拟列.docx

如果你要做一些大型OLTP（淘宝京东火车票）的网站，对一些表进行DML操作的时候，容易发生“缺失更新”的情况。我们在做spacer manager做dml模拟的时候，瞬间对数据库多表造成每秒数万的dml操作，如果遇到oracle 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)

更正一下上个邮件的一些问题。如果是在OLTP的库，而且表列较多，或被用来进行HASH运算的列是long或lob类的列时，应放弃第一卦邮件中使用的hash校验，转而使用时间戳，并使用oracle的乐观锁定。

例如原表为：
create table dept(
  deptno number(2),
  dname varchar2(14),
  loc varchar2(13));

加入校验列last_mod：
create table dept(
  deptno number(2),
  dname varchar2(14),
  loc varchar2(13),
  last_mod timestamp with time zone
           default systimestamp
           not null,
  constraint dept_pk primary key(deptno)
  );

插入数据：
insert into dept
  (deptno, dname, loc)
  select deptno, dname, loc from scott.dept;
rows created.
commit;

这是一个简单的实现，如果你想保护数据库表不出现丢失更新问题，应对每个要保护的表增加一列。这一列一般是NUMBER或DATE/TIMESTAMP列，通常通过表上的一个行触发器来维护。每次修改行时，这个触发器要负责递增NUMBER列中的值，或者更新DATE/TIMESTAMP列。
如果应用要实现乐观并发控制，只需要保存这个附加列的值，而不需要保存其他列的所有“前”映像。应用只需验证请求更新那一刻，数据库中这一列的值与最初读出的值是否匹配。如果两个值相等，就说明这一行未被更新过。
以上代码会重建DEPT表，但是将有一个附加的LAST_MOD列，这个列使用TIMESTAMP WITH TIME ZONE数据类型（Oracle9i 及以上版本中才有这个数据类型）。我们将这个列定义为NOT NULL，以保证这个列必须填有数据，其默认值是当前的系统时间。
这个TIMESTAMP数据类型在Oracle中精度最高，通常可以精确到微秒（百万分之一秒）。如果应用要考虑到用户的思考时间，这种TIMESTAMP级的精度实在是绰绰有余，而且数据库获取一行后，人看到这一行，然后修改，再向数据库发回更新，一般不太可能在不到1秒钟的片刻时间内执行整个过程。两个人在同样短的时间内（不到1秒钟）读取和修改同一行的几率实在太小了。
接下来，需要一种方法来维护这个值。我们有两种选择：可以由应用维护这一列，更新记录时将LAST_MOD列的值设置为SYSTIMESTAMP；也可以由触发器/存储过程来维护。如果让应用维护LAST_MOD，这比基于触发器的方法表现更好，因为触发器会代表Oracle对修改增加额外的处理。不过这并不是说：无论什么情况，你都要依赖所有应用在表中经过修改的所有位置上一致地维护LAST_MOD。所以，如果要由各个应用负责维护这个字段，就需要一致地验证LAST_MOD列未被修改，并把LAST_MOD列设置为当前的SYSTIMESTAMP。例如，如果应用查询DEPTNO=10这一行：

variable deptno number
variable dname varchar2(14)
variable loc varchar2(13)
variable last_mod varchar2(50)
begin
  :deptno := 10;
  select dname, loc, last_mod
           into :dname,:loclast_mod
  from dept
  where deptno = :deptno;
end;

目前我们看到的是：
select :deptno dno, :dname dname, :loc loc, :last_mod lm from dual;
DNO           DNAME                 LOC                    LM
----------      ----------                  --------                  ---------------------------
10               ACCOUNTING    NEW YORK       10-JUL-13 10.54.00.493380 AM -04:00

再使用下面的更新语句来修改信息。最后一行执行了一个非常重要的检查，以确保时间戳没有改变，并使用内置函数TO_TIMESTAMP_TZ(TZ是TimeZone的缩写，即时区）将以上select（选择）得到的串转换为适当的数据类型。另外，如果发现行已经更新，以下更新语句中的第3行会把LAST_MOD列更新为当前时间：
update dept
  set dname = initcap(:dname),
  last_mod = systimestamp
  where deptno = :deptno
  and last_mod = to_timestamp_tz(:last_mod);

可以看到，这里更新了一行，也就是我们关心的那一行。在此按主键（DEPTNO）更新了这一行，并验证从最初读取记录到执行更新这段时间，LAST_MOD列未被其他会话修改。如果我们想尝试再更新这个记录，仍然使用同样的逻辑，不过没有获取新的LAST_MOD值，就会观察到以下情况：
update dept
  set dname = upper(:dname),
  last_mod = systimestamp
  where deptno = :deptno
  and last_mod = to_timestamp_tz(:last_mod);

注意到这一次报告称“0 rows updated”（更新了0行），因为关于LAST_MOD的谓词条件不能满足。尽管DEPTNO 10还存在，但是想要执行更新的那个时刻的LAST_MOD值与查询行时的时间戳值不再匹配。所以，应用知道，既然未能修改行，就说明数据库中的数据已经（被别人）改变，现在它必须得出下一步要对此做什么。不能总是依赖各个应用来维护这个字段，原因是多方面的。例如，这样会增加应用程序代码，而且只要是表中需要修改的地方，都必须重复这些代码，并正确地实现。在一个大型应用中，这样的地方可能很多。另外，将来开发的每个应用也必须遵循这些规则。应用程序代码中很可能会“遗漏”某一处，未能适当地使用这个字段。因此，如果应用程序代码本身不负责维护这个LAST_MOD字段，我相信应用也不应负责检查这个LAST_MOD字段（如果它确实能执行检查，当然也能执行更新！）。所以在这种情况下，我建议把更新逻辑封装到一个存储过程中，而不要让应用直接更新表。如果无法相信应用能维护这个字段的值，那么也无法相信它能正确地检查这个字段。存储过程可以取以上更新中使用的绑定变量作为输入，执行同样的更新。当检测到更新了0行时，存储过程会向客户返回一个异常，让客户知道更新实际上失败了。
还有一种实现是使用一个触发器来维护这个LAST_MOD字段，但是对于这么简单的工作，建议还是避免使用触发器，而让DML来负责。触发器会引入大量开销，而且在这种情况下没有必要使用。