Oracle 11g 虚拟列.docx
《Oracle 11g 虚拟列.docx》由会员分享，可在线阅读，更多相关《Oracle 11g 虚拟列.docx（12页珍藏版）》请在冰豆网上搜索。
Oracle11g虚拟列
如果你要做一些大型OLTP（淘宝京东火车票）的网站，对一些表进行DML操作的时候，容易发生“缺失更新”的情况。
我们在做spacermanager做dml模拟的时候，瞬间对数据库多表造成每秒数万的dml操作，如果遇到oracle11g的库，可以采用这种思路，防止产生缺失更新的现象。
这时Oracle11g增加了表的虚拟列，就能比较好的处理这个问题。
这个虚拟列的数据并没有存储在数据文件中，而是Oracle通过列数据的生成放到了数据字典中。
看一个简单的例子：
SQL>CREATEORREPLACEFUNCTIONF_GETTYPE（P_TYPEINVARCHAR2）RETURNNUMBER
2DETERMINISTICAS
3BEGIN
4IFP_TYPEIN（'TABLE','INDEX','LOB','TABLEPARTITION','INDEXPARTITION','LOBPARTITION',
5'TABLESUBPARTITON','INDEXSUBPARTITION','LOBSUBPARTITION','CLUSTER'）THEN
6RETURN1;
7ELSE
8RETURN0;
9ENDIF;
10END;
11/
函数已创建。
SQL>CREATETABLET_VIRTUAL_COLUMN
2（
3IDNUMBERPRIMARYKEY,
4V_LENGTHAS（CEIL（LENGTH（TO_CHAR（ID））/2）+1+LENGTH（NAME）+LENGTH（TYPE））,
5NAMEVARCHAR2（30）,
6V_NAMECHAR（50）GENERATEDALWAYSAS（LOWER（NAME））VIRTUAL,
7TYPEVARCHAR2（30）,
8V_TYPEAS（F_GETTYPE（TYPE））
9）;
表已创建。
上面例子中，V_LENGTH、V_NAME和V_TYPE都是虚拟列，虚拟列的数值是通过真实列中的数据计算而来的。
虚拟列的位置可以放在它参考的列的前面，也可以包括多个实际列的值，但是不能引用其他的虚拟列：
SQL>CREATETABLET_VIRTUAL_COLUMN_ERR
2（IDNUMBER,
3V_ID1AS（ID*5）,
4V_ID2AS（V_ID1+45）
5）;
V_ID1AS（ID*5）,
*第3行出现错误:
ORA-54012:
在列表达式中引用了虚拟列
虚拟列的完整写法如上面例子中V_NAME列，包括列名、数据类型、GENERATEDALWAYS关键字、AS加列表达式和VIRTUAL关键字。
其中GENERATEDALWAYS和VIRTUAL为可选关键字，主要用于描述虚拟列的特性，写与不写没有本质区别。
而列的数据类型如果忽略，那么Oracle会根据AS后面的表达式最终结果的数据类型来确定虚拟列的数据类型。
虚拟列可以使用Oracle自带的函数，也可以使用用户定义的函数，不过对于用户定义的函数要求必须声明函数的确定性：
SQL>CREATEORREPLACEFUNCTIONF_TESTRETURNNUMBERAS
2BEGIN
3RETURN1;
4END;
5/
函数已创建。
SQL>CREATETABLET_VIRTUAL_COLUMN_DETER
2（IDNUMBER,VIDAS（F_TEST））;
（IDNUMBER,VIDAS（F_TEST））
*第2行出现错误:
ORA-54016:
指定了无效的列表达式
虚拟列必须是对实际列进行操作后的结果，不能像上面这样直接写一个返回常数的函数，换句话说，表不能只包括虚拟列：
SQL>CREATEORREPLACEFUNCTIONF_TEST（P_ININNUMBER）RETURNNUMBERAS
2BEGIN
3RETURN1;
4END;
5/
函数已创建。
SQL>CREATETABLET_VIRTUAL_COLUMN_DETER
2（
3IDNUMBER,
4V_IDAS（F_TEST（ID））
5）;
V_IDAS（F_TEST（ID））
*第4行出现错误:
ORA-30553:
函数不能确定
现在错误信息显示，函数没有声明确定性：
SQL>CREATEORREPLACEFUNCTIONF_TEST（P_ININNUMBER）RETURNNUMBERDETERMINISTICAS
2BEGIN
3RETURN1;
4END;
5/
函数已创建。
SQL>CREATETABLET_VIRTUAL_COLUMN_DETER
2（
3IDNUMBER,
4V_IDAS（F_TEST（ID））
5）;
表已创建。
Oracle虽然在创建创建的时候会检查函数的确定性，在表建立之后，却可以将函数替换为非确定性函数：
SQL>INSERTINTOT_VIRTUAL_COLUMN_DETER（ID）VALUES
（1）;
已创建1行。
SQL>SELECT*FROMT_VIRTUAL_COLUMN_DETER;
IDV_ID
--------------------
11
SQL>DROPFUNCTIONF_TEST;
函数已删除。
SQL>SELECT*FROMT_VIRTUAL_COLUMN_DETER;
SELECT*FROMT_VIRTUAL_COLUMN_DETER
*第1行出现错误:
ORA-00904:
"YANGTK"."F_TEST":
标识符无效
SQL>CREATEORREPLACEFUNCTIONF_TEST（P_ININNUMBER）RETURNNUMBERAS
2BEGIN
3RETURN2;
4END;
5/
函数已创建。
SQL>SELECT*FROMT_VIRTUAL_COLUMN_DETER;
IDV_ID
--------------------
12
建立了虚拟列可以有效的减少数据的存储，简化查询语句中对列进行的处理，而且还可以利用虚拟列进行分区。
不过虚拟列还会带来其他问题。
首先包含了虚拟列的表在INSERTINTO语句中不能省略COLUMN列表。
由于虚拟列的值是由其他列的值计算得出的，且Oracle并不存储虚拟列的值，因此无论是INSERT还是UPDATE都不能对虚拟列进行修改：
SQL>INSERTINTOT_VIRTUAL_COLUMN
2SELECTROWNUMID,NULL,OWNER,OBJECT_NAME,NULL,OBJECT_TYPE,NULL
3FROMDBA_OBJECTS;
INSERTINTOT_VIRTUAL_COLUMN
*第1行出现错误:
ORA-00913:
值过多
SQL>INSERTINTOT_VIRTUAL_COLUMN
2SELECTROWNUMID,NULL,OBJECT_NAME,NULL,OBJECT_TYPE,NULL
3FROMDBA_OBJECTS;
INSERTINTOT_VIRTUAL_COLUMN
*第1行出现错误:
ORA-54013:
不允许对虚拟列执行INSERT操作
SQL>INSERTINTOT_VIRTUAL_COLUMN
2SELECTROWNUMID,OBJECT_NAME,OBJECT_TYPE
3FROMDBA_OBJECTS;
INSERTINTOT_VIRTUAL_COLUMN
*第1行出现错误:
ORA-00947:
没有足够的值
SQL>INSERTINTOT_VIRTUAL_COLUMN（ID,NAME,TYPE）
2SELECTROWNUMID,OBJECT_NAME,OBJECT_TYPE
3FROMDBA_OBJECTS;
已创建68587行。
SQL>COMMIT;
提交完成。
如果程序选择使用了一些工具来自动生成表的INSERT、UPDATE语句，那么遇到包含虚拟列的表就会报错。
而且出于同样的原因，无法使用CREATETABLEASSELECT创建一个包含虚拟列的表。
解决方法是CREATETABLEASSELECT结束后通过ALTERTABLE添加虚拟列。
虚拟列还存在一个文件，当虚拟列的值一旦被实体化，那么虚拟列表达式发生变化会造成实体化结果与虚拟列不一致。
简单的说就是虚拟列的结果是在查询的时候确定的，如果修改了虚拟列的表达式，下次执行查询时，虚拟列的值就会发生变化。
但是一旦对虚拟列建立了索引，或者对包含虚拟列的表建立了物化视图，那么虚拟列的数值就被实际的存储下来，当虚拟列的表达式发生修改后，会导致索引或物化视图中已有的数据与目前虚拟列结果不一致。
这个问题的解决方法只有删除索引并重建，或者将物化视图完全刷新。
SQL>CREATEINDEXIND_T_VIRTUAL_COLUMN_VNAMEONT_VIRTUAL_COLUMN（V_NAME）;
索引已创建。
SQL>ALTERTABLET_VIRTUAL_COLUMNMODIFYV_NAMEAS（UPPER（NAME））;
ALTERTABLET_VIRTUAL_COLUMNMODIFYV_NAMEAS（UPPER（NAME））
*第1行出现错误:
ORA-54022:
无法更改虚拟列表达式,因为在列上定义了索引
一旦建立了索引，Oracle会禁止虚拟列发生修改，但是前面提到了，Oracle并不禁止虚拟列参考的函数的修改：
SQL>CREATEINDEXIND_T_VIRTUAL_COLUMN_VTYPEONT_VIRTUAL_COLUMN（V_TYPE）;
索引已创建。
SQL>SETAUTOTONEXP
SQL>SELECTCOUNT（*）FROMT_VIRTUAL_COLUMNWHEREV_TYPE=1;
COUNT（*）
--------
6914
执行计划
----------------------------------------------------------
Planhashvalue:
4264298180
----------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
----------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|42|53（33）|00:
00:
01|
|1|SORTAGGREGATE||1|42|||
|*2|INDEXFASTFULLSCAN|IND_T_VIRTUAL_COLUMN_VTYPE|34294|1406K|53（33）|00:
00:
01|
----------------------------------------------------------------------------------------------------
PredicateInformation（identifiedbyoperationid）:
---------------------------------------------------
2-filter（"V_TYPE"=1）
SQL>CREATEORREPLACEFUNCTIONF_GETTYPE（P_TYPEINVARCHAR2）RETURNNUMBER
2DETERMINISTICAS
3BEGIN
4IFP_TYPEIN（'TABLE','INDEX','LOB','TABLEPARTITION','INDEXPARTITION','LOBPARTITION',
5'TABLESUBPARTITON','INDEXSUBPARTITION','LOBSUBPARTITION','CLUSTER'）THEN
6RETURN-1;
7ELSE
8RETURN0;
9ENDIF;
10END;
11/
函数已创建。
SQL>SELECTCOUNT（*）FROMT_VIRTUAL_COLUMNWHEREV_TYPE=1;
COUNT（*）
--------
6914
执行计划
----------------------------------------------------------
Planhashvalue:
4264298180
----------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
----------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|42|53（33）|00:
00:
01|
|1|SORTAGGREGATE||1|42|||
|*2|INDEXFASTFULLSCAN|IND_T_VIRTUAL_COLUMN_VTYPE|34294|1406K|53（33）|00:
00:
01|
----------------------------------------------------------------------------------------------------
PredicateInformation（identifiedbyoperationid）:
---------------------------------------------------
2-filter（"V_TYPE"=1）
SQL>SELECT/*+FULL（A）*/COUNT（*）FROMT_VIRTUAL_COLUMNAWHEREV_TYPE=1;
COUNT（*）
--------
0
执行计划
----------------------------------------------------------
Planhashvalue:
3215935171
---------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
---------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|42|154（12）|00:
00:
02|
|1|SORTAGGREGATE||1|42|||
|*2|TABLEACCESSFULL|T_VIRTUAL_COLUMN|34294|1406K|154（12）|00:
00:
02|
---------------------------------------------------------------------------------------
PredicateInformation（identifiedbyoperationid）:
---------------------------------------------------
2-filter（"V_TYPE"=1）
SQL>DROPINDEXIND_T_VIRTUAL_COLUMN_VTYPE;
索引已删除。
SQL>CREATEINDEXIND_T_VIRTUAL_COLUMN_VTYPEONT_VIRTUAL_COLUMN（V_TYPE）;
索引已创建。
SQL>SELECTCOUNT（*）FROMT_VIRTUAL_COLUMNWHEREV_TYPE=1;
COUNT（*）
--------
0
执行计划
----------------------------------------------------------
Planhashvalue:
4264298180
----------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
----------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|42|53（33）|00:
00:
01|
|1|SORTAGGREGATE||1|42|||
|*2|INDEXFASTFULLSCAN|IND_T_VIRTUAL_COLUMN_VTYPE|34294|1406K|53（33）|00:
00:
01|
----------------------------------------------------------------------------------------------------
PredicateInformation（identifiedbyoperationid）:
---------------------------------------------------
2-filter（"V_TYPE"=1）
更正一下上个邮件的一些问题。
如果是在OLTP的库，而且表列较多，或被用来进行HASH运算的列是long或lob类的列时，应放弃第一卦邮件中使用的hash校验，转而使用时间戳，并使用oracle的乐观锁定。
例如原表为：
createtabledept（
deptnonumber
（2）,
dnamevarchar2（14）,
locvarchar2（13））;
加入校验列last_mod：
createtabledept（
deptnonumber
（2）,
dnamevarchar2（14）,
locvarchar2（13）,
last_modtimestampwithtimezone
defaultsystimestamp
notnull,
constraintdept_pkprimarykey（deptno）
）;
插入数据：
insertintodept
（deptno,dname,loc）
selectdeptno,dname,locfromscott.dept;
rowscreated.
commit;
这是一个简单的实现，如果你想保护数据库表不出现丢失更新问题，应对每个要保护的表增加一列。
这一列一般是NUMBER或DATE/TIMESTAMP列，通常通过表上的一个行触发器来维护。
每次修改行时，这个触发器要负责递增NUMBER列中的值，或者更新DATE/TIMESTAMP列。
如果应用要实现乐观并发控制，只需要保存这个附加列的值，而不需要保存其他列的所有“前”映像。
应用只需验证请求更新那一刻，数据库中这一列的值与最初读出的值是否匹配。
如果两个值相等，就说明这一行未被更新过。
以上代码会重建DEPT表，但是将有一个附加的LAST_MOD列，这个列使用TIMESTAMPWITHTIMEZONE数据类型（Oracle9i及以上版本中才有这个数据类型）。
我们将这个列定义为NOTNULL，以保证这个列必须填有数据，其默认值是当前的系统时间。
这个TIMESTAMP数据类型在Oracle中精度最高，通常可以精确到微秒（百万分之一秒）。
如果应用要考虑到用户的思考时间，这种TIMESTAMP级的精度实在是绰绰有余，而且数据库获取一行后，人看到这一行，然后修改，再向数据库发回更新，一般不太可能在不到1秒钟的片刻时间内执行整个过程。
两个人在同样短的时间内（不到1秒钟）读取和修改同一行的几率实在太小了。
接下来，需要一种方法来维护这个值。
我们有两种选择：
可以由应用维护这一列，更新记录时将LAST_MOD列的值设置为SYSTIMESTAMP；也可以由触发器/存储过程来维护。
如果让应用维护LAST_MOD，这比基于触发器的方法表现更好，因为触发器会代表Oracle对修改增加额外的处理。
不过这并不是说：
无论什么情况，你都要依赖所有应用在表中经过修改的所有位置上一致地维护LAST_MOD。
所以，如果要由各个应用负责维护这个字段，就需要一致地验证LAST_MOD列未被修改，并把LAST_MOD列设置为当前的SYSTIMESTAMP。
例如，如果应用查询DEPTNO=10这一行：
variabledeptnonumber
variablednamevarchar2（14）
variablelocvarchar2（13）
variablelast_modvarchar2（50）
begin
:
deptno:
=10;
selectdname,loc,
升级会员 