淺析Oracle SQL性能優(yōu)化

1、Oracle SQL 性能優(yōu)化選擇最有效率的表名順序例如: 表 TAB1 16,384 條記錄 表 TAB2 1 條記錄 選擇TAB2作為基礎(chǔ)表 (最好的方法) select count(*) from tab1,tab2 執(zhí)行時刻0.96秒 選擇TAB1作為基礎(chǔ)表 (不佳的方法) select count(*) from tab2,tab1 執(zhí)行時刻26.09秒 假如有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎(chǔ)表, 交叉表是指那個被其他表所引用的表. 例如: EMP表描述了LOCATION表和CATEGORY表的交集. SELECT * FRO

2、M LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN 將比下列SQL更有效率 SELECT * FROM EMP E , LOCATION L , CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000 WHERE子句中的連接順序ORACLE采納自下而上的順序解析WHERE子句,依照那個原理,表之

3、間的連接必須寫在其他WHERE條件之前, 那些能夠過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾. 例如: (低效,執(zhí)行時刻156.3秒)SELECT FROM EMP E WHERE SAL 50000 AND JOB = MANAGER AND 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); (高效,執(zhí)行時刻10.6秒) SELECT FROM EMP E WHERE 25 50000 AND JOB = MANAGER;SELECT子句中幸免使用 * 用Where子句替換HAVING子句幸免使用HAVING子句, HAVING 只會

4、在檢索出所有記錄之后才對結(jié)果集進行過濾. 那個處理需要排序,總計等操作. 假如能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷. 例如: 低效: SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != SYDNEY AND REGION != PERTH 高效 SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != SYDNEY AND REGION != PERTH GROUP BY REGION減少對

5、表的查詢在含有子查詢的SQL語句中,要特不注意減少對表的查詢. 例如: 低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) ANDDB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) 高效 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COL

6、UMNS WHERE VERSION = 604) Update 多個Column 例子: 低效: UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020; 高效: UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES)

7、WHERE EMP_DEPT = 0020;使用表的不名當在SQL語句中連接多個表時, 請使用表的不名并把不名前綴于每個Column上.如此一來,就能夠減少解析的時刻并減少那些由Column歧義引起的語法錯誤.用EXISTS替代IN在許多基于基礎(chǔ)表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯(lián)接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率. 低效: SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = MELB) 高效: SELE

8、CT * FROM EMP (基礎(chǔ)表) WHERE EMPNO 0 AND EXISTS (SELECT X FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = MELB)用NOT EXISTS替代NOT IN在子查詢中,NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并. 不管在哪種情況下,NOT IN差不多上最低效的 (因為它對子查詢中的表執(zhí)行了一個全表遍歷). 為了幸免使用NOT IN ,我們能夠把它改寫成外連接(Outer Joins)或NOT EXISTS. 例如: SELECT FROM EMP WHERE DEPT_NO NOT IN (

9、SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=A); 為了提高效率.改寫為: (方法一: 高效) SELECT . FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = A (方法二: 最高效) SELECT . FROM EMP E WHERE NOT EXISTS (SELECT X FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = A);用表連接替換EXISTS通常來講 ,

10、采納表連接的方式比EXISTS更有效率 SELECT ENAME FROM EMP E WHERE EXISTS (SELECT X FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = A); (更高效) SELECT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = A ;用EXISTS替換DISTINCT當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,幸免在SELECT子句中使用DISTINCT. 一般能夠考慮用EXIST替換 例如: 低效: SE

11、LECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO 高效: SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT X FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); EXISTS 使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足后,趕忙返回結(jié)果.基礎(chǔ)表的選擇基礎(chǔ)表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問). 依照優(yōu)化器的不同, SQL語句中基礎(chǔ)表

