【DB筆試面試570】在Oracle中，SQL優化在寫法上有哪些常用的方法？

• 2019 年 10 月 10 日
• 筆記

♣

題目部分

在Oracle中，SQL優化在寫法上有哪些常用的方法？

♣

答案部分

一般在書寫SQL時需要注意哪些問題，如何書寫可以提高查詢的效率呢？可以從以下幾個方面去考慮：

（1）減少對資料庫的訪問次數。

當執行每條SQL語句時，Oracle在內部執行了許多工作：解析SQL語句，估算索引的利用率，綁定變數，讀數據塊等等。由此可見，減少訪問資料庫的次數，就能實際上減少Oracle的工作量。充分利用表索引，避免進行全表掃描；充分利用共享快取機制，提高SQL工作效率；充分利用結構化編程方式，提高查詢的復用能力。常用的方法為把對資料庫的操作寫成存儲過程，然後應用程式通過調用存儲過程，而不是直接使用SQL。

（2）減少對大表的掃描次數。可以利用WITH對SQL中多次掃描的表來進行修改。採用各種手段來避免全表掃描。

（3）SELECT子句中避免使用「*」，應該寫出需要查詢的欄位。

當想在SELECT子句中列出所有的列時，可以使用「*」來返回所有的列，但這是一個非常低效的方法。實際上，Oracle在解析的過程中，會將「*」依次轉換成所有的列名，這個工作是通過查詢數據字典完成的，這意味著將耗費更多的時間。不需要的欄位盡量少查，多查的欄位可能有行遷移或行鏈接（timesten還有行外存儲問題）。少查LOB類型的欄位可以減少I/O。

（4）盡量使用表的別名（ALIAS）。

當在SQL語句中連接多個表時，請使用表的別名，並把別名前綴於每個列上。此時就可以減少解析的時間並減少那些由列歧義引起的語法錯誤。

（5）對於數據量較少、又有主鍵索引的情況，可以考慮將關聯子查詢或外連接的SQL修改為標量子查詢。

（6）避免隱式類型轉換（Implicit Type Conversion）。如果進行比較的兩個值的數據類型不同，那麼Oracle必須將其中一個值進行類型轉換使其能夠比較。這就是所謂的隱式類型轉換。通常當開發人員將數字存儲在字元列時會導致這種問題的產生。Oracle在運行時會在索引字元列使用TO_NUMBER函數強制轉化字元類型為數值類型。由於添加函數到索引列所以導致索引不被使用。實際上，Oracle也只能這麼做，類型轉換是一個應用程式設計因素。由於轉換是在每行都進行的，這會導致性能問題。一般情況下，當比較不同數據類型的數據時，Oracle自動地從複雜向簡單的數據類型轉換，該規則和MySQL中的隱式類型轉換是一致的。所以，字元類型的欄位值應該加上引號。例如，假設USER_NO是一個字元類型的索引列，則：

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO = 109204421;    --這個語句在執行的時候被Oracle在內部自動的轉換為：  SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE TO_NUMBER(USER_NO) = 109204421;    --因為內部發生的類型轉換，這個索引將不會被使用，所以正確的寫法應該是：  SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO = '109204421';    --但是，在下面的SQL語句中，Oracle隱式地將字元串「03-MAR-97」轉化為默認日期類型為「DD-MON-YY」的日期：  SELECT LAST_NAME FROM EMPLOYEES WHERE HIRE_DATE = '03-MAR-97';    Execution Plan  ----------------------------------------------------------  Plan hash value: 1445457117    -------------------------------------------------------------------------------  | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |           |     1 |    69 |     3   (0)| 00:00:01 |  |*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    69 |     3   (0)| 00:00:01 |  -------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       1 - filter("HIRE_DATE"='24-APR-06')  

（7）避免使用耗費資源的操作，包括DISTINCT、UNION、MINUS、INTERSECT、ORDER BY、GROUP BY等。能用DISTINCT的就不用GROUP BY。能用UNION ALL就不要用UNION。

（8）用TRUNCATE替代DELETE。若要刪除表中所有的數據，則可以用TRUNCATE替代DELETE。

（9）根據查詢條件建立合適的索引，利用索引可以避免大表全表掃描（FULL TABLE SCAN）。

