1. SQL 語句執行步驟

語法分析 > 語義分析 > 視圖轉換 > 表達式轉換 > 選擇優化器 > 選擇連接方法 > 選擇連接順序 > 選擇數據搜索路徑 > 運行“執行計劃”

2. 選擇合適的優化器 RULE（基于規則）、COST（基于成本）、（可選）

3. 訪問表的方法 全表掃描 全表掃描是按順序訪問表中的每條記錄，通過一次讀取多個數據塊來優化全表掃描。通過ROWID訪問表 ROWID包含了表中記錄的物理位置信息，并通過索引實現了數據與存儲數據的物理位置（ROWID）的連接。通常，索引提供了一種快速訪問ROWID的方法，因此那些基于索引列的查詢可以在性能上得到提升。

4. 共享 SQL 語句

5. 選擇最高效的表名序列 示例：表 TAB1 16,384 條記錄 表 TAB2 1 條記錄

/*選擇TAB2作為基礎表 (最好的方法)*/SELECT COUNT(*) FROM TAB1,TAB2/*執行時間0.96秒*/
/*選擇TAB1作為基礎表 (不佳的方法)*/SELECT COUNT(*) FROM TAB2,TAB1 /*執行時間26.09秒*/

如果要加入查詢的表超過3張，則需要選擇交叉表（表）作為基表，交叉表是指被其他表引用的表。

/*高效的SQL*/SELECT * FROM LOCATION L, CATEGORY C, EMP E WHERE?E.EMP_NO?BETWEEN?1000?AND?2000AND?E.CAT_NO?=?C.CAT_NOAND?E.LOCN?=?L.LOCN

會比下面的 SQL 更高效
/*低效的SQL*/SELECT * FROM EMP E, LOCATION L, CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND?E.EMP_NO?BETWEEN?1000?AND?2000



6. Where 子句中的連接順序以自下而上或從右到左的順序解析 WHERE 子句。根據這個原則，表之間的連接必須寫在其他 WHERE 條件之前，而那些可以過濾掉最大記錄數的條件必須寫在 WHERE 子句的末尾。
/*低效,執行時間156.3秒*/SELECT Column1,Column2FROM EMP EWHERE E.SAL > 50000AND E.JOB = 'MANAGER'AND 25 < (SELECT COUNT(*) FROM EMPWHERE MGR = E.EMPNO)

/*高效,執行時間10.6秒*/SELECT Column1,Column2FROM EMP EWHERE 25 < (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO)AND E.SAL > 50000AND E.JOB = 'MANAGER'

避免在 7. 子句中使用“*”
8. 減少數據庫訪問次數 在每條SQL語句執行的時候，內部都做了很多工作：解析SQL語句>估計索引的利用率>綁定變量>讀取數據塊等。可以是看到減少訪問數據庫的次數，實際上可以減少工作量。
9. 整個簡單的無關數據庫訪問如果有幾個簡單的數據庫查詢語句，可以將它們組合成一個查詢（即使它們之間沒有關系），以減少過多的數據庫IO開銷。這種方法雖然提高了效率，但是程序的可讀性大大降低，所以還是要權衡利弊。
10. 使用代替
11.盡可能多的使用盡可能多的在程序中使用，這樣可以提高程序的性能，由于資源的釋放而減少需求。發布的資源：
12. 統計記錄數
Select count(*) from tablename; Select count(1) from tablename; Select count(column) from tablename;

一般認為，在沒有主鍵索引的情況下，第二種 COUNT(1) 方法最快。如果只有一列，沒有索引，COUNT(*) 更快。如果有索引列，當然，使用索引列 COUNT() 是最快的。
13. 將該子句替換為Where子句，避免該子句，只有在檢索到所有記錄后才過濾結果集。這種處理需要進行排序、求和等操作。可以通過 WHERE 子句限制記錄的數量來減少這種開銷。
14.減少對表的查詢操作在包含子查詢的SQL語句中，注意減少對表的查詢操作。
/*低效SQL*/SELECT TAB_NAME FROM TABLESWHERE TAB_NAME =(SELECT?TAB_NAME FROM?TAB_COLUMNSWHERE VERSION = 604)AND?DB_VER?=(SELECT?DB_VER?FROM?TAB_COLUMNSWHERE?VERSION?=?604)

/*高效SQL*/SELECT TAB_NAME FROM TABLESWHERE (TAB_NAME，DB_VER)=(SELECT TAB_NAME，DB_VERFROM TAB_COLUMNSWHERE VERSION = 604)

