ORACLE數(shù)據(jù)庫(kù)操作手冊(cè)21

1、.：.；ORACLE 數(shù)據(jù)庫(kù)操作手冊(cè)2.0中國(guó)通訊集團(tuán)公司安徽計(jì)費(fèi)業(yè)務(wù)部7年11月修正記錄版本日期編輯者編輯內(nèi)容1.02006/2/10見春蕾構(gòu)成初稿1.12007/8/31見春蕾根據(jù)實(shí)踐情況修訂部分章節(jié)2.02007/11/05見春蕾一、在第一章數(shù)據(jù)庫(kù)運(yùn)用本卷須知中添加了關(guān)于批量更新數(shù)據(jù)的大事務(wù)分次提交的要求、客戶端的配置方法、修正密碼方法；二、在第二章SQL編寫本卷須知中添加了關(guān)于提示(Hints)的運(yùn)用。三、添加了第四章跟蹤SQL執(zhí)行方案，論述了相關(guān)的實(shí)際知識(shí)和SQL執(zhí)行方案的跟蹤方法。目錄 TOC o 1-3 h z u HYPERLINK l _Toc182711276 ORACLE

2、 數(shù)據(jù)庫(kù)操作手冊(cè)2.0 PAGEREF _Toc182711276 h 1 HYPERLINK l _Toc182711277 第一章數(shù)據(jù)庫(kù)運(yùn)用本卷須知 PAGEREF _Toc182711277 h 5 HYPERLINK l _Toc182711278 二、不運(yùn)用數(shù)據(jù)庫(kù)時(shí)請(qǐng)及時(shí)封鎖數(shù)據(jù)庫(kù)銜接，但是也不能頻繁的銜接和斷開 PAGEREF _Toc182711278 h 5 HYPERLINK l _Toc182711279 三、執(zhí)行了DML操作，請(qǐng)按業(yè)務(wù)規(guī)那么，不要忘記執(zhí)行COMMIT或ROLLBACK。 PAGEREF _Toc182711279 h 5 HYPERLINK l _Toc1

3、82711280 四、假設(shè)是查詢和統(tǒng)計(jì)不涉及到當(dāng)天的業(yè)務(wù)時(shí)，不要在消費(fèi)環(huán)境里操作，在BCV庫(kù)中操作。BCV每天晚上12點(diǎn)同步一次，數(shù)據(jù)和用戶口令、密碼和消費(fèi)環(huán)境一樣。 PAGEREF _Toc182711280 h 5 HYPERLINK l _Toc182711281 五、關(guān)聯(lián)表都很大的查詢和統(tǒng)計(jì)也盡量用BCV庫(kù)。 PAGEREF _Toc182711281 h 6 HYPERLINK l _Toc182711282 六、消費(fèi)環(huán)境營(yíng)業(yè)時(shí)間特別是營(yíng)業(yè)頂峰時(shí)間，目前是上午8:00-10:00，下午3:00-4:00制止做大數(shù)據(jù)量的查詢和統(tǒng)計(jì)，每個(gè)查詢的執(zhí)行時(shí)間要控制在分鐘內(nèi)。 PAGEREF _

4、Toc182711282 h 6 HYPERLINK l _Toc182711283 七、不要執(zhí)行索引和表的信息的搜集。 PAGEREF _Toc182711283 h 6 HYPERLINK l _Toc182711284 八、編寫程序的時(shí)候，留意語(yǔ)句規(guī)范，盡量運(yùn)用變量綁定，減少共享池的運(yùn)用。 PAGEREF _Toc182711284 h 6 HYPERLINK l _Toc182711285 九、按照規(guī)范要求編寫pl/sql等程序，留意事務(wù)的提交、回滾和對(duì)各種異常情況的處置。 PAGEREF _Toc182711285 h 6 HYPERLINK l _Toc182711286 十、要查

5、看表字段名或隨機(jī)的少量數(shù)據(jù)時(shí)候，運(yùn)用desc、也可以運(yùn)用where1=2或者rowcountn來查看，而盡量不要直接執(zhí)行select * from tablename，然后kill會(huì)話。 PAGEREF _Toc182711286 h 6 HYPERLINK l _Toc182711287 十一、盡量運(yùn)用索引，防止出現(xiàn)全表掃描，性能影響比單機(jī)更大。 PAGEREF _Toc182711287 h 6 HYPERLINK l _Toc182711288 十二、對(duì)分區(qū)表建立索引時(shí)，運(yùn)用local選項(xiàng)。 PAGEREF _Toc182711288 h 6 HYPERLINK l _Toc182711