（10）合理使用臨時表。

（11）避免寫過於複雜的SQL，不一定非要一個SQL解決問題。將一個大的SQL改寫為多個小的SQL來實現功能。條件允許的情況下可以使用批處理來完成。

（12）在不影響業務的前提下盡量減小事務的粒度。

（13）當使用基於規則的優化器（RBO）時，在多表連接查詢的時候，記錄數少的表應該放在右邊。

（14）避免使用複雜的集合函數，像NOT IN等。通常，要避免在索引列上使用NOT，NOT會產生和在索引列上使用函數相同的影響。當Oracle遇到NOT操作符時，它就會停止使用索引轉而執行全表掃描。很多時候用EXISTS和NOT EXISTS代替IN和NOT IN語句是一個好的選擇。需要注意的是，在Oracle 11g之前，若NOT IN的列沒有指定非空的話（注意：是主表和子表的列未同時有NOT NULL約束，或都未加IS NOT NULL限制），則NOT IN選擇的是filter操作（如果指定了非空，那麼會選擇ANTI的反連接），但是從Oracle 11g開始有新的ANTI NA（NULL AWARE）優化，可以對子查詢進行UNNEST，NOT IN和NOT EXISTS都選擇的是ANTI的反連接，所以效率是一樣的。在一般情況下，ANTI的反連接演算法比filter更高效。對於未UNNEST的子查詢，若選擇了filter操作，則至少有兩個子節點，執行計劃還有個特點就是Predicate謂詞部分有「:B1」這種類似綁定變數的內容，內部操作走類似Nested Loops操作。如果在Oracle 11g之前，遇到NOT IN無法UNNEST，那麼可以將NOT IN部分的匹配條件均設為NOT NULL約束。若不添加NOT NULL約束，則需要兩個條件均增加IS NOT NULL條件。當然也可以將NOT IN修改為NOT EXISTS。關於反連接的更多內容參考【3.2.5.10 什麼是半連接、反連接和星型連接？】。

分別在Oracle 10g和Oracle 11g實驗：

SELECT * FROM V$VERSION;  DROP TABLE EMP PURGE;  DROP TABLE DEPT PURGE;  CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;  CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;  SET TIMING ON  SET LINESIZE 1000  SET AUTOTRACE TRACEONLY  --寫法1  SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);  --寫法2  SELECT * FROM DEPT WHERE NOT EXISTS (SELECT DEPTNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO);  --寫法3  SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL) AND DEPTNO IS NOT NULL;  --寫法4  SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP) AND DEPTNO IS NOT NULL;  --寫法5  SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP WHERE DEPTNO IS NOT NULL);

看一下詳細執行計劃：

SELECT * FROM V$VERSION;  DROP TABLE EMP PURGE;  DROP TABLE DEPT PURGE;  CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;  CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;  SET TIMING ON  SET LINESIZE 1000  SET AUTOTRACE TRACEONLY  --寫法1  SELECT /*+optimizer_features_enable('10.2.0.5')*/ * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);  SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);    LHR@orclasm > SELECT /*+optimizer_features_enable('10.2.0.5')*/ * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);        DEPTNO DNAME          LOC  ---------- -------------- -------------          40 OPERATIONS     BOSTON      Execution Plan  ----------------------------------------------------------  Plan hash value: 3547749009    ---------------------------------------------------------------------------  | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  ---------------------------------------------------------------------------  |   0 | SELECT STATEMENT   |      |     1 |    30 |     5   (0)| 00:00:01 |  |*  1 |  FILTER            |      |       |       |            |          |  |   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |  |*  3 |   TABLE ACCESS FULL| EMP  |    13 |   169 |     2   (0)| 00:00:01 |  ---------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       1 - filter( NOT EXISTS (SELECT 0 FROM "EMP" "EMP" WHERE                LNNVL("DEPTNO"<>:B1)))     3 - filter(LNNVL("DEPTNO"<>:B1))    Note  -----     - dynamic sampling used for this statement (level=2)      Statistics  ----------------------------------------------------------           15  recursive calls            0  db block gets           31  consistent gets            0  physical reads            0  redo size          674  bytes sent via SQL*Net to client          519  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed    LHR@orclasm > SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);        DEPTNO DNAME          LOC  ---------- -------------- -------------          40 OPERATIONS     BOSTON      Execution Plan  ----------------------------------------------------------  Plan hash value: 2100826622    ---------------------------------------------------------------------------  | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  ---------------------------------------------------------------------------  |   0 | SELECT STATEMENT   |      |     4 |   172 |     7  (15)| 00:00:01 |  |*  1 |  HASH JOIN ANTI NA |      |     4 |   172 |     7  (15)| 00:00:01 |  |   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |  |   3 |   TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |  ---------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       1 - access("DEPTNO"="DEPTNO")    Note  -----     - dynamic sampling used for this statement (level=2)      Statistics  ----------------------------------------------------------            7  recursive calls            0  db block gets           14  consistent gets            0  physical reads            0  redo size          674  bytes sent via SQL*Net to client          519  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed  

