Oracle管理OracleSQL性能優(yōu)化

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

2、TION L ,CATEGORY C, EMP EWHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND 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子句，根據(jù)這個(gè)原理，表之間的連接必須寫在 其他

3、WHERE條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾. 例如：(低效，執(zhí)行時(shí)間156.3秒) SELECT FROM EMP EWHERE SAL 50000AND JOB = MANAGERAND 25 (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO);(高效，執(zhí)行時(shí)間10.6秒)SELECT FROM EMP EWHERE 25 50000AND JOB = MANAGER;. SELECT子句中避免使用*.用Where子句替換HAVING子句避免使用HAVING子句，HAVING只會(huì)在檢索出所有記錄之后才對結(jié)果集進(jìn)行過濾.

4、這個(gè)處理需要排序，總計(jì)等操作.如果能通過 WHERE子句限制記錄的數(shù)目，那就能減少這 方面的開銷.例如：彳氐效：SELECT REGION , AVG(LOG_SIZE)FROM LOCATIONGROUP BY REGIONHAVING REGION REGION != SYDNEYAND REGION != PERTH局效SELECT REGION , AVG(LOG_SIZE)FROM LOCATIONWHERE REGION REGION != SYDNEYAND REGION != PERTHGROUP BY REGION.減少對表的查詢在含有子查詢的 SQL語句中，要特別注意減少對

5、表的查詢 . 例如：低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = ( SELECT TAB_NAMEFROM TAB_COLUMNSWHERE VERSION = 604)AND DB_VER= ( SELECT DB_VERFROM TAB_COLUMNSWHERE VERSION = 604)局效SELECT TAB_NAME FROM TABLESWHERE (TAB_NAME,DB_VER)=(SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNSWHERE VERSION = 604)Update 多個(gè) Column

6、 例子：低效：UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;局效:UPDATE EMPSET (EMP_CAT, SAL_RANGE)=(SELECT MAX(CA TEGORY) , MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;.使用表的別名當(dāng)在SQL語句中連接多個(gè)表時(shí)，請使用表的別

7、名并把別名前綴于每個(gè)Column上.這樣一來，就可以減少解析的時(shí)間并減少那些由Column歧義引起的語法錯(cuò)誤.用 EXISTS 替代 IN在許多基于基礎(chǔ)表的查詢中，為了滿足一個(gè)條件，往往需要對另一個(gè)表進(jìn)行聯(lián)接.在這種情 況下，使用EXISTS(或NOT EXISTS)通常將提高查詢的效率.低效：SELECT *FROM EMP (基礎(chǔ)表)WHERE EMPNO 0AND DEPTNO IN (SELECT DEPTNO FROM DEPTWHERE LOC = MELB)局效:SELECT *FROM EMP (基礎(chǔ)表)WHERE EMPNO 0AND EXISTS (SELECT X FRO

8、M DEPTWHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = MELB).用 NOT EXISTS 替代 NOT IN在子查詢中,NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并 .無論在哪種情況下,NOT IN都 是最低效的(因?yàn)樗鼘ψ硬樵冎械谋韴?zhí)行了一個(gè)全表遍歷 ).為了避免使用 NOT IN，我 們可以把它改寫成外連接 (Outer Joins)或NOT EXISTS.例如：SELECT FROM EMPWHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPTWHERE DEPT_CAT=A); 為了提高效率.改寫為： (方法一

9、：高效)SELECT .FROM EMP A,DEPT BWHERE A.DEPT_NO = B.DEPT(+)AND B.DEPT_NO IS NULLAND B.DEPT_CAT(+) = A(方法二：最高效)SELECT .FROM EMP EWHERE NOT EXISTS (SELECT XFROM DEPT DWHERE D.DEPT_NO = E.DEPT_NOAND DEPT_CAT = A);.用表連接替換 EXISTS通常來說，采用表連接的方式比 EXISTS更有效率SELECT ENAMEFROM EMP EWHERE EXISTS (SELECT XFROM DEPTW

10、HERE DEPT_NO = E.DEPT_NOAND DEPT_CAT = A);(更高效)SELECT ENAMEFROM DEPT D,EMP EWHERE E.DEPT_NO = D.DEPT_NOAND DEPT_CAT = A;.用 EXISTS 替換 DISTINCT當(dāng)提交一個(gè)包含一對多表信息(比如部門表和雇員表)的查詢時(shí)，避免在SELECT子句中使用DISTINCT. 一般可以考慮用 EXIST替換例如： 低效：SELECT DISTINCT DEPT_NO,DEPT_NAMEFROM DEPT D,EMP EWHERE D.DEPT_NO = E.DEPT_NO局效:SELE

11、CT DEPT_NO,DEPT_NAMEFROM DEPT DWHERE EXISTS ( SELECT X FROM EMP EWHERE E.DEPT_NO = D.DEPT_NO);EXISTS使查詢更為迅速，因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿足后，立刻返回結(jié)果.基礎(chǔ)表的選擇基礎(chǔ)表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問).根據(jù)優(yōu)化器的不同，SQL語句中基礎(chǔ)表的選擇是不一樣的如果你使用的是 CBO (COST BASED OPTIMIZER), 優(yōu)化器會(huì)檢查 SQL語句中的每個(gè)表 的物理大小，索引的狀態(tài)，然后選用花費(fèi)最低的執(zhí)行路徑 .如果你用

