關于Oracle數據庫設計、開發、應用的探討

徐 卓

（哈爾濱鐵路局 信息技術所，哈爾濱 150001）

關于Oracle數據庫設計、開發、應用的探討

徐 卓

（哈爾濱鐵路局 信息技術所，哈爾濱 150001）

Oracle數據庫具有良好、穩定的大數據處理能力，安全、高可用的并發數據訪問功能，被鐵路運輸等大型國有企業廣泛應用。隨著Oracle數據庫的不斷升級，不少企業在應用過程中，設計和開發方面存在一些“誤區”，不同程度地導致企業在Oracle數據庫的應用上效率不高，反映遲緩等現象。因此，本文結合作者多年開發和維護Oracle數據庫的經驗、體會，總結了Oracle數據庫在設計、開發、應用方面容易出現的“誤區”，闡明了需要重點注意的若干問題。

I/O；索引；多對多關系

數據庫設計的質量好壞直接關系到開發周期和系統性能，其開發水平則直接影響到代碼的可讀性和可維護性。本文結合筆者多年的工作實踐，主要論述在Oracle數據庫的設計和開發方面的一些切身感悟。

1 數據庫設計

數據庫設計簡單的說就是庫、表的設計，即數據庫物理存儲結構（表空間）和數據表結構的設計。數據庫物理存儲結構涉及諸多操作系統和DBA建庫方面的理論和概念，在此不過多論述。本文重點探討數據表結構設計方面的一些問題。

1.1 多對多關系的表結構設計

Oracle數據庫的表都是一張二維表，關于單獨一張表的結構設計，只需要結合具體實體屬性，適當的符合數據庫第三范式3NF即可。而對于實體之間抽象出的一對一，多對一（一對多）和多對多3種類型的關系，通過Oracle數據庫主、外鍵的合理設置也能夠通過主、從表的方式實現其中的一對一，多對一（一對多）兩種關系。多對多關系的實現，是通過設立中間表來實現的。

例如：表A（項目表）（id,名稱，功能，開發時間，...）；

數據：（1，確報系統，收發確報，2006-08，...）

（2，貨票系統，生成電子貨票，2013-12，...)表B（設備表）（id，名稱，序列號，型號，...）。數據：(1，IBM服務器，99BHBH3，X3650M3，…)（2，HP服務器，G215LJC13G，DL380，…)（3，IBM小機，10C2F8C，P570，…) )

表A中的一個項目可對應多個設備；表B中的一個設備可被多個項目使用，表A和表B是標準的多對多關系。要實現A、B表的多對多關系，通常的方法是引入一個中間表C（項目ID，設備ID，備注）。

表C數據：（1，1，）

（1，3，）

（2，2，）

（2，3，）

通過設置表A的ID和表C的項目ID；表B的ID和表C的設備ID為主外鍵關系，來實現表A、C和表B、C的同步更新，通過表A、B、C的關聯查詢，就可以實現項目表和設備表相關聯數據的多對多查詢和展示。

1.2 索引

通過Oracle數據庫的索引，可以迅速定位記錄的位置，而不必去定位整個表，這樣極大地提高了數據庫的查詢速度。但是，表中的索引越多，維護索引所需要的成本也就越大，每當數據表中記錄有增加、刪除、更新變化時，數據庫系統都需要對所有索引進行更新。所以，數據庫表中的索引絕對不是多多益善。Oracle數據庫創建索引通常應遵循如下原則：

（1）在基數小的字段上要善于使用位圖索引。基數是位圖索引中的一個基本定義，它是指Oracle數據庫表中某個字段內容中不重復的數值。如在員工信息表中的性別字段，一般就只有男跟女兩個值，所以，其基數為2。再如婚姻狀況、民族等字段都適用位圖索引。除了在數據表某列基數比較小的情況下，采用位圖索引外，在Where限制條件中，若多次采用AND或者OR條件時，也建議采用位圖索引。因為當一個查詢，引用了一些部署了位圖索引的列時，這些位圖可以很方便的與AND或者OR 運算符操作結合以快速的找出用戶所需要的記錄。

（2）對于滿足查詢條件的數據不超過10%的查詢列和用于集函數、連接、group by和order by的列，應該建索引。這樣可以利用索引順序的特點加快排序速度。連接中經常使用的列或表中有外鍵約束的列應該建立索引，如果列處在索引順序中則系統可更快執行連接。