15. 使用表別名（Alias） 在一條SQL語句中連接多個表時，請使用表別名，并在每張表前加上別名。這將減少解析時間并減少那些由歧義引起的語法錯誤。歧義是指由于 SQL 中不同的表同名，當 SQL 語句中出現 this 時，SQL 解析器無法確定 this 的歸屬。
16. 使用代替IN 在很多基于底層表的查詢中，為了滿足一個條件，往往需要連接另一個表。在這種情況下，使用（或 NOT ）通常會提高查詢的效率。
/*低效SQL*/SELECT * FROM EMP WHERE EMPNO > 0AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE?LOC?=?'MELB')

/*高效SQL*/SELECT * FROM EMPWHERE EMPNO > 0AND?EXISTS?(SELECT?1FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = 'MELB')

17. 使用NOT代替NOT IN 在子查詢中，NOT IN子句會進行內部排序和合并，對子查詢中的表進行全表遍歷，效率非常低。為了避免使用 NOT IN，可以將其重寫為 Outer Joins 或 NOT。
/*低效SQL*/SELECT * FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE?DEPT_CAT='A')

/*高效SQL*/SELECT * FROM EMP EWHERE NOT EXISTS (SELECT 1FROM DEPT DWHERE D.DEPT_NO = E.DEPT_NOAND DEPT_CAT ='A')

18. 用表連接代替表連接通常比表連接更有效。
/*低效SQL*/SELECT ENAMEFROM EMP EWHERE EXISTS (SELECT 1FROM DEPTWHERE DEPT_NO = E.DEPT_NOAND DEPT_CAT = 'A')

/*高效SQL*/SELECT ENAMEFROM DEPT D，EMP EWHERE E.DEPT_NO = D.DEPT_NOAND D.DEPT_CAT = 'A'

19. 在提交包含多個表（例如部門表和員工表）信息的查詢時，被替換為避免使用 in 子句。一般來說，您可以考慮將其替換為 EXIST。使查詢更快，因為 RDBMS 核心模塊會在子查詢條件滿足時立即返回結果。
/*低效SQL*/SELECT DISTINCT D.DEPT_NO，D.DEPT_NAMEFROM DEPT D，EMP EWHERE D.DEPT_NO = E.DEPT_NO

/*高效SQL*/SELECT D.DEPT_NO，D.DEPT_NAMEFROM DEPT DWHERE EXISTS (SELECT 1FROM EMP EWHERE E.DEPT_NO = D.DEPT_NO)

20. 識別低效SQL 下列SQL 工具可以識別低效SQL，前提是需要DBA 權限，否則無法進行查詢。
SELECT?EXECUTIONS,?DISK_READS,?BUFFER_GETS,ROUND ((BUFFER_GETS-DISK_READS)/BUFFER_GETS, 2) Hit_radio,ROUND (DISK_READS/EXECUTIONS, 2) Reads_per_run,   SQL_TEXTFROM   V$SQLAREAWHERE  EXECUTIONS>0AND??BUFFER_GETS?>?0?AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8ORDER BY 4 DESC

此外，您還可以使用 SQL Trace 工具收集執行 SQL 的性能狀態數據，包括解析次數、執行次數和 CPU 使用時間。
21. SQL  with Plan Plan 是一個很好的分析SQL 語句的工具，它甚至可以在不執行SQL 的情況下分析語句。通過分析，我們可以知道如何連接表oracle數據庫優化方法，使用如何掃描表（索引掃描或全表掃描）以及使用的索引名稱。
22. SQL PLUS 的 TRACE
SQL> listSELECT *FROM?dept,?empWHERE?emp.deptno?=?dept.deptnoSQL> set autotrace traceonly /*traceonly 可以不顯示執行結果*/SQL> /rows selected.Execution Plan----------------------------------------------------------SELECT STATEMENT Optimizer=CHOOSE0   NESTED LOOPS1     TABLE ACCESS (FULL) OF 'EMP' 1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'3       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

