Oracle常見問題集(三)
151. 如何監控 SGA 中字典緩衝區的命中率？
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100
"miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
152. 如何監控 SGA 中共用緩存區的命中率，應該小於1% ？
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit
radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
153. 如何顯示所有資料庫物件的類別和大小？
select count(name) num_instances ,type ,sum(source_size)
source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size
,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size)
+sum(error_size) size_required
from dba_object_size
group by type order by 2;
154. 監控 SGA 中重做日誌緩存區的命中率，應該小於1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
155. 監控記憶體和硬碟的排序比率，最好使它小於 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)',
'sorts (disk)');
156. 如何監控當前資料庫誰在運行什麽SQL語句？
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
157. 如何監控字典緩衝區？
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM
V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW
CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE
EXECUTING" FROM V$LIBRARYCACHE;
後者除以前者,此比率小於1%,接近0%爲好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY
CACHE GET MISSES"
FROM V$ROWCACHE
158. 監控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大於0.5時，參數需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where
type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers時，參數需加大
159. 如何知道當前用戶的ID號
SQL>SHOW USER;
OR
SQL>select user from dual;
160. 如何查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY
segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP
BY segment_name);
162. 如何知道表在表空間中的存儲情況
select segment_name,sum(bytes),count(*) ext_quan from dba_extents
where
tablespace_name='&tablespace_name' and segment_type='TABLE' group
by tablespace_name,segment_name;
163. 如何知道索引在表空間中的存儲情況
select segment_name,count(*) from dba_extents where
segment_type='INDEX' and owner='&owner'
group by segment_name;
164、如何知道使用CPU多的用戶session
11是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40)
prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by
value desc;
165. 如何知道監聽器日誌文件
以8I爲例
$Oracle_HOME/NETWORK/LOG/LISTENER.LOG
166. 如何知道監聽器參數文件
以8I爲例
$ORACLE_HOME/NETWORK/ADMIN/LISTENER.ORA
167. 如何知道TNS 連接文件
以8I爲例
$ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA
168. 如何知道Sql*Net 環境文件
以8I爲例
$ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA
169. 如何知道警告日誌文件
以8I爲例
$ORACLE_HOME/ADMIN/SID/BDUMP/SIDALRT.LOG
170. 如何知道基本結構
以8I爲例
$ORACLE_HOME/RDBMS/ADMIN/STANDARD.SQL
171. 如何知道建立資料字典視圖
以8I爲例
$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL
172. 如何知道建立審計用資料字典視圖
以8I爲例
$ORACLE_HOME/RDBMS/ADMIN/CATAUDIT.SQL
173. 如何知道建立快照用資料字典視圖
以8I爲例
$ORACLE_HOME/RDBMS/ADMIN/CATSNAP.SQL
本講主要講的是SQL語句的優化方法!
主要基於ORACLE9I的.
174. /*+ALL_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE
EMP_NO='CCBZZP';
175. /*+FIRST_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳回應時間,使資源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE
EMP_NO='CCBZZP';
176. /*+CHOOSE*/
表明假如資料字典中有訪問表的統計資訊,將基於開銷的優化方法,並獲得最佳的吞吐量;
表明假如資料字典中沒有訪問表的統計資訊,將基於規則開銷的優化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE
EMP_NO='CCBZZP';
177. /*+RULE*/
表明對語句塊選擇基於規則的優化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE
EMP_NO='CCBZZP';
178. /*+FULL(TABLE)*/
表明對表選擇全局掃描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE
EMP_NO='CCBZZP';
179. /*+ROWID(TABLE)*/
提示明確表明對指定表根據ROWID進行訪問.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE
ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='CCBZZP';
180. /*+CLUSTER(TABLE)*/
提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇物件有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
181. /*+INDEX(TABLE INDEX_NAME)*/
表明對表選擇索引的掃描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE
FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
182. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明對表選擇索引昇冪的掃描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE
DPT_NO='CCBZZP';
183. /*+INDEX_COMBINE*/
爲指定表選擇點陣圖訪問路經,假如INDEX_COMBINE中沒有提供作爲參數的索引,將選擇出點陣圖索引的
布林組合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM
BSEMPMS
WHERE SAL<5000000 AND HIREDATE<SYSDATE;< SPAN>
184. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
提示明確命令優化器使用索引作爲訪問路徑.
例如:
SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL<60000;
185. /*+INDEX_DESC(TABLE INDEX_NAME)*/
表明對表選擇索引降冪的掃描方法.
例如:
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE
DPT_NO='CCBZZP';
186. /*+INDEX_FFS(TABLE INDEX_NAME)*/
對指定的表執行快速全索引掃描,而不是全表掃描的辦法.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE
DPT_NO='TEC305';
187. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
提示明確進行執行規劃的選擇,將幾個單列索引的掃描合起來.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM
BSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';
188. /*+USE_CONCAT*/
對查詢中的WHERE後面的OR條件進行轉換爲UNION ALL的組合查詢.
例如:
SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND
SEX='M';
189. /*+NO_EXPAND*/
對於WHERE後面的OR 或者IN-LIST的查詢語句,NO_EXPAND將阻止其基於優化器對其進行擴展.
例如:
SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND
SEX='M';
190. /*+NOWRITE*/
禁止對查詢塊的查詢重寫操作.
191. /*+REWRITE*/
可以將視圖作爲參數.
192. /*+MERGE(TABLE)*/
能夠對視圖的各個查詢進行相應的合併.
例如:
SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A
(SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE
A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;
193. /*+NO_MERGE(TABLE)*/
對於有可合併的視圖不再合併.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS
A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE
A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;
194. /*+ORDERED*/
根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連接.
例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2
B,TABLE3 C
WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
195. /*+USE_NL(TABLE)*/
將指定表與嵌套的連接的行源進行連接,並把指定表作爲內部表.
例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/
BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS
WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
196. /*+USE_MERGE(TABLE)*/
將指定的表與其他行源通過合併排序連接方式連接起來.
例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS
WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
197. /*+USE_HASH(TABLE)*/
將指定的表與其他行源通過哈希連接方式連接起來.
例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
198. /*+DRIVING_SITE(TABLE)*/
強制與ORACLE所選擇的位置不同的表進行查詢執行.
例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE
BSEMPMS.DPT_NO=DEPT.DPT_NO;
199. /*+LEADING(TABLE)*/
將指定的表作爲連接次序中的首表.
200. /*+CACHE(TABLE)*/
當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
201. /*+NOCACHE(TABLE)*/
當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
202. /*+APPEND*/
直接插入到表的最後,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
203. /*+NOAPPEND*/
通過在插入語句生存期內停止並行模式來啓動常規插入.
insert /*+noappend*/ into test1 select * from test4 ;
ORACLE優化器
. 選用適合的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. 兩個語句所指的物件必須完全相同:
例如:
用戶 物件名 如何訪問
Jack sal_limit private synonym
Work_city public synonym
Plant_detail public synonym
Jill sal_limit private synonym
Work_city public synonym
Plant_detail table owner
考慮一下下列SQL語句能否在這兩個用戶之間共用.
SQL
能否共用
原因
select max(sal_cap) from sal_limit;
不能
每個用戶都有一個private synonym - sal_limit , 它們是不同的物件
select count(*0 from work_city where sdesc like 'NEW%';
能
兩個用戶訪問相同的物件public synonym - work_city
select a.sdesc,b.location from work_city a , plant_detail b where
a.city_id = b.city_id
不能
用戶jack 通過private synonym訪問plant_detail 而jill 是表的所有者,物件不同.
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)