（3）對于順序增長的列索引，以及具有如“餐廳甲”，“餐廳乙”等相似但不重復的列值，使用反向鍵索引。

（4）索引與數據表應分別建在不同的表空間上。

（5）表的主鍵列和唯一性約束列自動建立唯一性索引，不需要單獨指定索引。

1.3 分區表

表結構設計一定要考慮應用數據長期積累或暴發式增長對系統性能和數據維護帶來的影響。否則，應用系統在積累了大量數據時再去修改表結構，由此帶來的維護工作量和異常，對開發人員和用戶來說都將是災難性的。Oracle的分區表，就是解決包含大量歷史數據的大數據表的性能和維護瓶頸的一個非常有效的表結構設計方式。

Oracle的分區表具有以下優點：（1）增強可用性：如果表的某個分區出現故障，表在其它分區的數據仍然可用。（2）維護方便：如果表的某個分區出現故障，需要修復數據，只修復該分區即可。（3）均衡I/O：可以把不同的分區映射到磁盤以平衡I/O，改善整個系統性能。（4）改善查詢性能：對分區對象的查詢可以僅搜索自己關心的分區，提高檢索速度。

Oracle的分區表可分為：范圍分區，哈希分區，復合分區，列表分區，混合分區，間隔分區等。對這些分區的具體應用，由于篇幅所限, 在此不再贅述，本文僅對建立數據庫分區表應遵循的原則，作以下簡要說明：

（1）表的大小：當表的大小超過1.5 GB～ 2 GB，或對于OLTP系統，表的記錄超過1 000萬，都應考慮對表進行分區。

（2）數據訪問特性：基于表的大部分查詢應用，只訪問表中少量的數據。對于這樣表進行分區，可充分利用分區排除無關數據查詢的特性。

（3）數據維護：按時間段刪除成批的數據，例如按月刪除歷史數據。對于這樣的表需要考慮進行分區，以滿足維護的需要。

（4）數據備份和恢復：按時間周期進行表空間的備份時，將分區與表空間建立對應關系。

（5）只讀數據：如果一個表中大部分數據都是只讀數據，通過對表進行分區，可將只讀數據存儲在只讀表空間中，對于數據庫的備份有益。

（6）并行數據操作：對于經常執行并行操作（如Parallel Insert，Parallel Update等）的表應考慮進行分區。

（7）表的可用性：當對表的部分數據可用性要求很高時，應考慮進行表分區。

2 數據庫開發和應用

本文只選取SQL共享，數據庫客戶端連接，歷史數據處理等在企業日常的數據庫維護工作中，具有代表性并且容易出錯的3個方面進行分析和探討。

2.1 SQL共享

Oracle將執行過的SQL語句存放在內存的共享池（sharedbuffer pool）中，可以被所有的數據庫用戶共享。當執行一個SQL語句時,如果它和之前執行過的語句完全相同, Oracle就能很快獲得已經被解析的語句以及最好的執行路徑。這就是SQL共享，這個功能大大地提高了SQL的執行性能并節省了內存空間。

SQL共享有3個條件：（1）當前被執行的語句和共享池中的語句必須完全相同（包括大小寫、空格、換行等）。（2）兩個語句所指的對象必須完全相同（同義詞與表是不同的對象）。（3）兩個SQL語句中必須使用相同名字的綁定變量（bindvariables）。

其中，綁定變量是許多數據庫開發人員在編程時容易忽視之處。如：select * from 表B where序列號=‘99BHBH3’；上面這個語句，每執行一次就需要在SHARE POOL 硬解析一次，100萬用戶就是100萬次，消耗CPU和內存，如果業務量大，很可能導致宕庫。如果綁定變量，則只需要硬解析一次，重復調用即可。

以下是綁定變量與不綁定變量在寫法上的異同，在數據庫編程時盡可能的應用綁定變量的方式。

未使用綁定變量的語句：

sprintf(sqlstr, "insert into scott.test1 (num1, num2) values (%d,%d)",n_var1, n_var2);

EXEC SQL EXECUTE IMMEDIATE :sqlstr ;

EXEC SQL COMMIT;

使用綁定變量的語句：