23. 使用索引提高效率
(1）特點優點：提高效率主鍵唯一性驗證成本：需要空間存儲來定期維護和重建索引：
ALTER INDEX  REBUILD 

(2）索引有兩種訪問方式
(3）基表選擇
(4）多個相等的索引
（5）等式比較優先于范圍比較有一個非唯一索引，并且也有一個非唯一索引。
SELECT ENAME FROM EMPWHERE DEPTNO > 20AND?EMP_CAT?=?'A'

這里只使用了索引，那么所有的記錄都會與條件一一進行比較。執行路徑如下：
TABLE?ACCESS?BY?ROWID?ON?EMPINDEX RANGE SCAN ON CAT_IDX

即使對于唯一索引，如果執行范圍比較，其優先級也低于非唯一索引的相等比較。
(6）不明確的索引級別當無法判斷索引的級別差異時，優化器將只使用一個索引，在WHERE子句中列在最前面。上面有一個非唯一索引，有也是一個非唯一索引。
SELECT ENAME FROM EMPWHERE DEPTNO > 20AND EMP_CAT > 'A'

這里只使用索引。執行路徑如下：
TABLE?ACCESS?BY?ROWID?ON?EMPINDEX RANGE SCAN ON DEPT_IDX

（7）強制索引無效如果兩個或多個索引具有相同的排名，您可以強制優化器使用其中一個（通過它檢索的記錄更少）。
SELECT?ENAMEFROM EMPWHERE EMPNO = 7935AND DEPTNO + 0 = 10    /*DEPTNO上的索引將失效*/AND EMP_TYPE || '' = 'A'  /*EMP_TYPE上的索引將失效*/

(8）如果索引列是函數的一部分，請避免在索引列上使用計算的 WHERE 子句。優化器將使用全表掃描而不是索引。
/*低效SQL*/SELECT * FROM DEPTWHERE SAL * 12 > 25000;

/*高效SQL*/SELECT * FROM DEPTWHERE SAL > 25000/12;

(9）如果表中有兩個以上（包括兩個）索引，則自動選擇索引，其中一個是唯一的，另一個是非唯一的。這種情況下，將使用唯一索引而不是完全忽略非唯一索??引。
SELECT ENAME FROM EMP WHERE EMPNO = 2326AND DEPTNO = 20;

在這里，只有 EMPNO 上的索引是唯一的，所以 EMPNO 索引將用于檢索記錄。

TABLE?ACCESS?BY?ROWID?ON?EMPINDEX UNIQUE SCAN ON EMP_NO_IDX

(10）避免在索引列上使用NOT 一般來說，我們希望避免在索引列上使用NOT，NOT與在索引列上使用函數的效果相同。當遇到NOT時，它會停止使用index 來執行全表掃描。
/*低效SQL:?(這里，不使用索引)*/SELECT * FROM DEPTWHERE NOT DEPT_CODE = 0

/*高效SQL:?(這里，使用索引)*/SELECT * FROM DEPTWHERE DEPT_CODE > 0

24. 如果有索引，則將 > 替換為 >=
/*高效SQL*/SELECT * FROM EMPWHERE DEPTNO >=4

/*低效SQL*/SELECT * FROM EMPWHERE DEPTNO >3

兩者的區別在于前者的DBMS會直接跳轉到DEPT等于4的第一條記錄，而后者會先定位等于3的記錄，然后向前掃描到DEPT大于3的第一條記錄。
25. 將 OR 替換為 Union（對于索引列） 通常，將 WHERE 子句中的 OR 替換為 UNION 效果會更好。在索引列上使用 OR 將導致全表掃描。請注意，上述規則僅對多個索引列有效。
/*高效SQL*/SELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE LOC_ID = 10UNIONSELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE REGION = 'MELBOURNE'

/*低效SQL*/SELECT LOC_ID,LOC_DESC,REGIONFROM LOCATIONWHERE LOC_ID = 10OR REGION = 'MELBOURNE'

26. 用 IN 替換 OR
/*低效SQL*/SELECT * FROM LOCATIONWHERE LOC_ID = 10OR LOC_ID = 20OR LOC_ID = 30

/*高效SQL*/SELECT * FROM LOCATIONWHERE LOC_IN IN (10，20，30)

實際執行效果還有待測試。下面，兩者的執行路徑似乎是一樣的。
27. 避免在索引列上使用is null 和is not null 避免在索引中使用任何可為空的列，索引將不起作用。
/*低效SQL：（索引失效）*/SELECT * FROM DEPARTMENTWHERE DEPT_CODE IS NOT NULL;

/*高效SQL：（索引有效）*/SELECT * FROM DEPARTMENTWHERE DEPT_CODE >=0;

28. 始終使用索引的第一列 如果索引建立在多列上，優化器將選擇僅在 where 子句引用其第一列 ( ) 時使用該索引。
SQL> create index multindex on multiindexusage(inda,indb);Index created.
SQL> select * from  multiindexusage where indb = 1;Execution Plan----------------------------------------------------------     SELECT STATEMENT Optimizer=CHOOSE0   TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE‘

顯然，優化器使用了全表掃描，并在僅引用了索引的第二列時忽略了該索引。
29. 使用UNION ALL代替UNION 當SQL語句需要對兩個查詢結果集進行UNION時，兩個結果集會以UNION-ALL的方式進行合并，然后進行排序后輸出最終結果。如果使用 UNION ALL 代替 UNION，則不需要排序，提高效率。由于UNION ALL的結果沒有排序oracle數據庫優化方法，重復記錄也沒有過濾，所以是否替換取決于業務需求。
30. UNION的優化 由于UNION對查詢結果進行排序，過濾重復記錄，所以執行效率不如UNION ALL高。UNION操作會用到一塊內存塊，所以這塊內存的優化也很重要。可以使用以下SQL查詢排序的消耗：
select substr（name，1，25）  "Sort Area Name"，substr（value，1，15）   "Value"from v$sysstatwhere name like 'sort%'

31. 避免改變索引列的類型
比較不同數據類型的數據時，會自動對列進行簡單類型轉換。
由于內部發生類型轉換，將不會使用此索引。幾點注意事項：
32. 使用提示 其他提示 這是一項棘手的工作。建議僅針對特定的少量 SQL 優化提示。
33.幾個不能使用索引的WHERE子句（1）在下面的例子中，'!='不會使用索引，索引只能告訴你表中存在什么，不能告訴你什么不存在' t 存在于表中。
/*不使用索引*/SELECT ACCOUNT_NAMEFROM TRANSACTIONWHERE AMOUNT !=0；

/*使用索引*/SELECT ACCOUNT_NAMEFROM TRANSACTIONWHERE AMOUNT > 0；

(2）在下面的例子中，'||' 是一個字符連接函數。和其他函數一樣，索引是禁用的。
/*不使用索引*/SELECT ACCOUNT_NAME，AMOUNTFROM TRANSACTIONWHERE ACCOUNT_NAME||ACCOUNT_TYPE='AMEXA'；

/*使用索引*/SELECT ACCOUNT_NAME，AMOUNTFROM TRANSACTIONWHERE ACCOUNT_NAME = 'AMEX'AND ACCOUNT_TYPE='A'；

(3）在下面的示例中，'+' 是一個數學函數。與其他數學函數一樣，索引被禁用。
/*不使用索引*/SELECT ACCOUNT_NAME，AMOUNTFROM TRANSACTIONWHERE AMOUNT + 3000 >5000；

/*使用索引*/SELECT ACCOUNT_NAME，AMOUNTFROM TRANSACTIONWHERE AMOUNT > 2000 ;

（4）在下面的示例中，相同的索引列無法相互比較，這將啟用全表掃描。
/*不使用索引*/SELECT ACCOUNT_NAME, AMOUNTFROM TRANSACTIONWHERE?ACCOUNT_NAME?=?NVL(:ACC_NAME,?ACCOUNT_NAME)

/*使用索引*/SELECT ACCOUNT_NAME，AMOUNTFROM TRANSACTIONWHERE ACCOUNT_NAME LIKE?NVL(:ACC_NAME,?’%’)

34. 加入多次掃描 如果將列與一組有限的值進行比較，優化器可能會執行多次掃描并合并結果。例子：
SELECT * FROM LODGING WHERE MANAGER IN ('BILL GATES','KEN MULLER')

優化器可能會將其轉換為以下形式：
SELECT * FROM LODGINGWHERE MANAGER = 'BILL GATES'OR MANAGER = 'KEN MULLER'

35. 在CBO下使用更具選擇性的索引
36. 避免資源密集型操作
37. 優化 GROUP BY，通過在 GROUP BY 之前過濾掉不必要的記錄來提高 GROUP BY 語句的效率。
/*低效SQL*/SELECT JOB,AVG（SAL）FROM EMPGROUP BY JOBHAVING JOB = 'PRESIDENT''OR JOB = 'MANAGER'

/*高效SQL*/SELECT JOB,AVG（SAL）FROM EMPWHERE JOB = 'PRESIDENT'OR JOB = 'MANAGER'GROUP BY JOB

38. 使用日期 使用日期時要注意，如果日期加上小數點后5位以上，日期會提前到第二天！
SELECT?TO_DATE（'01-JAN-93'+.99999）FROM DUAL結果：'01-JAN-93?23:59:59'
SELECT TO_DATE（'01-JAN-93'+.999999）FROM?DUAL結果：'02-JAN-93?00:00:00'

39. 使用 show () 來使用隱式游標，它將執行兩個操作。第一次檢索記錄，第二次檢查 TOO MANY ROWS 這個。并且顯式游標不執行第二個操作。
40. 分離表和索引 好了，關于SQL優化的內容，這篇文章應該可以滿足大部分一般的應用優化需求。剛到這里。

直播