針對上面的NOT IN子查詢，如果子查詢中的DEPTNO有NULL存在，那麼整個查詢都不會有結果，在Oracle 11g之前，如果主表和子表的DEPTNO未同時有NOT NULL約束，或都未加IS NOT NULL限制，那麼Oracle會選擇filter。從Oracle 11g開始有新的ANTI NA（NULL AWARE）優化，可以對子查詢進行UNNEST，從而提高效率。對於未UNNEST的子查詢，若選擇了FILTER操作，則至少有兩個子節點，執行計劃還有個特點就是Predicate謂詞部分有「:B1」這種類似綁定變數的內容，內部操作走類似Nested Loops操作。

如下所示：

LHR@orclasm > SELECT  /*+rule gather_plan_statistics*/  *    2    FROM SCOTT.EMP    3   WHERE NOT EXISTS (SELECT 0    4            FROM SCOTT.DEPT    5           WHERE DEPT.DNAME = 'SALES'    6             AND DEPT.DEPTNO = EMP.DEPTNO)    7     AND NOT EXISTS    8   (SELECT 0 FROM SCOTT.BONUS WHERE BONUS.ENAME = EMP.ENAME);         EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO  ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------        7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20        7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20        7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10        7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20        7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10        7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20        7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20        7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10    8 rows selected.    LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));      PLAN_TABLE_OUTPUT  ------------------------------------------------------------------------------------------  SQL_ID  b8w1s38hqtjkj, child number 0  -------------------------------------  SELECT  /*+rule gather_plan_statistics*/  *   FROM SCOTT.EMP  WHERE NOT  EXISTS (SELECT 0           FROM SCOTT.DEPT          WHERE DEPT.DNAME =  'SALES'            AND DEPT.DEPTNO = EMP.DEPTNO)    AND NOT EXISTS  (SELECT 0 FROM SCOTT.BONUS WHERE BONUS.ENAME = EMP.ENAME)    Plan hash value: 1445856646    -----------------------------------------------------------------------------------------  | Id  | Operation                    | Name    | Starts | A-Rows |   A-Time   | Buffers |  -----------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT             |         |      1 |      8 |00:00:00.01 |      14 |  |*  1 |  FILTER                      |         |      1 |      8 |00:00:00.01 |      14 |  |   2 |   TABLE ACCESS FULL          | EMP     |      1 |     14 |00:00:00.01 |       8 |  |*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |00:00:00.01 |       6 |  |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      3 |      3 |00:00:00.01 |       3 |  |*  5 |   TABLE ACCESS FULL          | BONUS   |      8 |      0 |00:00:00.01 |       0 |  -----------------------------------------------------------------------------------------    Query Block Name / Object Alias (identified by operation id):  -------------------------------------------------------------       1 - SEL$1     2 - SEL$1 / EMP@SEL$1     3 - SEL$2 / DEPT@SEL$2     4 - SEL$2 / DEPT@SEL$2     5 - SEL$3 / BONUS@SEL$3    Outline Data  -------------      /*+        BEGIN_OUTLINE_DATA        IGNORE_OPTIM_EMBEDDED_HINTS        OPTIMIZER_FEATURES_ENABLE('11.2.0.3')        DB_VERSION('11.2.0.3')        RBO_OUTLINE        OUTLINE_LEAF(@"SEL$2")        OUTLINE_LEAF(@"SEL$3")        OUTLINE_LEAF(@"SEL$1")        FULL(@"SEL$1" "EMP"@"SEL$1")        FULL(@"SEL$3" "BONUS"@"SEL$3")        INDEX_RS_ASC(@"SEL$2" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))        END_OUTLINE_DATA    */    Predicate Information (identified by operation id):  ---------------------------------------------------       1 - filter(( IS NULL AND  IS NULL))     3 - filter("DEPT"."DNAME"='SALES')     4 - access("DEPT"."DEPTNO"=:B1)     5 - filter("BONUS"."ENAME"=:B1)    Column Projection Information (identified by operation id):  -----------------------------------------------------------       1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],         "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],         "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]     2 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],         "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],         "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]     4 - "DEPT".ROWID[ROWID,10]    Note  -----     - rule based optimizer used (consider using cbo)      70 rows selected.      -----------------------------------------------------------------------------------------  | Id  | Operation                    | Name    | Starts | A-Rows |   A-Time   | Buffers |  -----------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT             |         |      1 |      8 |00:00:00.01 |      14 |  |*  1 |  FILTER                      |         |      1 |      8 |00:00:00.01 |      14 |  |   2 |   TABLE ACCESS FULL          | EMP     |      1 |     14 |00:00:00.01 |       8 |  |*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |00:00:00.01 |       6 |  |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      3 |      3 |00:00:00.01 |       3 |  |*  5 |   TABLE ACCESS FULL          | BONUS   |      8 |      0 |00:00:00.01 |       0 |  -----------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------  1 - filter(( IS NULL AND  IS NULL))  3 - filter("DEPT"."DNAME"='SALES')  4 - access("DEPT"."DEPTNO"=:B1)  5 - filter("BONUS"."ENAME"=:B1)  

