Oracle資料庫開發指南（原創）

Oracle資料庫系統是我們公司軟體的核心部分，資料庫效能的好壞直接關係到整個計算機系統的好壞乃至成敗。Oracle資料庫系統是一個高效，同時也是一個複雜的系統，不同的結構設計，不同的程式設計方法雖然都可以完成特定的功能，但是在效能、可靠性以及可維護性上可能會有巨大的差別。我們寫這篇文件的目的是針對在資料庫使用開發中發現過的問題，為公司內從事Oracle資料庫開發的技術人員提供在設計和開發上的一些建議，使得我們的資料庫系統和軟體系統達到一定程度上的規範化，提高系統的效能，也使得維護應用程式、改變工作方式和增加新功能更為容易。

[@more@]

1．設計時的考慮

由於資料庫物理結構上的設計主要是由DBA完成，所以這裡主要是針對資料庫邏輯結構，例如表和索引的設計，建立等，提供一些建議：

l         資料庫的表和列的命名應使用具有意義的名稱，避免使用ID、TEMP1、A1、A2等無意義或含義不夠明確的識別符號作為表或列的名稱。

l         對於每個表和列，應儘可能地使用COMMENT命令增加註釋，這樣有助於其他人員更好地理解表中資料。例如

COMMENT ON TABLE emp IS ‘Employee information ’;

COMMENT ON COLUMN emp.emp_id IS ‘Unique Employee Identity Number’;

l         對於變長字串的列應使用VARCHAR2型別，而不是CHAR。在Oracle資料庫中，任何非空的CHAR(255)資料，即使其值只有一個字元，它也將佔據255個位元組的磁碟空間，並且在它所出現的任何索引中也要佔用255個位元組的空間。

l         表和索引應放在不同的表空間（tablespace）中，表和索引的分離有益於效能，也利於維護和管理。對於SCM系統，建立新表時可不指定表空間（我們已經指定了預設的表空間SCMD），但是建立索引（包括Primary key，Unique Key）時，應指定表空間SCMX。其語法如下：

CREATE TABLE emp_info

   ( emp_id         INTEGER,

enp_name      VARCHAR2(32),

create_date    DATE,

CONSTRAINT pk_emp_info PRIMARY KEY (emp_id)

   USING INDEX TABLESPACE scmx

);

        或者

　　　　 CREATE INDEX idx_emp_info_name

           　ON emp_info ( emp_name )

           　TABLESPACE scmx;

l         對於程式中使用比較頻繁，比較複雜冗長的SQL查詢，例如三個以上表的連線條件查詢，應透過預先建立檢視VIEW來簡化程式中的SQL語句，這樣既使得程式更易讀，同時也在表結構和關係發生變化時，我們可以不需要修改程式，而只需要修改相應的檢視的定義即可。

2．開發時應注意的地方

資料庫效能變差80％是由糟糕的SQL語句造成的，所以編寫好的SQL對於保證資料庫的正常執行非常重要，以下是我們DBA根據資料庫的特點而給出的建議。

2.1 讓你的SQL 語句更易讀

儘管實際上易讀性不會影響SQL 語句的效能，好的程式設計師會習慣於呼叫易讀的程式碼，當你在WHERE 子句中存在多個條件的時候尤為重要，任何人讀到這個子句的時候都可以確切地知道表是否已經被正確地歸併了，並且也可以確切地知道條件的次序。這對我們DBA來說非常重要，可以快速有效的檢測SQL語句的效能。

 

試著讀下邊的語句：

 

SQL>　SELECT EMPLOYEE_TBL.EMPLOYEE_ID， EMPLOYEE_TBL.NAME，

EMPLOYEE_PAY_TBL.SALARY， EMPLOYEE_PAY_TBL.HIRE_DATE

FROM EMPLOYEE_TBL EMPLOYEE_PAY_TBL

WHERE EMPLOYEE_TBL.EMPLOYEE_ID=EMPLOYEE_PAY_TBL.EMPLOYEE_ID

AND EMPLOYEE_PAY_TBL.SALARY>30000 OR (EMPLOYEE_PAY_TBL.SALARY

BETWEEN 25000 AND 30000 AND EMPLOYEE_PAY_TBL.HIRE_DATE < SYSDATE -

365)

 

下邊是相同的查詢的更易讀的寫法

 

SQL> SELECT E.EMPLOYEE_ID, E.NAME, P.SALARY, P.HIRE_DATE

2 FROM EMPLOYEE_TBL E,

3 EMPLOYEE_PAY_TBL P

4 WHERE E.EMPLOYEE_ID = P.EMPLOYEE_ID

5 AND P.SALARY > 30000

6 OR (P.SALARY BETWEEN 25000 AND 30000

7 AND P.HIRE_DATE < SYSDATE - 365)

 

注：注意在上邊的查詢中使用了表的別名，在第2 行語句中EMPLOYEE_TBL 被賦予了簡單的別名E，在第3 行中EMPLOYEE_PAY 被賦予了別名P 。你可以看到在第4 5 6 行中E 和P 已經取代了表的名字，別名需要輸入的字元量比輸入全名時要少許多，更重要的是，使用別名以後查詢變得更有組織和易讀性，而使用表的命名則引出不必要的混亂。這兩個查詢是一樣的，但是第二個顯然是更易讀的它非常具有結構性那就是查詢的各個成份被回車合理地分開了，你可以很容易地找到哪裡是查詢的SELECT 部分（在SELECT 子句）中和都有哪些表被訪問在FROM 子句中哪些是需要指定的條件（在WHERE 子句中）。

當使用表的別名時，請把別名字首於每個Column上。這樣可以減少解析的時間並減少那些由Column歧義引起的語法錯誤。

2.2　在程式中的SQL應省略資料庫使用者的名稱

在Java、JSP、C/C++或其他程式語言中，我們不應在表名稱前加使用者名稱稱字首，這樣將會使得資料庫物件和程式移植時變得困難，即使該表不是屬於所連線的使用者，我們可以在資料庫內部透過同義詞SYNONYM來實現資料庫表的位置的透明性，所以下面的SQL

 

SELECT a.emp_name, a.email, b.dept_name

  FROM scott.emp a, iv.dept b

 WHERE a.dept_id = b.dept_id

 

應改寫為

 

SELECT a.emp_name, a.email, b.dept_name

  FROM emp a, dept_iv b

 WHERE a.dept_id = b.dept_id

 

其中dept_iv為同義詞的名字，指向iv使用者的dept表。

2.3 儘量避免全表掃描

當資料庫服務為執行某一個SQL語句需要對錶中的每一個記錄進行檢查時就會發生全表掃描，它通常在執行SELECT 語句時發生，但有時也會在更新和刪除記錄時發生，全表掃描通常是因為在WHERE 子句中使用了索引中沒有的欄位時發生，它就像對一本書一頁一頁地來看以找到所需內容一樣，在大多數情況下，我們使用索引。

我們通常透過對經常在WHERE子句中使用的欄位建立索引來避免全表掃描索引，所提供的找資料的方法與透過目錄找書中的指定內容方法一樣，使用索引可以提高資料的訪問速度。

儘管程式設計師們並不贊成使用全表掃描，但是有時使用它也是適當的，例如：

 

l         你選擇了一個表中的大多數行的時候

l         你在對錶中的每一行記錄進行更新的時候

l         表非常小的時候

 

對於頭兩種情況索引的效率是非常低的，因為資料庫服務程式不得不頻繁地讀表和索引內容，也就是說索引只有在你所要找的資料只在表中所佔比率很小的時候才會非常地有效，通常不會超過表中全部資料量的10%到15%。

此外最好在大型表中使用索引，當你設計表和索引的時候你要考慮表的大小，合適的索引應該是建立在對資料的熟悉上，知道那一列資料是最經常引用的。如果想讓索引工作得好，你需要做一些試驗。

注:當說到大表的時候這裡的大是相對而言的，一個表比某個表相比可以說很大而它與另一個表相比時卻又很小，表的大小的概念是與資料庫中其它表的大小，可用的磁碟空間，可用的磁碟的數量以及類似的因素相關的。很明顯：2GB 的表很大，而16KB的表是小的。如果一個資料庫中表的平均大小是100MB，那麼一個500MB 的表就是大的。

