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.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 建立一個包含虛擬列的表,只能建表之後重新新增虛擬列。