該執行計劃的執行順序為：

①　ID1有3個子節點ID2、ID3、ID5，由於ID2最小，故先執行ID2；

②　ID2對EMP表進行全表掃描，將返回14行給ID1；

③　在相關組合中ID2應當控制ID3和ID5的執行，由於Oracle此處對Distinct Value做了優化，所以ID3隻執行了3次。

④　ID4執行3次，並返回3個RWOID到ID3；

⑤　ID3使用ID4返回的3個ROWID來訪問數據表塊，過濾「filter("DEPT"."DNAME"='SALES')」的數據，由於是NOT EXISTS，所以這導致ID1原來獲得的14行排除6行的「"DEPT"."DNAME"='SALES'」，只剩下8行，這8行數據影響了ID5的執行次數，將執行8次，其中「filter("BONUS"."ENAME"=:B1)」過濾條件的「:B1」由ID1的8行數據提供，ID5沒有返回數據，所以那8行沒有減少ID1將8行徹底過濾的數據返回給客戶端。

（15）盡量避免使用UNION關鍵詞，可以根據情況修改為UNION ALL。

（16）在Oracle資料庫里，IN和OR是等價的，優化器在處理帶IN的目標SQL時會將其轉換為帶OR的等價SQL。例如，「DEPTNO IN (10,20)」和「DEPTNO=10 OR DEPTNO=20」是等價的。

（17）選擇合適的謂詞進行過濾。

（18）避免使用前置通配符（%）。在WHERE子句中，如果索引列所對應的值的第一個字元由通配符（WILDCARD）開始，索引將不被採用。在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當通配符出現在字元串其它位置時，優化器就能利用索引。若前置通配符實在無法取消，則可以從2個方面去考慮。①去重和去空。應該把表中的重複記錄或者為空的記錄全部去掉，這樣可以大大減少結果集，因而提升性能，這裡也體現了大表變小表的思想；②考慮建立文本索引。③做相關的轉換，請參考【3.2.5.3 模糊查詢可以使用索引嗎？】。

（19）應盡量避免在WHERE子句中對索引欄位進行函數、算術運算或其他表達式等操作，因為這樣可能會使索引失效，查詢時要儘可能將操作移至等號右邊。見如下例子：

SELECT * FROM T1 WHERE SUBSTR(NAME,2,1)='L';

在以上SQL中，即使NAME欄位建有唯一索引，該SQL語句也無法利用索引進行檢索數據，而是走全表掃描的方式。一些常見的改寫如下表所示：

需要注意的是，如果SELECT需要檢索的欄位只包含索引列且WHERE查詢中的索引列含有非空約束的時候，以上規則並不適用。例如，SQL語句「SELECT CREATED FROM T1 WHERE TRUNC(CREATED)=TRUNC(SYSDATE);」，若CREATED列上有非空約束或在WHERE子句中加上「CREATED IS NOT NULL」，則該SQL語句仍然會走索引，如下所示：

DROP TABLE T  PURGE;  CREATE TABLE T  NOLOGGING AS SELECT *  FROM    DBA_OBJECTS D ;  CREATE   INDEX IND_OBJECTNAME ON  T(OBJECT_NAME);    SELECT T.OBJECT_NAME FROM T WHERE T.OBJECT_NAME ='T';   --走索引  SELECT T.OBJECT_NAME FROM T WHERE UPPER(T.OBJECT_NAME) ='T';     --不走索引  SELECT T.OBJECT_NAME FROM T WHERE UPPER(T.OBJECT_NAME) ='T' AND T.OBJECT_NAME IS NOT NULL ;    --走索引(INDEX FAST FULL SCAN)  SELECT T.OBJECT_NAME FROM T WHERE UPPER(T.OBJECT_NAME) ||'AAA' ='T'||'AAA' AND T.OBJECT_NAME IS NOT NULL ;     --走索引(INDEX FAST FULL SCAN)  SELECT T.OBJECT_NAME,T.OWNER FROM T WHERE UPPER(T.OBJECT_NAME) ||'AAA' ='T'||'AAA' AND T.OBJECT_NAME IS NOT NULL ;     --不走索引  

（20）合理使用分析函數。

（21）應盡量避免在WHERE子句中使用不等操作符（!=或<>），否則引擎將放棄使用索引而進行全表掃描。

（22）避免不必要和無意義的排序。

（23）儘可能減少關聯表的數量，關聯表盡量不要超過3張。

（24）在建立複合索引時，盡量把最常用、重複率低的欄位放在最前面。在查詢的時候，WHERE條件盡量要包含索引的第一列即前導列。

（25）應盡量避免在WHERE子句中對欄位進行IS NULL值判斷，否則將導致引擎放棄使用索引而進行全表掃描。可以通過加偽列創建偽聯合索引來使得IS NULL使用索引。例如語句：「SELECT ID FROM T WHERE NUM IS NULL;」可以在NUM上設置默認值0，確保表中NUM列沒有NULL值，然後這樣查詢：「SELECT ID FROM T WHERE NUM=0;」。

（26）IN要慎用，因為IN會使系統無法使用索引，而只能直接搜索表中的數據。如：

SELECT ID FROM T WHERE NUM IN (1,2,3);  對於連續的數值，能用BETWEEN就不要用IN了：  SELECT ID FROM T WHERE NUM BETWEEN 1 AND 3;  

（27）必要時使用Hint強制查詢優化器使用某個索引，如在WHERE子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變數，但優化程式不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。

（28）在條件允許的情況下，只訪問索引，從而可以避免索引回表讀（TABLE ACCESS BY INDEX ROWID，通過索引再去讀表中的內容）。當索引中包括處理查詢所需要的所有數據時，可以執行只掃描索引操作，而不用做索引回表讀操作。因為索引回表讀開銷很大，能避免則避免。避免的方法就是，①根據業務需求只留下索引欄位；②建立聯合索引。這裡的第二點需要注意平衡，如果聯合索引的聯合列太多，必然導致索引過大，雖然消減了回表動作，但是索引塊變多，在索引中的查詢可能就要遍歷更多的BLOCK了，所以需要全面考慮，聯合索引列不宜過多，一般來說超過3個欄位組成的聯合索引都是不合適的，需要權衡利弊。

（29）選擇合適的索引。Oracle在進行一次查詢時，一般對一個表只會使用一個索引。例如，某表有索引1（POLICYNO）和索引2（CLASSCODE），如果查詢條件為POLICYNO ='XX' AND CLASSCODE ='XX'，那麼系統有可能會使用索引2，相較於使用索引1，查詢效率明顯降低。