2.4 使用CASE語句

    我們經常需要在同一個表（或同一個表集）上進行不同的統計，通常的做法需要對同一個表作多次掃描操作，但是在Oracle中提供了一個簡便的方法，使用CASE語句，使得只需要對錶作一次掃描就可以得到多個統計結果，從而極大地提高了效能。

下面我們用一個例子來說明。如果我們分別需要知道月薪少於2000，月薪在2000到4000之間以及月薪大於4000的僱員人數，通常的做法是用三個查詢

SELECT COUNT (*)

FROM employees

WHERE salary < 2000;

 

SELECT COUNT (*)

FROM employees

WHERE salary BETWEEN 2000 AND 4000;

 

SELECT COUNT (*)

FROM employees

WHERE salary>4000;

 

這樣就需要對錶作三次掃描，而使用如下的SQL語句則只需對錶作一次掃描就可以得到所有資料。

SELECT COUNT (CASE WHEN salary < 2000

                   THEN 1 ELSE null END) count1,

       　COUNT (CASE WHEN salary BETWEEN 2001 AND 4000

                   THEN 1 ELSE null END) count2,

       　COUNT (CASE WHEN salary > 4000

                   THEN 1 ELSE null END) count3

  FROM employees;

 

2.5 加入一個新的索引

你經常會發現一些SQL 語句執行的時間長得不合情理，儘管其它的語句執行的效能看起來是可以接受的,例如當資料的檢索條件改變或表的結構改變以後。

當我們加入一個WINDOWS 的應用前端時我們也會發現速度的下降，對於這種情況你首先要檢查的是所用的目標表是否存在索引。然後大多數情況下我們會發現表是有索引的，但是在WHERE 子句中所使用的新條件沒有索引，看一下SQL 語句中的WHERE 子句，我們要問的是：“是否可以加入其它的索引”。如果是在下列條件下，那麼答案是肯定的。

 

l         最大的限制條件返回資料少於表總資料量的10%

l         最大的限制條件在SQL語句中是經常使用的

l         條件列的查詢將會返回一個唯一的值

l         列經常被ORDER BY 或GROUP BY 子句所引用

 

也可以使用複合索引，複合索引是基於表中兩個或更多列的索引，如果在SQL 語句中經常將兩列一起使用時這種索引會比單列索引更有效，如果在一起的索引列經常是分開使用的，特別是在其它的查詢中，那麼單列索引則是更合適的，所以你要經過試驗來判斷在你的資料庫中使用哪一種索引會是更合適的。

2.6 在查詢中各個元素的佈局

在你的查詢中最好的元素佈局，尤其是在WHERE 子句中，是根據直譯器處理SQL 語句的步驟和次序而定的，在條件中安排被索引過的列，這樣的條件將會查詢最少的記錄。

你不一定非要在WHERE 子句中使用已經被索引過的列，但是顯然這樣做會更有效。試著調整SQL語句以使它返回的記錄數最少。在一個表中返回記錄數最少的條件就是最大的限制條件。在通常的語句中。你應該把最大的條件限制語句放在WHERE 子句的最後（ORACLE 查詢最佳化會對WHERE 子句從後向前讀，所以它會最先處理我們放置的條件語句）。

當最佳化器首先讀到最大條件限制語句以後，它就將為以後的條件所提供的結果集縮減至最小了，下一個條件將不再搜尋整個表，而是搜尋經過最大條件限制過的子集，所以資料的返回就會更快。在複雜查詢中的多個查詢，子查詢，計算以及使用邏輯條件（AND/OR/NOT）中最大限制條件可能並不清晰。

下邊的測試是我們對用兩種不同的方法來查詢相同的內容所耗用時間的差異，該例子使用ORACLE 9關聯式資料庫系統切記在直譯器中的最佳化是從後向前進行的在創造一個SELECT語句之前，對於每一個條件我們都選擇了獨立的行，下邊是不同的條件給出的數值。

 

Condition                   Distinct Values

calc_ytd = '-2109490.8'     13,000 +

dt_stmp = '01-SEP-96'       15

output_cd = '001'           13

activity_cd = 'IN'          10

status_cd = 'A'             4

function_cd = '060'         6

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/41224/viewspace-780380/，如需轉載，請註明出處，否則將追究法律責任。