strcpy(sqlstr, "insert into test (num1, num2) values (:v1, :v2)");

EXEC SQL PREPARE sql_stmt FROM :sqlstr;

EXEC SQL EXECUTE sql_stmt USING : n_var1, :n_var2;

EXEC SQL COMMIT;

2.2 數據庫連接

通常情況下，應用程序與后臺的Oracle數據庫進行數據交換是通過Oralcle的客戶端軟件,配置TNASNAMES.ORA文件中的“連接串”來實現的。目前,很多企業的后臺Oracle數據庫都建成了兼顧安全和效率的RAC方式。但是客戶端的數據庫連接配置，還停留在以往單機數據庫的配置方式。并沒有真正發揮RAC數據庫對客戶端的負載均衡和故障切換功能。以下是兩個客戶端的TNSNAMES.ORA 文件的配置：

（1）客戶端A：

SMIS.MZL=

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=10.25.3.25)

(PORT=1521)

)

(CONNECT_DATA=

(SERVER=dedicated)

(SERVICE_NAME=mzl)

)

) )

（2）客戶端B:

SMIS.MZL=

(DESCRIPTION=

(ADDRESS_LIST=

(FAILOVER=on)

(LOAD_BALANCE=off)

(ADDRESS=

(PROTOCOL=TCP)

(HOST=10.27.3.25)

(PORT=1521)

)

(ADDRESS=

(PROTOCOL=TCP)

(HOST=10.16.3.27)

(PORT=1521)

)

)

(CONNECT_DATA=

(FAILOVER_MODE=

(TYPE=select)

(METHOD=basic)

(RETRIES=50)

(DELAY=5)

)

(SERVER=dedicated)

(SERVICE_NAME=mzl)

)

)

客戶端A只連接了10.27.3.25上的單個數據庫實例，當10.27.3.25這個RAC節點因故宕機時，客戶端連接不能實現自動切換到另外的數據庫節點。客戶端B的配置實現了對RAC數據庫10.25.3.25和10.25.3.27兩個節點的故障切換功能。

客戶端B關閉了對兩個數據庫節點的負載均衡功能(LOAD_BALANCE=off)。這是因為，有時為了避免節點爭用，要進行人為的用戶分區。即把執行相同業務功能的用戶，固定連接到同一個數據庫節點上，避免不同的節點，訪問同一個數據塊，形成節點爭用，影響性能。客戶端B把10.27.3.25配置在前面，則每次都連接10.27.3.25節點，只有10.27.3.25節點因故無法啟動時，才連接10.27.3.27節點。上述客戶端配置LOAD_BALANCE=off，有時需要將后臺數據庫的remote_listener參數置空才能生效。

2.3 歷史數據處理

建議采取如下方式處理應用項目的歷史數據：（1）建立單獨表空間，用戶來管理歷史數據。（2）在單獨的表空間中建立與產生歷史數據的表同結構的數據表來存放歷史數據。

（3）建立通用的管理表，記錄哪些生產表及對應子表需要歷史；這些表的關聯字段、時間字段、歷史周期、刪除歷史數據周期以及相應的where條件等信息。

（4）編寫通用的存儲過程，根據（3）中管理表的記錄，負責按相應的where條件，定期將記錄的生產表及子表中的數據轉儲到歷史表中，并按記錄的周期刪除歷史表中的過期數據。

（5）調試應用程序，使其具備專門的查詢生產數據和歷史數據的功能。

3 結束語

Oracle數據庫的設計和開發是一個抽象性、系統性、規律性、挑戰性相結合的工作。以上只是拋磚引玉，淺嘗輒止地談了一些筆者在數據庫設計和開發過程中的一些經驗、感悟，希望大家能從中有所啟發。

[1] 盧 濤. 劍破冰山—Oracle開發藝術[M].北京：電子工業出版社，2011.

[2] 羅 敏. Oracle數據庫高級技術交流—大批量數據處理技術[EB/OL]. http://wenku.baidu.com/list/161，2011.

[3] Bill Karwin. SQL反模式[M]. 譚振林，譯.北京：人民郵電出版社，2011.

責任編輯 陳 蓉

U29∶TP392

A

1005-8451（2014）09-0059-04

2014-03-04

徐 卓，高級工程師。