（30）優先且儘可能使用分區索引。

（31）在刪除（DELETE）、插入（INSERT）、更新（UPDATE）頻繁的表中，建議不要使用點陣圖索引。

（32）對於分區表，應該減少需要掃描的分區，避免全分區掃描。對於單分區掃描，在分區表後加上PARTITION(分區名)；對於多分區掃描，使用分區關鍵字來限制需要掃描的範圍，從而可以避免全分區掃描。

（33）使用分批處理、DBMS_PARALLEL_EXECUTE進行處理。

（34）刪除重複記錄盡量採用ROWID的方法，如下所示：

DELETE FROM SCOTT.EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM SCOTT.EMP X WHERE X.EMPNO = E.EMPNO);

（35）SQL中慎用自定義函數。如果自定義函數的內容，只是針對函數輸入參數的運算，而沒有訪問表這樣的程式碼，那麼這樣的自定義函數在SQL中直接使用是高效的；否則，如果函數中含有對錶的訪問的語句，那麼在SQL中調用該函數很可能會造成很大的性能問題，需要謹慎！在這種情況下，往往將函數中訪問表的程式碼取出和調用它的SQL整合成新的SQL。

（36）使用DECODE函數可以避免重複掃描相同記錄或重複連接相同的表，這對於大表非常有效，如下所示：

SELECT COUNT(*), SUM(SAL) FROM SCOTT.EMP WHERE DEPTNO = 20 AND ENAME LIKE 'SMITH%';  SELECT COUNT(*), SUM(SAL) FROM SCOTT.EMP WHERE DEPTNO = 30 AND ENAME LIKE 'SMITH%';    --若使用DECODE函數則對SCOTT.EMP表只訪問一次，如下所示：  SELECT COUNT(DECODE(DEPTNO, 20, '1', NULL)) D20_COUNT,  COUNT(DECODE(DEPTNO, 30, '1', NULL)) D30_COUNT,         SUM(DECODE(DEPTNO, 20, SAL, NULL)) D20_SAL, SUM(DECODE(DEPTNO, 30, SAL, NULL)) D30_SAL    FROM SCOTT.EMP   WHERE ENAME LIKE 'SMITH%';  

類似的，DECODE函數也可以運用於GROUP BY和ORDER BY子句中。

（37）在計算表的行數時，若表上有主鍵，則盡量使用COUNT(*)或COUNT(1)。

（38）用WHERE子句替換HAVING子句。避免使用HAVING子句，因為HAVING只會在檢索出所有記錄之後才對結果集進行過濾。這個處理需要排序、總計等操作。如果能通過WHERE子句限制記錄的數目，那麼就能提高SQL的性能。如下所示：

--低效：  SELECT T.EMPNO, COUNT(*) FROM SCOTT.EMP T GROUP BY T.EMPNO HAVING EMPNO = 7369;    --高效：  SELECT T.EMPNO, COUNT(*) FROM SCOTT.EMP T WHERE EMPNO = 7369 GROUP BY T.EMPNO ;  

（39）減少對錶的查詢，尤其是要避免在同一個SQL中多次訪問同一張大表。可以考慮如下的改寫方法：

①　先根據條件提取數據到臨時表中，然後再做連接，即利用WITH進行改寫。

②　有的相似的語句可以用MAX+DECODE函數來處理。

③　在含有子查詢的SQL語句中，要特別注意減少對錶的查詢，例如形如「UPDATE AAA T SET T.A=(….) T.B=(….) WHERE ….;」該更新的SQL語句中小括弧中的大表都是一樣的，且查詢非常相似，這個時候可以修改為：「UPDATE AAA T SET (T.A,T.B)=(…..) WHERE ….;」。

（40）SQL語句統一使用大寫。因為Oracle總是先解析SQL語句，把小寫的字母轉換成大寫的再執行。

（41）對於一些固定性的小的查詢結果集或統計性的SQL語句（例如，SQL語句非常複雜，但是最終返回的結果集很簡單，只包含少數的幾行數據）可以使用結果集快取（Result Cache）。對於一些常用的小表可以使用保留池（Keep Pool）。