12、的選擇是不一樣的. 假如你使用的是CBO (COST BASED OPTIMIZER),優(yōu)化器會檢查SQL語句中的每個表的物理大小,索引的狀態(tài),然后選用花費最低的執(zhí)行路徑. 假如你用RBO (RULE BASED OPTIMIZER) , 同時所有的連接條件都有索引對應(yīng), 在這種情況下, 基礎(chǔ)表確實是FROM 子句中列在最后的那個表. 舉例: SELECT A.NAME , B.MANAGER FROMWORKER A, LODGING B WHEREA.LODGING = B.LODING; 由于LODGING表的LODING列上有一個索引, 而且WORKER表中沒有相比較的索引, WORK

13、ER表將被作為查詢中的基礎(chǔ)表.用UNION替換OR (適用于索引列)通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描. 注意, 以上規(guī)則只針對多個索引列有效. 假如有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION

14、WHERE REGION = “MELBOURNE” 低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 假如你堅持要用OR, 那就需要返回記錄最少的索引列寫在最前面.注意: WHERE KEY1 = 10 (返回最少記錄) OR KEY2 = 20 (返回最多記錄) ORACLE 內(nèi)部將以上轉(zhuǎn)換為 WHERE KEY1 = 10 AND (NOT KEY1 = 10) AND KEY2 = 20)用IN來替換OR下面的查詢能夠被更有效率的語句替換: 低效:

15、SELECT. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 高效 SELECT FROM LOCATION WHERE LOC_IN IN (10,20,30);幸免在索引列上使用IS NULL和IS NOT NULL幸免在索引中使用任何能夠為空的列，ORACLE將無法使用該索引 關(guān)于單列索引，假如列包含空值，索引中將不存在此記錄. 關(guān)于復合索引，假如每個列都為空，索引中同樣不存在此記錄.假如至少有一個列不為空，則記錄存在于索引中 舉例: 假如唯一性索引建立在表的A列和B列上, 同時表中存在一條記錄的A,B值為(1

16、23,null) , ORACLE將不同意下一條具有相同A,B值（123,null）的記錄(插入). 然而假如 所有的索引列都為空，ORACLE將認為整個鍵值為空而空不等于空. 因此你能夠插入1000 條具有相同鍵值的記錄,因此它們差不多上空! 因為空值不存在于索引列中,因此WHERE子句中對索引列進行空值比較將使ORACLE停用該索引. 舉例: 低效: (索引失效) SELECT FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 高效: (索引有效) SELECT FROM DEPARTMENT WHERE DEPT_CODE =0;總是使用索引的第一

17、個列16. 用WHERE替代ORDER BYORDER BY 子句只在兩種嚴格的條件下使用索引. ORDER BY中所有的列必須包含在相同的索引中并保持在索引中的排列順序. ORDER BY中所有的列必須定義為非空. WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列. 例如: 表DEPT包含以下列: DEPT_CODE PK NOT NULL DEPT_DESC NOT NULL DEPT_TYPE NULL 非唯一性的索引(DEPT_TYPE) 低效: (索引不被使用) SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE EXPLA

18、IN PLAN: SORT ORDER BY TABLE ACCESS FULL 高效: (使用索引) SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE 0 EXPLAIN PLAN: TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON DEPT_IDX 譯者按: ORDER BY 也能使用索引! 這的確是個容易被忽視的知識點. 我們來驗證一下:需要當心的WHERE子句某些SELECT 語句中的WHERE子句不使用索引. 那個地點有一些例子. 在下面的例子里, != 將不使用索引. 記住, 索引只能告訴你什么存在

19、于表中, 而不能告訴你什么不存在于表中. 不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0; 使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT 0; 下面的例子中, |是字符連接函數(shù). 就象其他函數(shù)那樣, 停用了索引. 不使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME|ACCOUNT_TYPE=AMEXA; 使用索引: SELECT ACCOUNT_NAME,AMOUNT

20、FROM TRANSACTION WHERE ACCOUNT_NAME = AMEX AND ACCOUNT_TYPE= A; 下面的例子中, +是數(shù)學函數(shù). 就象其他數(shù)學函數(shù)那樣, 停用了索引. 不使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE AMOUNT + 3000 5000; 使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE AMOUNT 2000 ; 下面的例子中,相同的索引列不能互相比較,這將會啟用全表掃描. 不使用索引: SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT