oracle11g中虛擬列有什么用

220次閱讀

共計 2049 個字符，預(yù)計需要花費 6 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關(guān) oracle11g 中虛擬列有什么用，丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

由于之前的一個 sql 效率不高，嘗試了多種寫法，雖然執(zhí)行計劃比較優(yōu)，但是執(zhí)行效率還是很低下（結(jié)果需要 3s 多），表本身數(shù)據(jù)量大概是 320W 左右，統(tǒng)計全表，其實業(yè)務(wù)需求本身理解起來并不復(fù)雜，就是對某張表的某一列進行判斷，統(tǒng)計這一列在不同范圍的數(shù)目。于是想起來此業(yè)務(wù)是可以使用虛擬列來計算這一列的值的。
虛擬列小釋：虛擬列是 oracle11g 引入的新特性，它是根據(jù)當(dāng)前表其他列計算出來的列值，此列數(shù)據(jù)不存儲在數(shù)據(jù)文件中，它只是作為一個表達式存儲在數(shù)據(jù)字典中，因此，此列不能進行 DML 操作，此外，虛擬列不能引用虛擬列。
在虛擬列可以進行如下操作：
1、可以在虛擬列上建立索引；
2、可以在虛擬列上建立約束；
3、可以基于虛擬列進行分區(qū)操作。
表結(jié)構(gòu)如下：
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 所買保險品種
插入測試數(shù)據(jù)后：
select * from people;

業(yè)務(wù)需求如下，統(tǒng)計買了單保險的有多少人，買了多保險的有多少人。因此我希望在此表單獨有一列對此人所買保險是單保險還是多保險做一個判斷。
此需求需要用到自定義函數(shù)，建立用戶自定義函數(shù)：
create or replace function fn_medisecu (i_id in varchar2,i_medisecu in varchar2 default null)
return number deterministic –oracle 要求對于用戶自定義函數(shù)，必須聲明函數(shù)的確定性（deterministic）
as
v_count pls_integer:=0;—pls_integer 這個數(shù)據(jù)類型值得關(guān)注，效率高于 number，pls_integer 和 number 數(shù)值類型介紹，請移步 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);– 這個不用關(guān)注，是此人需要滿足的條件
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 為可選關(guān)鍵字，寫不寫都可以，區(qū)別不大，如果忽略虛擬列的數(shù)據(jù)類型，oracle 會根據(jù) as 后的表達式結(jié)果的最終數(shù)據(jù)類型確定此虛擬列的數(shù)據(jù)類型。
在虛擬列上建立索引，同時收集統(tǒng)計信息：
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;
再次對單保險和多保險進行統(tǒng)計，結(jié)果縮減到了 0.2s。
虛擬列要注意的問題：
1、虛擬列的使用會帶來其他問題，包含了虛擬列的表在進行 insert 操作的時候不能省略 column 列表，因此，必須和開發(fā)人員確定所有對于虛擬列表的插入完整的寫了 column，不然程序會報錯；
2、無法使用 create table as select 創(chuàng)建一個包含虛擬列的表, 只能建表之后重新添加虛擬列。