（42）如果在一條SQL語句中同時取最大值和最小值，那麼需要注意寫法上的差異：

SELECT MAX(OBJECT_ID),MIN(OBJECT_ID) FROM T; --效率差，選擇INDEX FAST FULL SCAN  SELECT MAX_VALUE, MIN_VALUE FROM (SELECT MAX(OBJECT_ID) MAX_VALUE FROM T) A, (SELECT MIN(OBJECT_ID) MIN_VALUE FROM T) B;--效率高，選擇INDEX FULL SCAN (MIN/MAX)  

示例如下所示：

準備環境：

DROP TABLE T_20170704_LHR_01 PURGE;  CREATE TABLE T_20170704_LHR_01 AS SELECT * FROM DBA_OBJECTS;  UPDATE T_20170704_LHR_01 SET OBJECT_ID=ROWNUM;  COMMIT;  ALTER TABLE T_20170704_LHR_01 ADD CONSTRAINT PK_20170704_OBJECT_ID PRIMARY KEY (OBJECT_ID);  

普通寫法：

LHR@orclasm > SET AUTOTRACE ON  LHR@orclasm > SET LINESIZE 1000  LHR@orclasm > SELECT MAX(OBJECT_ID),MIN(OBJECT_ID) FROM T_20170704_LHR_01;    MAX(OBJECT_ID) MIN(OBJECT_ID)  -------------- --------------           79298              1      Execution Plan  ----------------------------------------------------------  Plan hash value: 2419726051    -----------------------------------------------------------------------------------------------  | Id  | Operation             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |  -----------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT      |                       |     1 |    13 |    51   (2)| 00:00:01 |  |   1 |  SORT AGGREGATE       |                       |     1 |    13 |            |          |  |   2 |   INDEX FAST FULL SCAN| PK_20170704_OBJECT_ID | 76600 |   972K|    51   (2)| 00:00:01 |  -----------------------------------------------------------------------------------------------    Note  -----     - dynamic sampling used for this statement (level=2)      Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets          172  consistent gets            0  physical reads            0  redo size          613  bytes sent via SQL*Net to client          519  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed  

優化後的寫法：

LHR@orclasm > SELECT MAX_VALUE, MIN_VALUE FROM (SELECT MAX(OBJECT_ID) MAX_VALUE FROM T_20170704_LHR_01) A, (SELECT MIN(OBJECT_ID) MIN_VALUE FROM T_20170704_LHR_01) B;     MAX_VALUE  MIN_VALUE  ---------- ----------       79298          1      Execution Plan  ----------------------------------------------------------  Plan hash value: 3965153161    ------------------------------------------------------------------------------------------------------  | Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |  ------------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT             |                       |     1 |    26 |     4   (0)| 00:00:01 |  |   1 |  NESTED LOOPS                |                       |     1 |    26 |     4   (0)| 00:00:01 |  |   2 |   VIEW                       |                       |     1 |    13 |     2   (0)| 00:00:01 |  |   3 |    SORT AGGREGATE            |                       |     1 |    13 |            |          |  |   4 |     INDEX FULL SCAN (MIN/MAX)| PK_20170704_OBJECT_ID |     1 |    13 |     2   (0)| 00:00:01 |  |   5 |   VIEW                       |                       |     1 |    13 |     2   (0)| 00:00:01 |  |   6 |    SORT AGGREGATE            |                       |     1 |    13 |            |          |  |   7 |     INDEX FULL SCAN (MIN/MAX)| PK_20170704_OBJECT_ID |     1 |    13 |     2   (0)| 00:00:01 |  ------------------------------------------------------------------------------------------------------    Note  -----     - dynamic sampling used for this statement (level=2)      Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets            4  consistent gets            0  physical reads            0  redo size          603  bytes sent via SQL*Net to client          519  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed  

無論是從cost還是邏輯讀方面，差異都是非常大的，因為優化後的SQL選擇的是「INDEX FULL SCAN (MIN/MAX)」，性能大幅度提升。

（43）在PL/SQL中，在定義變數類型時盡量使用%TYPE和%ROWTYPE，這樣可以減少程式碼的修改，增加程式的可維護性。

以上講解的每點優化內容希望讀者可以通過實驗來加深理解。

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。