6、289 十三、不要在事務(wù)中引入Trigger，建議在事務(wù)中實(shí)現(xiàn)。 PAGEREF _Toc182711289 h 6 HYPERLINK l _Toc182711290 十四、批量更新數(shù)據(jù)的大的事務(wù)分次提交。 PAGEREF _Toc182711290 h 6 HYPERLINK l _Toc182711291 十五、客戶端的配置。 PAGEREF _Toc182711291 h 7 HYPERLINK l _Toc182711292 十六、修正密碼。 PAGEREF _Toc182711292 h 7 HYPERLINK l _Toc182711293 第二章SQL編寫本卷須知 PAGERE

7、F _Toc182711293 h 9 HYPERLINK l _Toc182711294 一、SELECT 子句中防止運(yùn)用* PAGEREF _Toc182711294 h 9 HYPERLINK l _Toc182711295 二、查找總記錄數(shù)時(shí)，盡量不要用count(*)，而要指定一個(gè)有索引的字段。 PAGEREF _Toc182711295 h 9 HYPERLINK l _Toc182711296 三、將大的歷史表創(chuàng)建為分區(qū)表，便于數(shù)據(jù)轉(zhuǎn)儲(chǔ)和刪除。 PAGEREF _Toc182711296 h 9 HYPERLINK l _Toc182711297 四、運(yùn)用分區(qū)表進(jìn)展查詢時(shí)，盡量把

8、分區(qū)鍵作為查詢條件的第一個(gè)條件。 PAGEREF _Toc182711297 h 9 HYPERLINK l _Toc182711298 五、Sequence采用cache/noorder，假設(shè)在運(yùn)用sequence上的列建索引，建議加大cache值。 PAGEREF _Toc182711298 h 9 HYPERLINK l _Toc182711299 六、在FROM子句中包含多個(gè)表的情況下，選擇記錄條數(shù)最少的表作為根底表，放在FROM子句的最后面。 PAGEREF _Toc182711299 h 9 HYPERLINK l _Toc182711300 七、WHERE子句中的銜接順序 PAG

9、EREF _Toc182711300 h 10 HYPERLINK l _Toc182711301 八、在需求無條件刪除表中數(shù)據(jù)時(shí)，用truncate替代delete。 PAGEREF _Toc182711301 h 11 HYPERLINK l _Toc182711302 九、語(yǔ)句中盡量運(yùn)用表的索引字段，防止做大表的全表掃描。 PAGEREF _Toc182711302 h 11 HYPERLINK l _Toc182711303 十、 帶通配符%的like語(yǔ)句 PAGEREF _Toc182711303 h 11 HYPERLINK l _Toc182711304 十一、用EXISTS替代

10、IN PAGEREF _Toc182711304 h 11 HYPERLINK l _Toc182711305 十二、用NOT EXISTS替代NOT IN PAGEREF _Toc182711305 h 12 HYPERLINK l _Toc182711306 十三、盡能夠的用UNION-ALL 交換UNION PAGEREF _Toc182711306 h 12 HYPERLINK l _Toc182711307 十四、Order by語(yǔ)句建議 PAGEREF _Toc182711307 h 13 HYPERLINK l _Toc182711308 十五、防止運(yùn)用NOT PAGEREF _

11、Toc182711308 h 13 HYPERLINK l _Toc182711309 十六、運(yùn)用DECODE函數(shù)來減少處置時(shí)間 PAGEREF _Toc182711309 h 14 HYPERLINK l _Toc182711310 十七、刪除反復(fù)記錄 PAGEREF _Toc182711310 h 14 HYPERLINK l _Toc182711311 十八、假設(shè)可以運(yùn)用where條件，盡量不要在having中限制數(shù)據(jù) PAGEREF _Toc182711311 h 14 HYPERLINK l _Toc182711312 十九、盡量不要使數(shù)據(jù)排序 PAGEREF _Toc1827113

