Oracle Database 11g New Features - Virtual Column (虛擬欄位)

在資料庫設計的相關技巧中，正規化是DBA耳熟能詳的基本技巧，而基本的正規化至少要達到第三正規化，才有正規化的效果。正規化可以帶來以下的一些好處：1.刪除重複資料。2.節省儲存空間。當然某些情況下，正規化可能導致效能上的問題。這時可以適時地使用反正規化來增加整體的效能，不過在進行反正規化之前，當然還是要先正規化，因為不是每個表格都需要反正規化。以下對正規化的規則做一些簡單的說明：

第一正規化：每個表格中都要有主鍵(Primary Key)用以辨識資料列，而且表格中不能有重複的欄位，同時每個欄位中只能有一筆值。
學號為主鍵，沒有重複欄位以及每個欄位只有一個值。因此範例表格滿足第一正規化。
第二正規化：當滿足第一正規化的要求後，表格中非主鍵的欄位，還必須跟主鍵有完全相依性(即可以由主鍵決定其他非主鍵的欄位)。
國文、數學、英文都與學號有著完全的相依性。所以此表格也滿足第二正規化的要求。
第三正規化：當滿足第二正規化後，非主鍵欄位之間不能存在相依性。
國文、數學、英文這些非主鍵欄位之間並沒有相依性。所以這個範例表格可以說符合第三正規化的表格。
也許有人會建議再加上一個欄位：總分，用來存放三科的總成績。可是總分除與學號(主鍵)相依外，還與國文、數學、英文的成績相依，為達到第三正規化的要求，所以不能加上總分的欄位。
因此當需要計算學生的總成績時，可以由下列方式得到總成績。
1.由AP人員必須將計算公式(國文+數學+英文)寫到程式中，這種方法的好處是，表格依然維持正規化的設計，但是若計算公式一但有所變動，則需要修改程式內容，以及必須將新的程式部署到各個客戶端。
2.然而DBA也可以透過建立一個視圖(VIEW)，將計算公式產生總分的虛擬欄位(create view student_view as select 國文,數學,英文,(國文+數學+英文) as 總分 from student)，這樣便可以透過視圖存取總分的資料。這種方式的缺點是必須多花一些維護成本在視圖的管理上。（
3.DBA直接在Student表格上，多加一個總分的欄位，其內容值為國文、數學、英文的總和。並利用觸發器(Trigger)在相關分數(國文、數學、英文)變動時，自動維護總分的內容，以保持資料的一致性。此方式即所謂的反正規化，可以增加查詢的效能，但也造成儲存的浪費，也增加了資料不一致的風險。所以反正規化在使用前必須仔細設計，避免相關的問題發生。

虛擬欄位(Virtual Column)介紹
由Oracle Database 11g開始，DBA可以宣告一種欄位形態：虛擬欄位，這種欄位並沒有真正的消耗儲存空間，只有在表格定義中存在。當此欄位被查詢時，才會使用欄位定義的公式計算欄位值。欄位定義的公式可以包括任何合法的運算以及函數。
SQL> create table t1
2 (a number,b number,c number generated always as (a+b) virtual);
/*generated always是為語法清晰而存在，virtual也是相同用途。所以可以直接使用c number as (a+b)即可*/


SQL> desc t1 /*這裡看不出來，是否有使用虛擬欄位*/
Name Null? Type
--------------------------------------------------------- ------ ---------
A NUMBER
B NUMBER
C NUMBER

SQL> select column_name,data_type,data_default from user_tab_columns
2> where table_name='T1';

COLUMN_NAM DATA_TYPE DATA_DEFAULT
---------- ---------- --------------------
A NUMBER
B NUMBER
C NUMBER "A"+"B" /*欄位公式*/


SQL> insert into t1 values(1,2,3); /*不能直接Insert/Update虛擬欄位的內容*/
insert into t1 values(1,2,3)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

SQL> insert into t1 values(1,2);
insert into t1 values(1,2)
*
ERROR at line 1:
ORA-00947: not enough values

SQL> insert into t1(a,b) values(1,2); /*必須如此才能新增成功*/
或 insert into t1 values(1,2,default);
1 row created.

SQL> select * from t1;

A B C
---------- ---------- ----------
1 2 3


/*找出row真正存放的位置*/
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,
2 dbms_rowid.rowid_block_number(rowid) block#
3 from t1 where a=1 and b=2 and c=3;

