譯者注：解決資料庫級（SQL）工作上的問題，應該採用的是SET方法（整體的）而不是過程式的方法。下面來看看作者為什麼這麼說。
編寫有效的SQL查詢是企業軟體世界中最大的難題之一。
每個公司在資料庫開發專案中所面臨的最根本的問題，在於開發環境中實現的效能不能在生產環境中實現。一般來說，存在效能損失是因為生產環境中的資料量要大得多。
這些問題（執行緩慢的資料庫操作）可能有各種各樣的原因。本文將解釋如何在編寫查詢時進行思考，如何思考是最基本的問題，也是解決此類問題的起點。
觀察發現SQL開發人員常使用過程方法編寫查詢。事實上，這是很自然的，因為用程式方法解決問題是最方便的人類邏輯解決方案。另一個方面，幾乎所有的SQL開發人員都在同時編寫Java、c#或其他程式語言的程式碼。Java、C#等可以用來訓練開發人員以一種程式化的方式來培養他們的思維方式，因為當使用這些語言開發應用程式時，會使用很多類似的東西，比如IF .. THEN .. ELSE，FOR .. LOOP，WHILE .. DO， CASE .. WHEN。當然，在這種情況下，當將業務規則應用到一組資料時，意味著每個記錄都是單獨處理的(逐行處理)。這個過程方法在Java、c#等語言中使用。雖然使用語言開發軟體是一種正確的方法，但在編寫資料庫級(SQL)的查詢時，卻不會產生同樣的效果。
下面用兩種不同的方法來解決同一個示例問題，並將結果進行比較。看看CUSTOMERS表中對應的每個客戶在SALES表中有多少條記錄。
過程式方法如下:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SET AUTOTRACE ON SELECT c.cust_id, (SELECT COUNT (*) FROM sh.sales s WHERE s.cust_id = c.cust_id) sa_count FROM SH.CUSTOMERS c; Plan hash value: 881374884 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2454756 consistent gets 0 physical reads 0 redo size 925474 bytes sent via SQL*Net to client 41104 bytes received via SQL*Net from client 3701 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 55500 rows processed |
現在，採用基於SET的方法來編寫查詢。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SET AUTOTRACE ON SELECT c.cust_id, COUNT (s.cust_id) jh_count FROM SH.CUSTOMERS c, sh.sales s WHERE c.cust_id = s.cust_id(+) GROUP BY c.cust_id; Plan hash value: 716053480 Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 742 consistent gets 0 physical reads 0 redo size 925474 bytes sent via SQL*Net to client 41104 bytes received via SQL*Net from client 3701 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 55500 rows processed |
可以看到在兩個查詢的consistent gets數量之間的差異（當檢查緩衝區快取讀到的塊資料時）是巨大的。使用兩種不同方法編寫的查詢在執行時導致不同時間。這種差別可以用效能來解釋。
在另一個例子中，常見的習慣是在SQL語句中呼叫PL/SQL函式。作為過程式工作的例子，也是一種解決問題的方法。還有其他一些影響在SQL內呼叫PL/SQL程式碼效能的不利因素，但在本文中，不會提到效能問題。
下面編寫查詢客戶表中每個客戶的購買金額的程式碼。
過程方法:
在第一步中，建立一個PL/SQL函式來計算每個客戶的總數，然後在程式碼和輸出中呼叫這個函式。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
CREATE OR REPLACE FUNCTION get_grand_total ( p_cust_id_in IN SH.CUSTOMERS.CUST_ID%TYPE) RETURN NUMBER IS r_grand_total NUMBER; BEGIN SELECT SUM (amount_sold) INTO r_grand_total FROM sh.sales WHERE cust_id = p_cust_id_in; RETURN r_grand_total; END; SET AUTOTRACE ON SELECT cust_id, get_grand_total (cust_id) grand_total FROM sh.customers; Statistics ---------------------------------------------------------- 55503 recursive calls 0 db block gets 3066293 consistent gets 0 physical reads 0 redo size 890447 bytes sent via SQL*Net to client 41104 bytes received via SQL*Net from client 3701 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 55500 rows processed |
現在，採用基於SET的方法來編寫查詢。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SET AUTOTRACE ON SELECT c.cust_id, SUM (amount_sold) FROM SH.CUSTOMERS c, sh.sales s WHERE c.cust_id = s.cust_id(+) GROUP BY c.cust_id; Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1841 consistent gets 0 physical reads 0 redo size 890452 bytes sent via SQL*Net to client 41104 bytes received via SQL*Net from client 3701 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 55500 rows processed |
在本例中，通過檢視consistent GETS和遞迴呼叫輸出，我們可以看到相同的情況。
我們的查詢也是生成更高效的資料庫操作的第一步，它考慮的是批處理，而不是逐行思考。在進行資料庫操作時，批處理的方法會讓你在一天結束時消耗更少的資源，從而提高工作效率。