12、12 h 14 HYPERLINK l _Toc182711313 二十、運(yùn)用提示(Hints) PAGEREF _Toc182711313 h 15 HYPERLINK l _Toc182711314 第三章oracle和sybase的SQL區(qū)別 PAGEREF _Toc182711314 h 15 HYPERLINK l _Toc182711315 一、大小寫 PAGEREF _Toc182711315 h 15 HYPERLINK l _Toc182711316 二、限制記錄數(shù)量 PAGEREF _Toc182711316 h 15 HYPERLINK l _Toc182711317 三

13、、列的選擇 PAGEREF _Toc182711317 h 16 HYPERLINK l _Toc182711318 四、銜接 PAGEREF _Toc182711318 h 16 HYPERLINK l _Toc182711319 五、字符串函數(shù) PAGEREF _Toc182711319 h 16 HYPERLINK l _Toc182711320 六、日期函數(shù) PAGEREF _Toc182711320 h 16 HYPERLINK l _Toc182711321 七、數(shù)據(jù)類型轉(zhuǎn)換函數(shù): PAGEREF _Toc182711321 h 17 HYPERLINK l _Toc1827113

14、22 八、空值替代函數(shù): PAGEREF _Toc182711322 h 17 HYPERLINK l _Toc182711323 九、sybase的where語(yǔ)句執(zhí)行 正那么符號(hào)，但是oracle9i不支持。 PAGEREF _Toc182711323 h 17 HYPERLINK l _Toc182711324 十、數(shù)字取舍 PAGEREF _Toc182711324 h 17 HYPERLINK l _Toc182711325 第四章 跟蹤SQL執(zhí)行方案 PAGEREF _Toc182711325 h 18 HYPERLINK l _Toc182711326 一、實(shí)際 PAGEREF _

15、Toc182711326 h 18 HYPERLINK l _Toc182711327 一ORACLE優(yōu)化器 PAGEREF _Toc182711327 h 18 HYPERLINK l _Toc182711328 二訪問TABLE的方式 PAGEREF _Toc182711328 h 18 HYPERLINK l _Toc182711329 三 索引訪問方式 PAGEREF _Toc182711329 h 19 HYPERLINK l _Toc182711330 二、SET TRACE跟蹤sql執(zhí)行方案 PAGEREF _Toc182711330 h 19第一章數(shù)據(jù)庫(kù)運(yùn)用本卷須知一、對(duì)BOS

16、S1.5營(yíng)帳庫(kù)，營(yíng)業(yè)網(wǎng)址嚴(yán)厲按照要求進(jìn)展配置，不可隨意改換。營(yíng)業(yè)網(wǎng)址要求按照下面方式進(jìn)展分配配置，假設(shè)隨意改換，會(huì)添加營(yíng)業(yè)主機(jī)間的數(shù)據(jù)交互，影響數(shù)據(jù)庫(kù)性能，降低營(yíng)業(yè)任務(wù)效率。合肥、六安、阜陽(yáng)、宿州、亳州、淮北、黃山、銅陵配置: 10.147.132.5:7001/WebRoot/login.jsp或者main.webA1.amcc/WebRoot/login.jsp蕪湖、蚌埠、淮南、馬鞍山、安慶、滁州、宣城、巢湖、池州，配置如下:10.147.132.6:8001/WebRoot/login.jsp或者main.webB1.amcc/WebRoot/login.jsp二、不運(yùn)用數(shù)據(jù)庫(kù)時(shí)請(qǐng)及時(shí)封

17、鎖數(shù)據(jù)庫(kù)銜接，但是也不能頻繁的銜接和斷開數(shù)據(jù)庫(kù)銜接也是數(shù)據(jù)庫(kù)的珍貴資源，數(shù)據(jù)庫(kù)支持的數(shù)據(jù)庫(kù)銜接有限，當(dāng)不需求運(yùn)用數(shù)據(jù)庫(kù)時(shí)，請(qǐng)“優(yōu)雅的退出數(shù)據(jù)庫(kù)吧，假設(shè)能正常退出，請(qǐng)別“終了義務(wù)或KILL -9。假設(shè)正在執(zhí)行SQL的時(shí)候忽然異常終端，請(qǐng)聯(lián)絡(luò)數(shù)據(jù)庫(kù)管理員檢查處置，以防止數(shù)據(jù)庫(kù)不斷占用該SQL相關(guān)資源。三、執(zhí)行了DML操作，請(qǐng)按業(yè)務(wù)規(guī)那么，不要忘記執(zhí)行COMMIT或ROLLBACK。不要只執(zhí)行語(yǔ)句，而不控制事務(wù)。當(dāng)他執(zhí)行一條DML語(yǔ)句時(shí)，數(shù)據(jù)庫(kù)會(huì)為他分配鎖、回滾段、REDO LOG BUFFER等資源。事務(wù)終了后，這些資源才干得以釋放。四、假設(shè)是查詢和統(tǒng)計(jì)不涉及到當(dāng)天的業(yè)務(wù)時(shí)，不要在消費(fèi)環(huán)境里操作，

18、在BCV庫(kù)中操作。BCV每天晚上12點(diǎn)同步一次，數(shù)據(jù)和用戶口令、密碼和消費(fèi)環(huán)境一樣。bcv是一個(gè)節(jié)點(diǎn)的數(shù)據(jù)庫(kù)，一切的地市的查詢的銜接配置是同一個(gè)，如下： YZDBBCV = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.153.192.45)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = yzdb) ) )五、關(guān)聯(lián)表都很大的查詢和統(tǒng)計(jì)也盡量用BCV庫(kù)。六、消費(fèi)環(huán)境營(yíng)業(yè)時(shí)間特別是營(yíng)業(yè)頂峰時(shí)間，目前是上午8:00-10:00，下午3:00-4:00制止做大數(shù)據(jù)

19、量的查詢和統(tǒng)計(jì)，每個(gè)查詢的執(zhí)行時(shí)間要控制在分鐘內(nèi)。七、不要執(zhí)行索引和表的信息的搜集。八、編寫程序的時(shí)候，留意語(yǔ)句規(guī)范，盡量運(yùn)用變量綁定，減少共享池的運(yùn)用。九、按照規(guī)范要求編寫pl/sql等程序，留意事務(wù)的提交、回滾和對(duì)各種異常情況的處置。十、要查看表字段名或隨機(jī)的少量數(shù)據(jù)時(shí)候，運(yùn)用desc、也可以運(yùn)用where1=2或者rowcount 50000AND JOB = MANAGERAND 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);(高效,執(zhí)行時(shí)間10.6秒)SELECT *FROM EMP EWHERE 25 50000AND JOB =

20、MANAGER;八、在需求無條件刪除表中數(shù)據(jù)時(shí)，用truncate替代delete。九、語(yǔ)句中盡量運(yùn)用表的索引字段，防止做大表的全表掃描。例如Where子句中有聯(lián)接的列，即使最后的聯(lián)接值為一個(gè)靜態(tài)值，也不會(huì)運(yùn)用索引。 select * from employeewhere first_name|last_name =Beill Cliton; 這條語(yǔ)句沒有運(yùn)用基于last_name創(chuàng)建的索引。 當(dāng)采用下面這種SQL語(yǔ)句的編寫，Oracle系統(tǒng)就可以采用基于last_name創(chuàng)建的索引。 Select * from employee where first_name =Beill and las

21、t_name =Cliton; 十、 帶通配符%的like語(yǔ)句例如SQL語(yǔ)句： select * from employee where last_name like %cliton%; 由于通配符%在搜索詞首出現(xiàn)，所以O(shè)racle系統(tǒng)不運(yùn)用last_name的索引。通配符如此運(yùn)用會(huì)降低查詢速度。當(dāng)通配符出如今字符串其他位置時(shí)，優(yōu)化器就能利用索引。在下面的查詢中索引得到了運(yùn)用： select * from employee where last_name like c%;十一、用EXISTS替代IN 在許多基于根底表的查詢中,為了滿足一個(gè)條件,往往需求對(duì)另一個(gè)表進(jìn)展聯(lián)接.在這種情況下, 運(yùn)用E