FILE# BLOCK#
---------- ----------
1 40378

/*將Data Block內容dump到user tracefile*/
SQL> alter system dump datafile 1 block 40378;

SQL> show parameter user_dump_dest /*確認User tracefile的位置*/
NAME TYPE VALUE
----------------------------- --------- ----------------------------
user_dump_dest string /u03/diag/diag/rdbms/ora11g
/ora11g/trace

/*找到Server Process的OS Process ID*/
SQL> select spid from v$process
2 where addr=(select paddr from v$session where
3 sid=(select distinct sid from v$mystat));

SPID
------------------------
5000

SQL> !cat /u03/diag/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5000.trc

從Data Block的內容來看，可以證明虛擬欄位-C，並未佔有任何空間。
block_row_dump:
tab 0, row 0, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02 /*A欄位*/
col 1: [ 2] c1 03 /*B欄位*/
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 40378 maxblk 40378

使用User Defined Function的注意事項
當虛擬欄位用到User Defined Function時，必須確定函數是Deterministic，即只要輸入相同的參數值，函數就一定回傳相同的結果。
SQL> create or replace function minus_10(x number) return number
2 is
3 begin
4 return x-10;
5 end;
6 /

Function created.

SQL> create table t2 (a number,b number,c number as (minus_10(a+b)) virtual);
create table t2 (a number,b number,c number as (minus_10(a+b)) virtual)
*
ERROR at line 1:
ORA-30553: The function is not deterministic
SQL> create or replace function minus_10(x number) return number deterministic
2 is
3 begin
4 return x-10;
5 end;
6 /

Function created.

SQL> create table t2 (a number,b number,c number as (minus_10(a+b) virtual);
create table t2 (a number,b number,c number as (minus_10(a+b) virtual)
*
ERROR at line 1:
ORA-02000: missing ) keyword


SQL> create table t2 (a number,b number,c number as (minus_10(a+b)) virtual);

Table created.

虛擬欄位的操作
虛擬欄位在Oracle Database 11g中，被當作一般的欄位，所以除了不能直接新增、異動其內容外，其餘的操作如：當做主鍵、建立索引等都可以正常運行。
SQL> create index t1_c_idx on t1(c);

Index created.

SQL> select index_name,index_type from user_indexes where table_name='T1';

INDEX_NAME INDEX_TYPE
------------------------------- ----------------------
T1_C_IDX FUNCTION-BASED NORMAL
/*從INDEX_TYPE可以得知，當建立索引在虛擬欄位上時，所建立的索引為Function-Based形態*/

SQL> alter table t1 add constraints t1_c_pk primary key(c);

Table altered.

SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='T1';

CONSTRAINT_NAME C
------------------------ --
T1_C_PK P /*P表示為Primary Key*/

SQL> execute dbms_stats.gather_table_stats('SYS','T1'); /*收集統計值*/

PL/SQL procedure successfully completed.

/*虛擬欄位也可以當作分區欄位，這種分區方式稱為Virtual Column-Based Partitioning*/
SQL> create table t3
2 (a number,b number,c number as (a+b+100) virtual)
3 partition by range (c)
4 (partition p1 values less than(150),
5 partition p2 values less than(250),
6 partition p3 values less than(350));


虛擬欄位的使用限制
目前虛擬欄位在使用上，還存在著一些限制：
1.僅適用在Heap Table上，還不能用在Index-Organized、Cluster、External、Temporary以及Object等表格上。
2.虛擬欄位不能參考其他的虛擬欄位，只能參考同一個表格中的真實欄位。
3.當使用函數時，此函數必須宣告為Deterministic以及回傳的資料型態必須為Scalar。
4.虛擬欄位的資料型態不能為PL/SQL的資料形態、User Defined Type、LOB以及Long Raw等型態。
了解以上這些限制，可以幫助DBA在使用虛擬欄位時，更加的得心應手。

結論
資料庫表格設計的正規化是DBA的基本功，也可以減少資料庫的資料量，避免資料不一致的情況，同時更可以增加資料庫整體的效能。然而某一情況下，DBA可能採用反正規化的技巧，來解決某些效能上的問題，不過重複資料與內容不一致的風險也同時增加。如果DBA能夠正確地使用虛擬欄位的功能，便可以一方面擁有正規化的好處，同時也可以達到原本需要反正規化才能得到的效能提昇。