自總結（基于時間、空間的原則）：
一、按SQL執行順序調整SQL子句
二、充分利用索引
三、充分利用内部函數
四、exists, in, distinct, 連接配接查詢之間的選擇
五、減少SQL解釋過程中資料字典的查詢
六、利用緩存
MySQL的索引類型：
普通索引
唯一索引
主鍵索引
組合索引
Oracle的主要索引類型：
B-Tree 索引 (預設)
BITMAP 位圖索引
UNIQUE 唯一值索引
CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>
ON <schema>.<table_name>
(<column_name> | <expression> ASC | DESC,
<column_name> | <expression> ASC | DESC,...)
TABLESPACE <tablespace_name>
STORAGE <storage_settings>
LOGGING | NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS | COMPRESS<nn>
NOSORT | REVERSE
PARTITION | GLOBAL PARTITION<partition_setting>;
參見（http://justplayoop1.iteye.com/blog/1259562）
1. 選用适合的ORACLE優化器
ORACLE的優化器共有3種: a. RULE (基于規則) b. COST (基于成本) c. CHOOSE (選擇性)
設定預設的優化器,可以通過對init.ora檔案中OPTIMIZER_MODE參數的各種聲明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你當然也在SQL句級或是會話(session)級對其進行覆寫.
為了使用基于成本的優化器(CBO, Cost-Based Optimizer) , 你必須經常運作analyze 指令,以增加資料庫中的對象統計資訊(object statistics)的準确性.
如果資料庫的優化器模式設定為選擇性(CHOOSE),那麼實際的優化器模式将和是否運作過analyze指令有關. 如果table已經被analyze過, 優化器模式将自動成為CBO, 反之,資料庫将采用RULE形式的優化器.
在預設情況下,ORACLE采用CHOOSE優化器, 為了避免那些不必要的全表掃描(full table scan), 你必須盡量避免使用CHOOSE優化器,而直接采用基于規則或者基于成本的優化器.
2.通路Table的方式
ORACLE 采用兩種通路表中記錄的方式:
a.全表掃描
全表掃描就是順序地通路表中每條記錄. ORACLE采用一次讀入多個資料塊(database block)的方式優化全表掃描.
b.通過ROWID通路表
你可以采用基于ROWID的通路方式情況,提高通路表的效率,ROWID包含了表中記錄的實體位置資訊.ORACLE采用索引(INDEX)實作了資料和存放資料的實體位置(ROWID)之間的聯系. 通常索引提供了快速通路ROWID的方法,是以那些基于索引列的查詢就可以得到性能上的提高.
3.共享SQL語句
為了不重複解析相同的SQL語句,在第一次解析之後, ORACLE将SQL語句存放在記憶體中.這塊位于系統全局區域SGA(system global area)的共享池(shared buffer pool)中的記憶體可以被所有的資料庫使用者共享. 是以,當你執行一個SQL語句(有時被稱為一個遊标)時,如果它和之前的執行過的語句完全相同, ORACLE就能很快獲得已經被解析的語句以及最好的執行路徑. ORACLE的這個功能大大地提高了SQL的執行性能并節省了記憶體的使用.
可惜的是ORACLE隻對簡單的表提供高速緩沖(cache buffering) ,這個功能并不适用于多表連接配接查詢.資料庫管理者必須在init.ora中為這個區域設定合适的參數,當這個記憶體區域越大,就可以保留更多的語句,當然被共享的可能性也就越大了. 當你向ORACLE 送出一個SQL語句,ORACLE會首先在這塊記憶體中查找相同的語句. 這裡需要注明的是,ORACLE對兩者采取的是一種嚴格比對,要達成共享,SQL語句必須完全相同(包括空格,換行等).
共享的語句必須滿足三個條件:
A.字元級的比較:
目前被執行的語句和共享池中的語句必須完全相同.
例如:
SELECT * FROM EMP;
和下列每一個都不同
SELECT * from EMP;
Select * From Emp;
SELECT * FROM EMP;
B.兩個語句所指的對象必須完全相同
C.兩個SQL語句中必須使用相同的名字的綁定變量(bind variables)
例如：第一組的兩個SQL語句是相同的(可以共享),而第二組中的兩個語句是不同的(即使在運作時,賦于不同的綁定變量相同的值)
a.
select pin, name from people where pin = :blk1.pin;
select pin, name from people where pin = :blk1.pin;
b.
select pin, name from people where pin = :blk1.ot_ind;
select pin, name from people where pin = :blk1.ov_ind;
4.選擇最有效率的表名順序(隻在基于規則的優化器中有效)
ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,是以FROM子句中寫在最後的表(基礎表 driving table)将被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表.當ORACLE處理多個表時, 會運用排序及合并的方式連接配接它們.首先,掃描第一個表(FROM子句中最後的那個表)并對記錄進行派序,然後掃描第二個表(FROM子句中最後第二個表),最後将所有從第二個表中檢索出的記錄與第一個表中合适記錄進行合并.
例如: 表 TAB1 16,384 條記錄
表 TAB2 1 條記錄
選擇TAB2作為基礎表 (最好的方法)
select count(*) from tab1,tab2 執行時間0.96秒
選擇TAB2作為基礎表 (不佳的方法)
select count(*) from tab2,tab1 執行時間26.09秒
如果有3個以上的表連接配接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.
例如: EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT *
FROM 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
5.WHERE子句中的連接配接順序．
ORACLE采用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接配接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.
例如:
(低效,執行時間156.3秒)
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER’
AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);
(高效,執行時間10.6秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER’;
6.SELECT子句中避免使用“*”
當你想在SELECT子句中列出所有的COLUMN時,使用動态SQL列引用“*”是一個友善的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中,會将“*” 依次轉換成所有的列名, 這個工作是通過查詢資料字典完成的, 這意味着将耗費更多的時間.
7.減少通路資料庫的次數
當執行每條SQL語句時, ORACLE在内部執行了許多工作: 解析SQL語句, 估算索引的使用率, 綁定變量, 讀資料塊等等. 由此可見, 減少通路資料庫的次數, 就能實際上減少ORACLE的工作量.
例如,
以下有三種方法可以檢索出雇員号等于0342或0291的職員.
方法1 (最低效)
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = 291;
方法2 (次低效)
DECLARE
CURSOR C1 (E_NO NUMBER) IS
SELECT EMP_NAME, SALARY, GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1(342);
FETCH C1 INTO …,..,.. ;
OPEN C1(291);
FETCH C1 INTO …,..,.. ;
CLOSE C1;
END;
方法3 (高效)
SELECT A.EMP_NAME, A.SALARY, A.GRADE,
B.EMP_NAME, B.SALARY, B.GRADE
FROM EMP A,EMP B
WHERE A.EMP_NO = 342
AND B.EMP_NO = 291;
注意: 在SQL*Plus,SQL*Forms和Pro*C中重新設定ARRAYSIZE參數, 可以增加每次資料庫通路的檢索資料量,建議值為200.
8.使用DECODE函數來減少處理時間
使用DECODE函數可以避免重複掃描相同記錄或重複連接配接相同的表.
例如:
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE 'SMITH%';
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE 'SMITH%';
你可以用DECODE函數高效地得到相同結果
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
類似的,DECODE函數也可以運用于GROUP BY和ORDER BY子句中.
9.整合簡單且無關聯的資料庫通路
如果你有幾個簡單的資料庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關系)
例如:
SELECT NAME
FROM EMP
WHERE EMP_NO = 1234;
SELECT NAME
FROM DPT
WHERE DPT_NO = 10 ;
SELECT NAME
FROM CAT
WHERE CAT_TYPE = ‘RD’;
上面的3個查詢可以被合并成一個:
SELECT E.NAME, D.NAME, C.NAME
FROM CAT C, DPT D, EMP E, DUAL X
WHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,E.ROWID(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))
AND E.EMP_NO(+) = 1234
AND D.DEPT_NO(+) = 10
AND C.CAT_TYPE(+) = ‘RD’;
(譯者按: 雖然采取這種方法,效率得到提高,但是程式的可讀性大大降低,是以讀者還是要權衡之間的利弊)
10.删除重複記錄
最高效的删除重複記錄方法 (因為使用了ROWID)
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
11.用TRUNCATE替代DELETE
當删除表中的記錄時,在通常情況下, 復原段(rollback segments )用來存放可以被恢複的資訊. 如果你沒有COMMIT事務,ORACLE會将資料恢複到删除之前的狀态(準确地說是恢複到執行删除指令之前的狀況)
而當運用TRUNCATE時, 復原段不再存放任何可被恢複的資訊.當指令運作後,資料不能被恢複.是以很少的資源被調用,執行時間也會很短.
(譯者按: TRUNCATE隻在删除全表适用,TRUNCATE是DDL不是DML)
12.盡量多使用COMMIT
隻要有可能,在程式中盡量多使用COMMIT, 這樣程式的性能得到提高,需求也會因為COMMIT所釋放的資源而減少:
COMMIT所釋放的資源:
a.復原段上用于恢複資料的資訊.
b.被程式語句獲得的鎖
c.redo log buffer 中的空間
d.ORACLE為管理上述3種資源中的内部花費
(譯者按: 在使用COMMIT時必須要注意到事務的完整性,現實中效率和事務完整性往往是魚和熊掌不可得兼)
13.計算記錄條數
和一般的觀點相反, count(*)比count(1)稍快, 當然如果可以通過索引檢索,對索引列的計數仍舊是最快的. 例如 COUNT(EMPNO)
(譯者按: 在CSDN論壇中,曾經對此有過相當熱烈的讨論, 作者的觀點并不十分準确,通過實際的測試,上述三種方法并沒有顯著的性能差别)
14.用Where子句替換HAVING子句
避免使用HAVING子句, HAVING 隻會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷.
例如:
低效:
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
(譯者按: HAVING 中的條件一般用于對一些集合函數的比較,如COUNT()等等. 除此而外,一般的條件應該寫在WHERE子句中)
15.減少對表的查詢
在含有子查詢的SQL語句中,要特别注意減少對表的查詢.
例如:
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_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_COLUMNS
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)
WHERE EMP_DEPT = 0020;
16.通過内部函數提高SQL效率.
SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
FROM HISTORY_TYPE T, EMP E, EMP_HISTORY H
WHERE H.EMPNO = E.EMPNO
AND H.HIST_TYPE = T.HIST_TYPE
GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
通過調用下面的函數可以提高效率.
FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2 AS
TDESC VARCHAR2(30);
CURSOR C1 IS
SELECT TYPE_DESC
FROM HISTORY_TYPE
WHERE HIST_TYPE = TYP;
BEGIN
OPEN C1;
FETCH C1 INTO TDESC;
CLOSE C1;
RETURN (NVL(TDESC,’?’));
END;
FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2 AS
ENAME VARCHAR2(30);
CURSOR C1 IS
SELECT ENAME
FROM EMP
WHERE EMPNO=EMP;
BEGIN
OPEN C1;
FETCH C1 INTO ENAME;
CLOSE C1;
RETURN (NVL(ENAME,’?’));
END;
SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO), H.HIST_TYPE, LOOKUP_HIST_TYPE(H.HIST_TYPE), COUNT(*)
FROM EMP_HISTORY H
GROUP BY H.EMPNO, H.HIST_TYPE;
(譯者按: 經常在論壇中看到如 ’能不能用一個SQL寫出….’ 的貼子, 殊不知複雜的SQL往往犧牲了執行效率. 能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的)
17.使用表的别名(Alias)
當在SQL語句中連接配接多個表時, 請使用表的别名并把别名字首于每個Column上.這樣一來,就可以減少解析的時間并減少那些由Column歧義引起的文法錯誤.
(譯者注: Column歧義指的是由于SQL中不同的表具有相同的Column名,當SQL語句中出現這個Column時,SQL解析器無法判斷這個Column的歸屬)
18.用EXISTS替代IN
在許多基于基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常将提高查詢的效率.
低效:
SELECT *
FROM EMP (基礎表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB’)
高效:
SELECT *
FROM EMP (基礎表)
WHERE EMPNO > 0
AND EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’)
(譯者按: 相對來說,用NOT EXISTS替換NOT IN 将更顯著地提高效率,下一節中将指出)
19.用NOT EXISTS替代NOT IN
在子查詢中,NOT IN子句将執行一個内部的排序和合并. 無論在哪種情況下,NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表周遊). 為了避免使用NOT IN ,我們可以把它改寫成外連接配接(Outer Joins)或NOT EXISTS.
例如:
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (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’);
20.用表連接配接替換EXISTS
通常來說, 采用表連接配接的方式比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’ ;
(譯者按: 在RBO的情況下,前者的執行路徑包括FILTER,後者使用NESTED LOOP)
21.用EXISTS替換DISTINCT
當送出一個包含一對多表資訊(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換
例如:
低效:
SELECT 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核心子產品将在子查詢的條件一旦滿足後,立刻傳回結果.
22.識别’低效執行’的SQL語句
用下列SQL工具找出低效SQL:
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_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>; 0
AND BUFFER_GETS >; 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
(譯者按: 雖然目前各種關于SQL優化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個最好的方法)
23.使用TKPROF工具來查詢SQL性能狀态
SQL trace工具收集正在執行的SQL的性能狀态資料并記錄到一個跟蹤檔案中. 這個跟蹤檔案提供了許多有用的資訊,例如解析次數.執行次數,CPU使用時間等.這些資料将可以用來優化你的系統.
設定SQL TRACE在會話級别: 有效
ALTER SESSION SET SQL_TRACE TRUE
設定SQL TRACE 在整個資料庫有效仿, 你必須将SQL_TRACE參數在init.ora中設為TRUE, USER_DUMP_DEST參數說明了生成跟蹤檔案的目錄
(譯者按: 這一節中,作者并沒有提到TKPROF的用法, 對SQL TRACE的用法也不夠準确, 設定SQL TRACE首先要在init.ora中設定TIMED_STATISTICS, 這樣才能得到那些重要的時間狀态. 生成的trace檔案是不可讀的,是以要用TKPROF工具對其進行轉換,TKPROF有許多執行參數. 大家可以參考ORACLE手冊來了解具體的配置. )
24.用EXPLAIN PLAN分析SQL語句
EXPLAIN PLAN 是一個很好的分析SQL語句的工具,它甚至可以在不執行SQL的情況下分析語句. 通過分析,我們就可以知道ORACLE是怎麼樣連接配接表,使用什麼方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱.
你需要按照從裡到外,從上到下的次序解讀分析的結果. EXPLAIN PLAN分析的結果是用縮進的格式排列的, 最内部的操作将被最先解讀, 如果兩個操作處于同一層中,帶有最小操作号的将被首先執行.
NESTED LOOP是少數不按照上述規則處理的操作, 正确的執行路徑是檢查對NESTED LOOP提供資料的操作,其中操作号最小的将被最先處理.
譯者按: 通過實踐, 感到還是用SQLPLUS中的SET TRACE 功能比較友善.
舉例:
SQL> list
1 SELECT *
2 FROM dept, emp
3* WHERE emp.deptno = dept.deptno
SQL> set autotrace traceonly
SQL> /
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
30 consistent gets
0 physical reads
0 redo size
2598 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
通過以上分析,可以得出實際的執行步驟是:
1.TABLE ACCESS (FULL) OF 'EMP'
2.INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
3.TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4.NESTED LOOPS (JOINING 1 AND 3)
注: 目前許多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了極其友善的EXPLAIN PLAN工具.也許喜歡圖形化界面的朋友們可以選用它們.
25.用索引提高效率
索引是表的一個概念部分,用來提高檢索資料的效率. 實際上,ORACLE使用了一個複雜的自平衡B-tree結構. 通常,通過索引查詢資料比全表掃描要快. 當ORACLE找出執行查詢和Update語句的最佳路徑時, ORACLE優化器将使用索引. 同樣在聯結多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.
除了那些LONG或LONG RAW資料類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特别有效. 當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率.
雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味着每條記錄的INSERT , DELETE , UPDATE将為此多付出4 , 5 次的磁盤I/O . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢.
譯者按: 定期的重構索引是有必要的. ALTER INDEX <INDEXNAME>; REBUILD <TABLESPACENAME>;
26.索引的操作
ORACLE對索引有兩種通路模式.
索引唯一掃描 (INDEX UNIQUE SCAN) ：
大多數情況下, 優化器通過WHERE子句通路INDEX.
例如:
表LODGING有兩個索引: 建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER.
SELECT *
FROM LODGING
WHERE LODGING = ‘ROSE HILL’;
在内部, 上述SQL将被分成兩步執行, 首先, LODGING_PK索引将通過索引唯一掃描的方式被通路, 獲得相對應的ROWID, 通過ROWID通路表的方式執行下一步檢索.
如果被檢索傳回的列包括在INDEX列中,ORACLE将不執行第二步的處理(通過ROWID通路表). 因為檢索資料儲存在索引中, 單單通路索引就可以完全滿足查詢結果.
下面SQL隻需要INDEX UNIQUE SCAN 操作.
SELECT LODGING
FROM LODGING
WHERE LODGING = ‘ROSE HILL’;
索引範圍查詢(INDEX RANGE SCAN) ：
适用于兩種情況:
1.基于一個範圍的檢索
2.基于非唯一性索引的檢索
例1:
SELECT LODGING
FROM LODGING
WHERE LODGING LIKE ‘M%’;
WHERE子句條件包括一系列值, ORACLE将通過索引範圍查詢的方式查詢LODGING_PK. 由于索引範圍查詢将傳回一組值, 它的效率就要比索引唯一掃描低一些.
例2:
SELECT LODGING
FROM LODGING
WHERE MANAGER = ‘BILL GATES’;
這個SQL的執行分兩步, LODGING$MANAGER的索引範圍查詢(得到所有符合條件記錄的ROWID) 和下一步同過ROWID通路表得到LODGING列的值. 由于LODGING$MANAGER是一個非唯一性的索引,資料庫不能對它執行索引唯一掃描.
由于SQL傳回LODGING列,而它并不存在于LODGING$MANAGER索引中, 是以在索引範圍查詢後會執行一個通過ROWID通路表的操作.
WHERE子句中, 如果索引列所對應的值的第一個字元由通配符(WILDCARD)開始, 索引将不被采用.
如：SELECT LODGING
FROM LODGING
WHERE MANAGER LIKE ‘％HANMAN’;
在這種情況下，ORACLE将使用全表掃描.
27.基礎表的選擇
基礎表(Driving Table)是指被最先通路的表(通常以全表掃描的方式被通路). 根據優化器的不同, SQL語句中基礎表的選擇是不一樣的.
如果你使用的是CBO (COST BASED OPTIMIZER),優化器會檢查SQL語句中的每個表的實體大小,索引的狀态,然後選用花費最低的執行路徑.
如果你用RBO (RULE BASED OPTIMIZER) , 并且所有的連接配接條件都有索引對應, 在這種情況下, 基礎表就是FROM子句中列在最後的那個表.
舉例:
SELECT A.NAME, B.MANAGER
FROM WORKER A, LODGING B
WHERE A.LODGING = B.LODING;
由于LODGING表的LODING列上有一個索引, 而且WORKER表中沒有相比較的索引, WORKER表将被作為查詢中的基礎表.
28.多個平等的索引
當SQL語句的執行路徑可以使用分布在多個表上的多個索引時, ORACLE會同時使用多個索引并在運作時對它們的記錄進行合并, 檢索出僅對全部索引有效的記錄.
在ORACLE選擇執行路徑時,唯一性索引的等級高于非唯一性索引. 然而這個規則隻有當WHERE子句中索引列和常量比較才有效.如果索引列和其他表的索引類相比較. 這種子句在優化器中的等級是非常低的.
如果不同表中兩個想同等級的索引将被引用, FROM子句中表的順序将決定哪個會被率先使用. FROM子句中最後的表的索引将有最高的優先級.
如果相同表中兩個想同等級的索引将被引用, WHERE子句中最先被引用的索引将有最高的優先級.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME,
FROM EMP
WHERE DEPT_NO = 20 AND EMP_CAT = ‘A’;
這裡,DEPTNO索引将被最先檢索,然後同EMP_CAT索引檢索出的記錄進行合并. 執行路徑如下:
TABLE ACCESS BY ROWID ON EMP
AND-EQUAL
INDEX RANGE SCAN ON DEPT_IDX
INDEX RANGE SCAN ON CAT_IDX
29.等式比較和範圍比較
當WHERE子句中有索引列, ORACLE不能合并它們,ORACLE将用範圍比較.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT = ‘A’;
這裡隻有EMP_CAT索引被用到,然後所有的記錄将逐條與DEPTNO條件進行比較. 執行路徑如下:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON CAT_IDX
30.不明确的索引等級
當ORACLE無法判斷索引的等級高低差别,優化器将隻使用一個索引,它就是在WHERE子句中被列在最前面的.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME
FROM EMP
WHERE DEPTNO >; 20
AND EMP_CAT >; ‘A’;
這裡, ORACLE隻用到了DEPT_NO索引. 執行路徑如下:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON DEPT_IDX
譯者按:
我們來試一下以下這種情況:
SQL> select index_name, uniqueness from user_indexes where table_name = 'EMP';
INDEX_NAME UNIQUENES
------------------------------ ---------
EMPNO UNIQUE
EMPTYPE NONUNIQUE
SQL>; select * from emp where empno >;= 2 and emp_type = 'A' ;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPTYPE' (NON-UNIQUE)
雖然EMPNO是唯一性索引,但是由于它所做的是範圍比較, 等級要比非唯一性索引的等式比較低!
31.強制索引失效
如果兩個或以上索引具有相同的等級,你可以強制指令ORACLE優化器使用其中的一個(通過它,檢索出的記錄數量少).
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO + 0 = 10
AND EMP_TYPE || ‘’ = ‘A’
這是一種相當直接的提高查詢效率的辦法. 但是你必須謹慎考慮這種政策,一般來說,隻有在你希望單獨優化幾個SQL時才能采用它.
這裡有一個例子關于何時采用這種政策,
假設在EMP表的EMP_TYPE列上有一個非唯一性的索引而EMP_CLASS上沒有索引.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’
AND EMP_CLASS = ‘X’;
優化器會注意到EMP_TYPE上的索引并使用它. 這是目前唯一的選擇. 如果,一段時間以後, 另一個非唯一性建立在EMP_CLASS上,優化器必須對兩個索引進行選擇,在通常情況下,優化器将使用兩個索引并在他們的結果集合上執行排序及合并. 然而,如果其中一個索引（EMP_TYPE）接近于唯一性而另一個索引（EMP_CLASS）上有幾千個重複的值. 排序及合并就會成為一種不必要的負擔. 在這種情況下,你希望使優化器屏蔽掉EMP_CLASS索引.
用下面的方案就可以解決問題.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’ AND EMP_CLASS||’’ = ‘X’;
32.避免在索引列上使用計算．
WHERE子句中，如果索引列是函數的一部分．優化器将不使用索引而使用全表掃描．
舉例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 >; 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL >; 25000/12;
譯者按: 這是一個非常實用的規則，請務必牢記
33.自動選擇索引
如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性．在這種情況下，ORACLE将使用唯一性索引而完全忽略非唯一性索引．
舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ;
這裡，隻有EMPNO上的索引是唯一性的，是以EMPNO索引将用來檢索記錄．
TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX
34.避免在索引列上使用NOT
通常，我們要避免在索引列上使用NOT, NOT會産生在和在索引列上使用函數相同的影響. 當ORACLE”遇到”NOT,他就會停止使用索引轉而執行全表掃描.
舉例:
低效: (這裡,不使用索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE NOT = 0;
高效: (這裡,使用了索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE > 0;
需要注意的是,在某些時候, ORACLE優化器會自動将NOT轉化成相對應的關系操作符.
NOT > to <=
NOT >= to <
NOT < to >=
NOT <= to >
譯者按: 在這個例子中,作者犯了一些錯誤. 例子中的低效率SQL是不能被執行的.
我做了一些測試:
SQL>; select * from emp where NOT empno >; 1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPNO' (UNIQUE)
SQL>; select * from emp where empno <= 1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPNO' (UNIQUE)
兩者的效率完全一樣，也許這符合作者關于” 在某些時候, ORACLE優化器會自動将NOT轉化成相對應的關系操作符” 的觀點
35.用>=替代>
如果DEPTNO上有一個索引,
高效:
SELECT *
FROM EMP
WHERE DEPTNO >=4
低效:
SELECT *
FROM EMP
WHERE DEPTNO >3
兩者的差別在于, 前者DBMS将直接跳到第一個DEPT等于4的記錄而後者将首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于3的記錄.
36.用UNION替換OR (适用于索引列)
通常情況下, 用UNION替換WHERE子句中的OR将會起到較好的效果. 對索引列使用OR将造成全表掃描. 注意, 以上規則隻針對多個索引列有效. 如果有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
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 内部将以上轉換為
WHERE KEY1 = 10 AND ((NOT KEY1 = 10) AND KEY2 = 20)
譯者按: 下面的測試資料僅供參考: (a = 1003 傳回一條記錄 , b = 1 傳回1003條記錄)
SQL>; select * from unionvsor
2 where a = 1003 or b = 1;
1003 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'UNIONVSOR'
3 2 INDEX (RANGE SCAN) OF 'UB' (NON-UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'UNIONVSOR'
5 4 INDEX (RANGE SCAN) OF 'UA' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
144 consistent gets
0 physical reads
0 redo size
63749 bytes sent via SQL*Net to client
7751 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1003 rows processed
SQL>; select * from unionvsor
2 where b = 1 or a = 1003 ;
1003 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'UNIONVSOR'
3 2 INDEX (RANGE SCAN) OF 'UA' (NON-UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'UNIONVSOR'
5 4 INDEX (RANGE SCAN) OF 'UB' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
143 consistent gets
0 physical reads
0 redo size
63749 bytes sent via SQL*Net to client
7751 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1003 rows processed
SQL>; select * from unionvsor
2 where a = 1003
3 union
4 select * from unionvsor
5 where b = 1;
1003 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 UNION-ALL
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'UNIONVSOR'
4 3 INDEX (RANGE SCAN) OF 'UA' (NON-UNIQUE)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'UNIONVSOR'
6 5 INDEX (RANGE SCAN) OF 'UB' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
63735 bytes sent via SQL*Net to client
7751 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1003 rows processed
用UNION的效果可以從consistent gets和 SQL*NET的資料交換量的減少看出
37.用IN來替換OR
下面的查詢可以被更有效率的語句替換:
低效:
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);
譯者按: 這是一條簡單易記的規則，但是實際的執行效果還須檢驗，在ORACLE8i下，兩者的執行路徑似乎是相同的．
38.避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以為空的列，ORACLE将無法使用該索引 ．對于單列索引，如果列包含空值，索引中将不存在此記錄. 對于複合索引，如果每個列都為空，索引中同樣不存在此記錄. 如果至少有一個列不為空，則記錄存在于索引中．
舉例:
如果唯一性索引建立在表的A列和B列上, 并且表中存在一條記錄的A,B值為(123,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;
39. 總是使用索引的第一個列
如果索引是建立在多個列上, 隻有在它的第一個列(leading column)被where子句引用時,優化器才會選擇使用該索引.
譯者按:
這也是一條簡單而重要的規則. 見以下執行個體.
SQL>; create table multiindexusage ( inda number , indb number , descr varchar2(10));
Table created.
SQL>; create index multindex on multiindexusage(inda,indb);
Index created.
SQL>; set autotrace traceonly
SQL>; select * from multiindexusage where inda = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE'
2 1 INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE)
SQL>; select * from multiindexusage where indb = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE'
很明顯, 當僅引用索引的第二個列時,優化器使用了全表掃描而忽略了索引
40.ORACLE内部操作
當執行查詢時,ORACLE采用了内部的操作. 下表顯示了幾種重要的内部操作：
ORACLE Clause 内部操作
ORDER BY SORT ORDER BY
UNION UNION-ALL
MINUS MINUS
INTERSECT INTERSECT
DISTINCT,MINUS,INTERSECT,UNION SORT UNIQUE
MIN,MAX,COUNT SORT AGGREGATE
GROUP BY SORT GROUP BY
ROWNUM COUNT or COUNT STOPKEY
Queries involving Joins SORT JOIN,MERGE JOIN,NESTED LOOPS
CONNECT BY CONNECT BY
41.用UNION-ALL替換UNION (如果有可能的話)
當SQL語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合并, 然後在輸出最終結果前進行排序. 如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會是以得到提高.
舉例:
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
譯者按: 需要注意的是，UNION ALL 将重複輸出兩個結果集合中相同記錄. 是以各位還是要從業務需求分析使用UNION ALL的可行性.UNION 将對結果集合排序,這個操作會使用到SORT_AREA_SIZE這塊記憶體. 對于這塊記憶體的優化也是相當重要的. 下面的SQL可以用來查詢排序的消耗量
Select substr(name,1,25) "Sort Area Name", substr(value,1,15) "Value"
from v$sysstat
where name like 'sort%'
42.使用提示(Hints)
對于表的通路,可以使用兩種Hints：FULL 和 ROWID
FULL hint 告訴ORACLE使用全表掃描的方式通路指定表.
例如:
SELECT *
FROM EMP
WHERE EMPNO = 7893;
ROWID hint 告訴ORACLE使用TABLE ACCESS BY ROWID的操作通路表.
通常, 你需要采用TABLE ACCESS BY ROWID的方式特别是當通路大表的時候, 使用這種方式, 你需要知道ROIWD的值或者使用索引.
如果一個大表沒有被設定為緩存(CACHED)表而你希望它的資料在查詢結束是仍然停留在SGA中,你就可以使用CACHE hint 來告訴優化器把資料保留在SGA中. 通常CACHE hint 和 FULL hint 一起使用.
例如:
SELECT *
FROM WORK;
索引hint 告訴ORACLE使用基于索引的掃描方式. 你不必說明具體的索引名稱
例如:
SELECT LODGING
FROM LODGING
WHERE MANAGER = ‘BILL GATES’;
在不使用hint的情況下, 以上的查詢應該也會使用索引,然而,如果該索引的重複值過多而你的優化器是CBO, 優化器就可能忽略索引. 在這種情況下, 你可以用INDEX hint強制ORACLE使用該索引.
ORACLE hints 還包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等.
譯者按: 使用hint, 表示我們對ORACLE優化器預設的執行路徑不滿意,需要手工修改. 這是一個很有技巧性的工作. 我建議隻針對特定的,少數的SQL進行hint的優化. 對ORACLE的優化器還是要有信心(特别是CBO)
43.用WHERE替代ORDER BY
ORDER 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
EXPLAIN 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 也能使用索引! 這的确是個容易被忽視的知識點. 我們來驗證一下:
SQL>; select * from emp order by empno;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (FULL SCAN) OF 'EMPNO' (UNIQUE)
44.避免改變索引列的類型.
當比較不同資料類型的資料時, ORACLE自動對列進行簡單的類型轉換.
假設 EMPNO是一個數值類型的索引列.
SELECT …
FROM EMP
WHERE EMPNO = ‘123’
實際上,經過ORACLE類型轉換, 語句轉化為:
SELECT …
FROM EMP
WHERE EMPNO = TO_NUMBER(‘123’)
幸運的是,類型轉換沒有發生在索引列上,索引的用途沒有被改變.
現在,假設EMP_TYPE是一個字元類型的索引列.
SELECT …
FROM EMP
WHERE EMP_TYPE = 123
這個語句被ORACLE轉換為:
SELECT …
FROM EMP
WHERE TO_NUMBER(EMP_TYPE)=123
因為内部發生的類型轉換, 這個索引将不會被用到!
譯者按: 為了避免ORACLE對你的SQL進行隐式的類型轉換, 最好把類型轉換用顯式表現出來. 注意當字元和數值比較時, ORACLE會優先轉換數值類型到字元類型.
45.需要當心的WHERE子句
某些SELECT語句中的WHERE子句不使用索引. 這裡有一些例子.
在下面的例子裡, ‘!=’ 将不使用索引. 記住, 索引隻能告訴你什麼存在于表中, 而不能告訴你什麼不存在于表中.
不使用索引:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT !=0;
使用索引:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT >;0;
下面的例子中, ‘||’是字元連接配接函數. 就象其他函數那樣, 停用了索引.
不使用索引:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’;
使用索引:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = ‘AMEX’
AND ACCOUNT_TYPE=’ A’;
下面的例子中, ‘+’是數學函數. 就象其他數學函數那樣, 停用了索引.
不使用索引:
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_NAME);
使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME,’%’);
譯者按: 如果一定要對使用函數的列啟用索引, ORACLE新的功能: 基于函數的索引(Function-Based Index) 也許是一個較好的方案.
CREATE INDEX EMP_I ON EMP (UPPER(ename));
SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’;
46.連接配接多個掃描
如果你對一個列和一組有限的值進行比較, 優化器可能執行多次掃描并對結果進行合并連接配接.
舉例:
SELECT *
FROM LODGING
WHERE MANAGER IN (‘BILL GATES’,’KEN MULLER’);
優化器可能将它轉換成以下形式
SELECT *
FROM LODGING
WHERE MANAGER = ‘BILL GATES’
OR MANAGER = ’KEN MULLER’;
當選擇執行路徑時, 優化器可能對每個條件采用LODGING$MANAGER上的索引範圍掃描. 傳回的ROWID用來通路LODGING表的記錄 (通過TABLE ACCESS BY ROWID 的方式). 最後兩組記錄以連接配接(CONCATENATION)的形式被組合成一個單一的集合.
Explain Plan :
SELECT STATEMENT Optimizer=CHOOSE
CONCATENATION
TABLE ACCESS (BY INDEX ROWID) OF LODGING
INDEX (RANGE SCAN ) OF LODGING$MANAGER (NON-UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF LODGING
INDEX (RANGE SCAN ) OF LODGING$MANAGER (NON-UNIQUE)
譯者按: 本節和第37節似乎有沖突之處.
47.CBO下使用更具選擇性的索引
基于成本的優化器(CBO, Cost-Based Optimizer)對索引的選擇性進行判斷來決定索引的使用是否能提高效率. 如果索引有很高的選擇性, 那就是說對于每個不重複的索引鍵值,隻對應數量很少的記錄. 比如, 表中共有100條記錄而其中有80個不重複的索引鍵值. 這個索引的選擇性就是80/100 = 0.8 . 選擇性越高, 通過索引鍵值檢索出的記錄就越少. 如果索引的選擇性很低, 檢索資料就需要大量的索引範圍查詢操作和ROWID 通路表的操作. 也許會比全表掃描的效率更低.
譯者按:
下列經驗請參閱:
a.如果檢索資料量超過30%的表中記錄數.使用索引将沒有顯著的效率提高.
b.在特定情況下, 使用索引也許會比全表掃描慢, 但這是同一個數量級上的差別. 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍!
48.避免使用耗費資源的操作
帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎執行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執行兩次排序. 例如,一個UNION查詢,其中每個查詢都帶有GROUP BY子句, GROUP BY會觸發嵌入排序(NESTED SORT) ; 這樣, 每個查詢需要執行一次排序, 然後在執行UNION時, 又一個唯一排序(SORT UNIQUE)操作被執行而且它隻能在前面的嵌入排序結束後才能開始執行. 嵌入的排序的深度會大大影響查詢的效率. 通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫.
譯者按: 如果你的資料庫的SORT_AREA_SIZE調配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強
49.優化GROUP BY
提高GROUP BY 語句的效率, 可以通過将不需要的記錄在GROUP BY 之前過濾掉.下面兩個查詢傳回相同結果但第二個明顯就快了許多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP JOB
HAVING JOB = ‘PRESIDENT’
OR JOB = ‘MANAGER’
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT’
OR JOB = ‘MANAGER’
GROUP JOB
譯者按: 本節和14節相同. 可略過.
50.使用日期
當使用日期是,需要注意如果有超過5位小數加到日期上, 這個日期會進到下一天!
例如:
1.
SELECT TO_DATE(‘01-JAN-93’+.99999)
FROM DUAL;
Returns: ’01-JAN-93 23:59:59’
2.
SELECT TO_DATE(‘01-JAN-93’+.999999)
FROM DUAL;
Returns: ’02-JAN-93 00:00:00’
譯者按: 雖然本節和SQL性能優化沒有關系, 但是作者的功力可見一斑
51.使用顯式的遊标(CURSORs)
使用隐式的遊标,将會執行兩次操作. 第一次檢索記錄, 第二次檢查TOO MANY ROWS 這個exception . 而顯式遊标不執行第二次操作.
52.優化EXPORT和IMPORT
使用較大的BUFFER(比如10MB , 10,240,000)可以提高EXPORT和IMPORT的速度. ORACLE将盡可能地擷取你所指定的記憶體大小,即使在記憶體不滿足,也不會報錯.這個值至少要和表中最大的列相當,否則列值會被截斷.
譯者按: 可以肯定的是, 增加BUFFER會大大提高EXPORT , IMPORT的效率. (曾經碰到過一個CASE, 增加BUFFER後,IMPORT/EXPORT快了10倍!) 作者可能犯了一個錯誤: “這個值至少要和表中最大的列相當,否則列值會被截斷. “ 其中最大的列也許是指最大的記錄大小. 關于EXPORT/IMPORT的優化,CSDN論壇中有一些總結性的貼子,比如關于BUFFER參數, COMMIT參數等等, 詳情請查.
53. 分離表和索引
總是将你的表和索引建立在不同的表空間内(TABLESPACES). 決不要将不屬于ORACLE内部系統的對象存放到SYSTEM表空間裡. 同時,確定資料表空間和索引表空間置于不同的硬碟上.
譯者按: “同時,確定資料表空間和索引表空間置與不同的硬碟上.”可能改為如下更為準确 “同時,確定資料表空間和索引表空間置與不同的硬碟控制卡控制的硬碟上.”
原文連結：http://bbs.chinaunix.net/viewthread.php?tid=214182
轉載請注明作者名及原文出處
![IntelliJ IDEA 2020.1.2 設定多個project共享database插件[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)