12、RBO (RULE BASED OPTIMIZER),并且所有的連接條件都有索引對應(yīng)，在這種情況下，基礎(chǔ)表就是FROM子句中列在最后的那個(gè)表.舉例：SELECT A.NAME , B.MANAGER FROM WORKER A, LODGING BWHERE A.LODGING = B.LODING;由于LODGING表的LODING列上有一個(gè)索引，而且 WORKER表中沒有相比較的索引 WORKER表將被作為查詢中的基礎(chǔ)表 .用UNION替換OR (適用于索引列)通常情況下，用UNION替換WHERE子句中的OR將會(huì)起到較好的效果.對索引列使用OR將造成全表掃描.注意，以上規(guī)則只針對多個(gè)索引

13、列有效.如果有column沒有被索弓I,查詢效率可能會(huì)因?yàn)槟銢]有選擇OR而降低.在下面的例子中，LOC_ID和REGION上都建有索引.局效:SELECT LOC_ID , LOC_DESC , REGION FROM LOCATIONWHERE LOC_ID = 10 UNIONSELECT LOC_ID , LOC_DESC , REGION FROM LOCATIONWHERE REGION = MELBOURNE低效：SELECT LOC_ID , LOC_DESC , REGION FROM LOCATIONWHERE LOC_ID = 10 OR REGION = MELBOURN

14、E如果你堅(jiān)持要用 OR,那就需要返回記錄最少的索引列寫在最前面.注意：WHERE KEY1 = 10 (返回最少t己錄)OR KEY2 = 20 (返回最多記錄)ORACLE內(nèi)部將以上轉(zhuǎn)換為WHERE KEY1 =10 AND(NOT KEY1 = 10) AND KEY2 = 20).用IN來替換OR 下面的查詢可以被更有效率的語句替換：低效：SELECT-. FROM LOCATIONWHERE LOC_ID = 10OR LOC_ID = 20OR LOC_ID = 30 局效SELECT- FROM LOCATION WHERE LOC_IN IN （10,20,30）;.避免在索引列

15、上使用 IS NULL和IS NOT NULL避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引.對于單列索引，如果列包含空值，索引中將不存在此記錄.對于復(fù)合索引，如果每個(gè)列都為空，索引中同樣不存在此記錄.如果至少有一個(gè)列不為空，則記錄存在于索引中. 舉例：如果唯一性索引建立在表的 A歹U和B列上，并且表中存在一條記錄的 A,B值為（123,null）, ORACLE將不接受下一條具有相同 A,B值（123,null）的記錄（插入）.然而如果 所有的索引列都為空，ORACLE將認(rèn)為整個(gè)鍵值為空而空不等于空.因此你可以插入1000 條具有相同鍵值的記錄，當(dāng)然它們都是空！ 因?yàn)榭罩挡淮?/p>

16、在于索引列中，所以WHERE子句中對索引列進(jìn)行空值比較將使ORACLE停用該索引. 舉例： 低效：（索引失效） SELECT FROM DEPARTMENTWHERE DEPT_CODE IS NOT NULL; 高效：（索引有效） SELECT FROM DEPARTMENTWHERE DEPT_CODE =0;.總是使用索引的第一個(gè)列.用 WHERE 替代 ORDER BY TOC o 1-5 h z ORDER BY子句只在兩種嚴(yán)格的條件下使用索引.ORDER BY中所有的列必須包含在相同的索引中并保持在索引中的排列順序ORDER BY中所有的列必須定義為非空.WHERE子句使用的索引和

17、 ORDER BY子句中所使用的索引不能并列 . 例如： 表DEPT包含以下列： DEPT_CODE PK NOT NULL DEPT_DESC NOT NULL DEPT_TYPE NULL 非唯一性的索引（DEPT_TYPE） 低效：（索引不被使用） SELECT DEPT_CODE FROM DEPTORDER BY DEPT_TYPE EXPLAIN PLAN:SORT ORDER BYTABLE ACCESS FULL高效：（使用索引）SELECT DEPT_CODEFROM DEPTWHERE DEPT_TYPE 0EXPLAIN PLAN:TABLE ACCESS BY ROWI

18、D ON EMPINDEX RANGE SCAN ON DEPT_IDX譯者按：ORDER BY也能使用索引！這的確是個(gè)容易被忽視的知識點(diǎn).我們來驗(yàn)證一下：.需要當(dāng)心的 WHERE 子句某些SELECT語句中的 WHERE子句不使用索引.這里有一些例子.在下面的例子里，!二將不使用索引.記住，索引只能告訴你什么存在于表中，而不能告訴你什么不存在于表中.不使用索引：SELECT ACCOUNT_NAMEFROM TRANSACTIONWHERE AMOUNT !=0;使用索引：SELECT ACCOUNT_NAMEFROM TRANSACTIONWHERE AMOUNT 0;下面的例子中，建字符

19、連接函數(shù).就象其他函數(shù)那樣，停用了索引.不使用索引：SELECT ACCOUNT_NAME,AMOUNTFROM TRANSACTIONWHERE ACCOUNT_NAME|ACCOUNT_TYPE=AMEXA;使用索引：SELECT ACCOUNT_NAME,AMOUNTFROM TRANSACTIONWHERE ACCOUNT_NAM E =AMEXAND ACCOUNT_TYPE= A;下面的例子中，是數(shù)學(xué)函數(shù).就象其他數(shù)學(xué)函數(shù)那樣，停用了索引.不使用索引：SELECT ACCOUNT_NAME, AMOUNTFROM TRANSACTIONWHERE AMOUNT + 3000 5000;使用索引：SELECT ACCOUNT_NAME, AMOUNTFROM TRANSACTIONWHERE AMOUNT 2000 ;下面的例子中，相同的索引列不能互相比較 ，這將會(huì)啟用全表掃描.不使用索引：SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTIONWHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAM