oracle11g中虚拟列有什么用

71次阅读

共计 2049 个字符，预计需要花费 6 分钟才能阅读完成。

这篇文章将为大家详细讲解有关 oracle11g 中虚拟列有什么用，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

由于之前的一个 sql 效率不高，尝试了多种写法，虽然执行计划比较优，但是执行效率还是很低下（结果需要 3s 多），表本身数据量大概是 320W 左右，统计全表，其实业务需求本身理解起来并不复杂，就是对某张表的某一列进行判断，统计这一列在不同范围的数目。于是想起来此业务是可以使用虚拟列来计算这一列的值的。
虚拟列小释：虚拟列是 oracle11g 引入的新特性，它是根据当前表其他列计算出来的列值，此列数据不存储在数据文件中，它只是作为一个表达式存储在数据字典中，因此，此列不能进行 DML 操作，此外，虚拟列不能引用虚拟列。
在虚拟列可以进行如下操作：
1、可以在虚拟列上建立索引；
2、可以在虚拟列上建立约束；
3、可以基于虚拟列进行分区操作。
表结构如下：
create table people (id varchar2(32),name varchar2(10),id_card varchar2(40),medisecu varchar2(50))
comment on column people.name is 姓名
comment on column people.id_card is 身份证号
comment on column people.medisecu is 所买保险品种
插入测试数据后：
select * from people;

业务需求如下，统计买了单保险的有多少人，买了多保险的有多少人。因此我希望在此表单独有一列对此人所买保险是单保险还是多保险做一个判断。
此需求需要用到自定义函数，建立用户自定义函数：
create or replace function fn_medisecu (i_id in varchar2,i_medisecu in varchar2 default null)
return number deterministic –oracle 要求对于用户自定义函数，必须声明函数的确定性（deterministic）
as
v_count pls_integer:=0;—pls_integer 这个数据类型值得关注，效率高于 number，pls_integer 和 number 数值类型介绍，请移步 http://blog.itpub.net/30485601/viewspace-2151857/
begin
select count(people_id)
into v_count
from diagninfo
where people_id=i_id and en_disease_code is not null and disease_jzlx in(1,2,3,4);– 这个不用关注，是此人需要满足的条件
if i_medisecu is not null and v_count 0
then
if i_medisecu= 10
then return 0;
else
if length(replace(i_medisecu, , ,))=1– 单保险
then return 1;
elsif length(replace(i_medisecu, , ,)) 1– 多保险
then return 2;
end if;
end if;
else
return 0;
end if;
end;
建立虚拟列：
alter table people add vir_medisecu number generated always as (fn_medisecu(id,medisecu)) virtual；– 其中 generated 和 always 为可选关键字，写不写都可以，区别不大，如果忽略虚拟列的数据类型，oracle 会根据 as 后的表达式结果的最终数据类型确定此虚拟列的数据类型。
在虚拟列上建立索引，同时收集统计信息：
create index people_vir_medisecu on people(vir_medisecu);
begin
dbms_stats.gather_table_stats(ownname = QJ ,tabname = PEOPLE
end;
begin
dbms_stats.gather_index_stats(ownname = QJ ,indname = PEOPLE_VIR_MEDISECU
end;
再次对单保险和多保险进行统计，结果缩减到了 0.2s。
虚拟列要注意的问题：
1、虚拟列的使用会带来其他问题，包含了虚拟列的表在进行 insert 操作的时候不能省略 column 列表，因此，必须和开发人员确定所有对于虚拟列表的插入完整的写了 column，不然程序会报错；
2、无法使用 create table as select 创建一个包含虚拟列的表, 只能建表之后重新添加虚拟列。