22、XISTS(或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在子查詢中,NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并. 無論在

23、哪種情況下,NOT IN都是最低效的 (由于它對(duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷). 為了防止運(yùn)用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

24、 (方法二: 最高效) SELECT . FROM EMP E WHERE NOT EXISTS (SELECT X FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = A);十三、盡能夠的用UNION-ALL 交換UNION 當(dāng)SQL語(yǔ)句需求UNION兩個(gè)查詢結(jié)果集合時(shí),這兩個(gè)結(jié)果集合會(huì)以UNION-ALL的方式被合并, 然后在輸出最終結(jié)果前進(jìn)展排序. 假設(shè)用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會(huì)因此得到提高. 舉例: 低效： SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT

25、_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-DE

26、C-95十四、Order by語(yǔ)句建議ORDER BY語(yǔ)句決議了Oracle如何將前往的查詢結(jié)果排序。Order by語(yǔ)句對(duì)要排序的列沒有什么特別的限制，也可以將函數(shù)參與列中象聯(lián)接或者附加等。任何在Order by語(yǔ)句的非索引項(xiàng)或者有計(jì)算表達(dá)式都將降低查詢速度。 仔細(xì)檢查order by語(yǔ)句以找出非索引項(xiàng)或者表達(dá)式，它們會(huì)降低性能。處理這個(gè)問題的方法就是重寫order by語(yǔ)句以運(yùn)用索引，也可以為所運(yùn)用的列建立另外一個(gè)索引，同時(shí)應(yīng)絕對(duì)防止在order by子句中運(yùn)用表達(dá)式。十五、防止運(yùn)用NOT 在查詢時(shí)經(jīng)常在where子句運(yùn)用一些邏輯表達(dá)式，如大于、小于、等于以及不等于等等，也可以運(yùn)用and與

27、、or或以及not非。NOT可用來對(duì)任何邏輯運(yùn)算符號(hào)取反。下面是一個(gè)NOT子句的例子： . where not (status =VALID) 假設(shè)要運(yùn)用NOT，那么應(yīng)在取反的短語(yǔ)前面加上括號(hào)，并在短語(yǔ)前面加上NOT運(yùn)算符。NOT運(yùn)算符包含在另外一個(gè)邏輯運(yùn)算符中，這就是不等于運(yùn)算符。換句話說，即使不在查詢where子句中顯式地參與NOT詞，NOT仍在運(yùn)算符中，見下例： . where status INVALID; 再看下面這個(gè)例子： select * from employee where salary3000; 對(duì)這個(gè)查詢，可以改寫為不運(yùn)用NOT： select * from employ

28、ee where salary3000; 雖然這兩種查詢的結(jié)果一樣，但是第二種查詢方案會(huì)比第一種查詢方案更快些。第二種查詢?cè)试SOracle對(duì)salary列運(yùn)用索引，而第一種查詢那么不能運(yùn)用索引。十六、運(yùn)用DECODE函數(shù)來減少處置時(shí)間 運(yùn)用DECODE函數(shù)可以防止反復(fù)掃描一樣記錄或反復(fù)銜接一樣的表. 例如: SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0020 AND ENAME LIKESMITH%; SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0030 AND ENAME LIKES

29、MITH%; 可以用DECODE函數(shù)高效地得到一樣結(jié)果 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函數(shù)也可以運(yùn)用于GROUP BY 和ORDER BY子句中.十七、刪除反復(fù)記

30、錄DELETE FROM EMP E WHERE E.ROWID (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO); 十八、假設(shè)可以運(yùn)用where條件，盡量不要在having中限制數(shù)據(jù)十九、盡量不要使數(shù)據(jù)排序引起排序的條件- Order by- Group by- Union,intersect,minus- Distinct二十、運(yùn)用提示(Hints)對(duì)于表的訪問,可以運(yùn)用兩種Hints：FULL 和 ROWID FULL hint 通知ORACLE運(yùn)用全表掃描的方式訪問指定表. 例如: SELECT /*+ FULL(EM

31、P) */ * FROM EMP WHERE EMPNO = 7893; ROWID hint 通知ORACLE運(yùn)用TABLE ACCESS BY ROWID的操作訪問表. 通常, 他需求采用TABLE ACCESS BY ROWID的方式特別是當(dāng)訪問大表的時(shí)候, 運(yùn)用這種方式, 他需求知道ROIWD的值或者運(yùn)用索引。 假設(shè)一個(gè)大表沒有被設(shè)定為緩存(CACHED)表而他希望它的數(shù)據(jù)在查詢終了是依然停留在SGA中,他就可以運(yùn)用CACHE hint 來通知優(yōu)化器把數(shù)據(jù)保管在SGA中。 通常CACHE hint 和 FULL hint 一同運(yùn)用。 例如: SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ * FROM WORK; 索引hint 通知ORACLE運(yùn)用基于索引的掃描方式. 他不用闡明詳細(xì)的索引稱號(hào) 例如: SELECT /*+ INDEX(a index_name) */ LODGING FROM LODGING aWHERE MANAGER = BILL GATES; ORACLE hints 還包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等?？梢愿鶕?jù)詳細(xì)情