oracle11g中虚拟列有什么用

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

由于之前的一个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-/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 创建一个包含虚拟列的表,只能建表之后重新添加虚拟列。

关于“oracle11g中虚拟列有什么用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。