ORACLE數(shù)據(jù)庫操作手冊210

1、ORACLE 數(shù)據(jù)庫庫操作手冊2.0中國通信集團(tuán)公司安徽徽有限公司計(jì)費(fèi)業(yè)務(wù)部7年11月修改記錄版本日期編輯者編輯內(nèi)容1.02006/2/10見春蕾形成初稿1.12007/8/31見春蕾根據(jù)實(shí)際情況修訂部分分章節(jié)2.02007/11/05見春蕾一、在第一章（數(shù)據(jù)庫庫使用注意事項(xiàng)）中中增加了關(guān)于批量量更新數(shù)據(jù)的大事事務(wù)分次提交的要要求、客戶端的配配置方法、修改密密碼方法；二、在第二章（SQL編編寫注意事項(xiàng)）中中增加了關(guān)于提示示(Hints)的使用。三、增加了第四章（跟跟蹤SQL執(zhí)行計(jì)計(jì)劃），闡述了相相關(guān)的理論知識(shí)和和SQL執(zhí)行計(jì)劃劃的跟蹤方法。目錄TOC o 1-3 h z u HYPERLINK

2、 l _Toc ORACLE 數(shù)據(jù)庫庫操作手冊2.0 PAGEREF _Toc h 1f。 HYPERLINK l _Toc 第一章數(shù)據(jù)庫使用注注意事項(xiàng) PAGEREF _Toc h 5b。 HYPERLINK l _Toc 二、不使用數(shù)據(jù)庫時(shí)請請及時(shí)關(guān)閉數(shù)據(jù)庫庫連接，但是也不不能頻繁的連接和和斷開 PAGEREF _Toc h 5F。 HYPERLINK l _Toc 三、執(zhí)行了DML操作作，請按業(yè)務(wù)規(guī)則則，不要忘記執(zhí)行行COMMIT或ROLLBACK。 PAGEREF _Toc h 5l。 HYPERLINK l _Toc 四、如果是查詢和統(tǒng)計(jì)計(jì)不涉及到當(dāng)天的的業(yè)務(wù)時(shí)，不要在在生產(chǎn)環(huán)境里操

3、作作，在BCV庫中操作。BCV每天晚上12點(diǎn)同步一次，數(shù)數(shù)據(jù)和用戶口令、密碼和生產(chǎn)環(huán)境境相同。 PAGEREF _Toc h 5i。 HYPERLINK l _Toc 五、關(guān)聯(lián)表都很大的查查詢和統(tǒng)計(jì)也盡量量用BCV庫。 PAGEREF _Toc h 6P。 HYPERLINK l _Toc 六、生產(chǎn)環(huán)境營業(yè)時(shí)間間（特別是營業(yè)高高峰時(shí)間，目前是是上午8:00-10:00，下下午3:00-4:00）禁止做大大數(shù)據(jù)量的查詢和和統(tǒng)計(jì)，每個(gè)查詢詢的執(zhí)行時(shí)間要控控制在分鐘內(nèi)。 PAGEREF _Toc h 6a。 HYPERLINK l _Toc 七、不要執(zhí)行索引和表表的信息的收集。 PAGEREF _T

4、oc h 6S。 HYPERLINK l _Toc 八、編寫程序的時(shí)候，注注意語句規(guī)規(guī)范，盡量使用變變量綁定，減少共共享池的使用。 PAGEREF _Toc h 6a。 HYPERLINK l _Toc 九、按照標(biāo)準(zhǔn)要求編寫寫pl/sql等程程序，注意事務(wù)的的提交、回滾和對(duì)對(duì)各種異常情況的的處理。 PAGEREF _Toc h 6h。 HYPERLINK l _Toc 十、要查看表字段名或或隨機(jī)的少量數(shù)據(jù)據(jù)時(shí)候，使用desc、也可以以使用where1=2或者rowcountn來查看，而盡盡量不要直接執(zhí)行行select * from tablename，然后kill會(huì)話。 PAGEREF _To

5、c h 6g。 HYPERLINK l _Toc 十一、盡量使用索引，避避免出現(xiàn)全表掃描描，性能影響比單單機(jī)更大。 PAGEREF _Toc h 6s。 HYPERLINK l _Toc 十二、對(duì)分區(qū)表建立索索引時(shí)，使用local選項(xiàng)。 PAGEREF _Toc h 6A。 HYPERLINK l _Toc 十三、不要在事務(wù)中引引入Trigger，建建議在事務(wù)中實(shí)現(xiàn)現(xiàn)。 PAGEREF _Toc h 66。 HYPERLINK l _Toc 十四、批量更新數(shù)據(jù)的的大的事務(wù)分次提提交。 PAGEREF _Toc h 62。 HYPERLINK l _Toc 十五、客戶端的配置。 PAGEREF

6、_Toc h 7y。 HYPERLINK l _Toc 十六、修改密碼。 PAGEREF _Toc h 7b。 HYPERLINK l _Toc 第二章SQL編寫注注意事項(xiàng) PAGEREF _Toc h 9f。 HYPERLINK l _Toc 一、SELECT 子子句中避免使用* PAGEREF _Toc h 9k。 HYPERLINK l _Toc 二、查找總記錄數(shù)時(shí)，盡盡量不要用count(*)，而要指定定一個(gè)有索引的字字段。 PAGEREF _Toc h 9a。 HYPERLINK l _Toc 三、將大的歷史表創(chuàng)建建為分區(qū)表，便于于數(shù)據(jù)轉(zhuǎn)儲(chǔ)和刪除除。 PAGEREF _Toc h 9

7、s。 HYPERLINK l _Toc 四、使用分區(qū)表進(jìn)行查查詢時(shí)，盡量把分分區(qū)鍵作為查詢條條件的第一個(gè)條件件。 PAGEREF _Toc h 9i。 HYPERLINK l _Toc 五、Sequence采用cache/noorder，如果在使用sequence上的列建索引，建議加大cache值。 PAGEREF _Toc h 9T。 HYPERLINK l _Toc 六、在FROM子句中中包含多個(gè)表的情情況下，選擇記錄錄條數(shù)最少的表作作為基礎(chǔ)表，放在在FROM子句的最最后面。 PAGEREF _Toc h 9v。 HYPERLINK l _Toc 七、WHERE子句中中的連接順序 PAGE

8、REF _Toc h 10C。 HYPERLINK l _Toc 八、在需要無條件刪除除表中數(shù)據(jù)時(shí)，用用truncate代替delete。 PAGEREF _Toc h 114。 HYPERLINK l _Toc 九、語句中盡量使用表表的索引字段，避避免做大表的全表表掃描。 PAGEREF _Toc h 11G。 HYPERLINK l _Toc 十、 帶通配符（%）的的like語句 PAGEREF _Toc h 11f。 HYPERLINK l _Toc 十一、用EXISTS替代代IN PAGEREF _Toc h 11B。 HYPERLINK l _Toc 十二、用NOT EXISTS替代

9、NOT IN PAGEREF _Toc h 126。 HYPERLINK l _Toc 十三、盡可能的用UNION-ALL 替換UNION PAGEREF _Toc h 129。 HYPERLINK l _Toc 十四、Order by語句建議 PAGEREF _Toc h 133。 HYPERLINK l _Toc 十五、避免使用NOT PAGEREF _Toc h 13q。 HYPERLINK l _Toc 十六、使用DECODE函數(shù)數(shù)來減少處理時(shí)間間 PAGEREF _Toc h 14R。 HYPERLINK l _Toc 十七、刪除重復(fù)記錄 PAGEREF _Toc h 145。 HY

10、PERLINK l _Toc 十八、如果可以使用where條件，盡盡量不要在having中限限制數(shù)據(jù) PAGEREF _Toc h 14f。 HYPERLINK l _Toc 十九、盡量不要使數(shù)據(jù)據(jù)排序 PAGEREF _Toc h 14U。 HYPERLINK l _Toc 二十、使用提示(Hints) PAGEREF _Toc h 15z。 HYPERLINK l _Toc 第三章oracle和和sybase的SQL區(qū)別 PAGEREF _Toc h 15f。 HYPERLINK l _Toc 一、大小寫 PAGEREF _Toc h 152。 HYPERLINK l _Toc 二、限制記

11、錄數(shù)量 PAGEREF _Toc h 15Y。 HYPERLINK l _Toc 三、列的選擇 PAGEREF _Toc h 16z。 HYPERLINK l _Toc 四、連接 PAGEREF _Toc h 168。 HYPERLINK l _Toc 五、字符串函數(shù) PAGEREF _Toc h 16r。 HYPERLINK l _Toc 六、日期函數(shù) PAGEREF _Toc h 16f。 HYPERLINK l _Toc 七、數(shù)據(jù)類型轉(zhuǎn)換函數(shù)數(shù): PAGEREF _Toc h 175。 HYPERLINK l _Toc 八、空值替代函數(shù): PAGEREF _Toc h 17f。 HYPE

12、RLINK l _Toc 九、sybase的where語句執(zhí)執(zhí)行 正則符號(hào)，但但是oracle9i不支持。 PAGEREF _Toc h 17k。 HYPERLINK l _Toc 十、數(shù)字取舍舍 PAGEREF _Toc h 17a。 HYPERLINK l _Toc 第四章 跟蹤SQL執(zhí)執(zhí)行計(jì)劃 PAGEREF _Toc h 18w。 HYPERLINK l _Toc 一、理論 PAGEREF _Toc h 18T。 HYPERLINK l _Toc （一）ORACLE優(yōu)優(yōu)化器 PAGEREF _Toc h 18b。 HYPERLINK l _Toc （二）訪問TABLE的方式式 PAGE

13、REF _Toc h 18j。 HYPERLINK l _Toc （三） 索引訪問方式式 PAGEREF _Toc h 19J。 HYPERLINK l _Toc 二、SET TRACE跟蹤sql執(zhí)行計(jì)劃 PAGEREF _Toc h 19q。第一章數(shù)據(jù)庫使用注注意事項(xiàng)一、對(duì)BOSS1.5營營帳庫，營業(yè)網(wǎng)址址嚴(yán)格按照要求進(jìn)進(jìn)行配置，不可隨隨意更換。D。營業(yè)網(wǎng)址要求按照下面面方式進(jìn)行分配配配置，如果隨意更更換，會(huì)增加營業(yè)業(yè)主機(jī)間的數(shù)據(jù)交交互，影響數(shù)據(jù)庫庫性能，降低營業(yè)業(yè)工作效率。U。合肥、六安、阜陽、宿宿州、亳州、淮北北、黃山、銅陵配配置: :7001/WebRoot/login.jspV?；蛘?/p>

14、http:/main.webA1.amcc/WebRoot/login.jspm。蕪湖、蚌埠、淮南、馬馬鞍山、安慶、滁滁州、宣城、巢湖湖、池州，配置如如下:I。:8001/WebRoot/login.jsph?；蛘遠(yuǎn)ttp:/main.webB1.amcc/WebRoot/login.jspu。二、不使用數(shù)據(jù)庫時(shí)請請及時(shí)關(guān)閉數(shù)據(jù)庫庫連接，但是也不不能頻繁的連接和和斷開3。數(shù)據(jù)庫連接也是數(shù)據(jù)庫庫的寶貴資源，數(shù)數(shù)據(jù)庫支持的數(shù)據(jù)據(jù)庫連接有限，當(dāng)當(dāng)不需要使用數(shù)據(jù)據(jù)庫時(shí)，請“優(yōu)雅”的退出數(shù)據(jù)庫吧吧，如果能正常退退出，請別“結(jié)束任務(wù)”或KILL -9。如果正在執(zhí)行SQL的時(shí)候突然異常終終端，請聯(lián)系數(shù)據(jù)據(jù)庫

15、管理員檢查處處理，以防止數(shù)據(jù)據(jù)庫一直占用該SQL相相關(guān)資源。y。三、執(zhí)行了DML操作作，請按業(yè)務(wù)規(guī)則，不要忘記執(zhí)行COMMIT或或ROLLBACK。3。不要只執(zhí)行語句，而不不控制事務(wù)。當(dāng)你你執(zhí)行一條DML語語句時(shí)，數(shù)據(jù)庫會(huì)會(huì)為你分配鎖、回回滾段、REDO LOG BUFFER等資源。事務(wù)結(jié)束后，這些資源才能得以釋放。h。四、如果是查詢和統(tǒng)計(jì)計(jì)不涉及到當(dāng)天的的業(yè)務(wù)時(shí)，不要在在生產(chǎn)環(huán)境里操作，在BCV庫庫中操作。BCV每天晚上12點(diǎn)點(diǎn)同步一次，數(shù)據(jù)據(jù)和用戶口令、密密碼和生產(chǎn)環(huán)境相相同。D。bcv是一個(gè)節(jié)點(diǎn)的數(shù)數(shù)據(jù)庫，所有的地地市的查詢的連接接配置是同一個(gè)，如如下： 3。 YZDBBCV = (DE

16、SCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 5)(PORT = 1521)1。 ) (CONNECT_DATA = (SERVICE_NAME = yzdb) ) )五、關(guān)聯(lián)表都很大的查查詢和統(tǒng)計(jì)也盡量量用BCV庫。六、生產(chǎn)環(huán)境營業(yè)時(shí)間間（特別是營業(yè)高高峰時(shí)間，目前是是上午8:00-10:00，下下午3:00-4:00）禁止做大大數(shù)據(jù)量的查詢和和統(tǒng)計(jì)，每個(gè)查詢詢的執(zhí)行時(shí)間要控控制在分鐘內(nèi)。E。七、不要執(zhí)行索引和表表的信息的收集。八、編寫程序的時(shí)候，注意語句規(guī)范，盡量使用變量綁定，減少共享池的使用。Q。九、按照標(biāo)準(zhǔn)

17、要求編寫寫pl/sql等等程序，注意事務(wù)務(wù)的提交、回滾和和對(duì)各種異常情況況的處理。6。十、要查看表字段名或或隨機(jī)的少量數(shù)據(jù)據(jù)時(shí)候，使用desc、也可以使用where1=2或者rowcount 50000AND JOB = MANAGERAND 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);t。(高效,執(zhí)行時(shí)間間10.6秒)SELECT *FROM EMP EWHERE 25 50000AND JOB = MANAGER;八、在需要無條件刪除除表中數(shù)據(jù)時(shí)，用用truncate代代替delete。N。九、語句中盡量使用表表的索引字段，避避免做大表的

18、全表表掃描。例如Where子句中中有聯(lián)接的列，即即使最后的聯(lián)接值值為一個(gè)靜態(tài)值，也也不會(huì)使用索引。 E。select * from employeewhere first_name|last_name =Beill Cliton; K。這條語句沒有使用基于于last_name創(chuàng)創(chuàng)建的索引。 當(dāng)采用下面這種SQL語語句的編寫，Oracle系系統(tǒng)就可以采用基基于last_name創(chuàng)創(chuàng)建的索引。 s。Select * from employee where first_name =Beill and last_name =Cliton; s。十、 帶通配符（%）的的like語句例如SQL語句： se

19、lect * from employee where last_name like %cliton%; 由于于通配符（%）在在搜尋詞首出現(xiàn)，所所以O(shè)racle系系統(tǒng)不使用last_name的索引引。通配符如此使使用會(huì)降低查詢速速度。當(dāng)通配符出出現(xiàn)在字符串其他他位置時(shí)，優(yōu)化器器就能利用索引。在在下面的查詢中索索引得到了使用： select * from employee where last_name like c%;J。十一、用EXISTS替替代IN 在許多基于基礎(chǔ)表的查查詢中,為了滿足足一個(gè)條件,往往往需要對(duì)另一個(gè)表表進(jìn)行聯(lián)接.在這這種情況下, 使使用EXISTS(或NOT EXISTS)通

20、常將提高查詢詢的效率.S。低效: SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC =MELB) 高效: SELECT * 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í)行行一個(gè)內(nèi)部的排序序和合并. 無論論在哪種情況下,NOT IN

21、都是最低效效的 (因?yàn)樗鼘?duì)對(duì)子查詢中的表執(zhí)執(zhí)行了一個(gè)全表遍遍歷). 為了避避免使用NOT IN ,我們可可以把它改寫成外外連接(Outer Joins)或或NOT EXISTS. l。例如: SELECT FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO l。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(+) =

22、A (方法二: 最高效) 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語句需要UNION兩兩個(gè)查詢結(jié)果集合合時(shí),這兩個(gè)結(jié)果果集合會(huì)以UNION-ALL的方式被被合并, 然后在在輸出最終結(jié)果前前進(jìn)行排序. m。如果用UNION ALL替替代UNION, 這樣排序就不是是必要了. 效率率就會(huì)因此得到提提高. P。舉例: 低效： SELECT ACCT_NUM, BALANCE

23、_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 TR

24、AN_DATE = 31-DEC-95十四、Order by語語句建議ORDER BY語句句決定了Oracle如如何將返回的查詢詢結(jié)果排序。Order by語句對(duì)要排排序的列沒有什么么特別的限制，也也可以將函數(shù)加入入列中（象聯(lián)接或或者附加等）。任任何在Order by語句的非索索引項(xiàng)或者有計(jì)算算表達(dá)式都將降低低查詢速度。 J。仔細(xì)檢查order by語句以找出出非索引項(xiàng)或者表表達(dá)式，它們會(huì)降降低性能。解決這這個(gè)問題的辦法就就是重寫order by語句以使用用索引，也可以為為所使用的列建立立另外一個(gè)索引，同同時(shí)應(yīng)絕對(duì)避免在在order by子子句中使用表達(dá)式式。z。十五、避免使用NOT 在查詢時(shí)

25、經(jīng)常在where子子句使用一些邏輯輯表達(dá)式，如大于于、小于、等于以以及不等于等等，也也可以使用and（與與）、or（或）以以及not（非）。NOT可可用來對(duì)任何邏輯輯運(yùn)算符號(hào)取反。下下面是一個(gè)NOT子子句的例子： . where not (status =VALID) 如果要要使用NOT，則則應(yīng)在取反的短語語前面加上括號(hào)，并并在短語前面加上上NOT運(yùn)算符。NOT運(yùn)運(yùn)算符包含在另外外一個(gè)邏輯運(yùn)算符符中，這就是不等等于（）運(yùn)算算符。換句話說，即即使不在查詢where子子句中顯式地加入入NOT詞，NOT仍仍在運(yùn)算符中，見見下例： . where status INVALID; 再看下下面這個(gè)例子：

26、select * from employee where salary3000; 對(duì)這個(gè)查詢詢，可以改寫為不不使用NOT： select * from employee where salary3000; 雖然這兩種查查詢的結(jié)果一樣，但但是第二種查詢方方案會(huì)比第一種查查詢方案更快些。第第二種查詢允許Oracle對(duì)對(duì)salary列列使用索引，而第第一種查詢則不能能使用索引。2。十六、使用DECODE函函數(shù)來減少處理時(shí)時(shí)間 使用DECODE函數(shù)數(shù)可以避免重復(fù)掃掃描相同記錄或重重復(fù)連接相同的表表. h。例如: SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_N

27、O = 0020 AND ENAME LIKESMITH%; SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0030 AND ENAME LIKESMITH%; 可以用DECODE函函數(shù)高效地得到相相同結(jié)果 SELECT COUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT, l。COUNT(DECODE(DEPT_NO,0030,X,NULL) D0030_COUNT, r。SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL, Z。SUM(DECODE(DEPT_NO

28、,0030,SAL,NULL) D0030_SAL F。FROM EMP WHERE ENAME LIKE SMITH%; g。類似的,DECODE函函數(shù)也可以運(yùn)用于于GROUP BY 和ORDER BY子句中.E。十七、刪除重復(fù)記錄DELETE FROM EMP E WHERE E.ROWID (SELECT MIN(X.ROWID) 5。FROM EMP X WHERE X.EMP_NO = E.EMP_NO); 十八、如果可以使用where條條件，盡量不要在在having中中限制數(shù)據(jù)H。十九、盡量不要使數(shù)據(jù)據(jù)排序引起排序的條件- Order by- Group by- Union,int

29、ersect,minus- Distinct二十、使用提示(Hints)對(duì)于表的訪問,可以使使用兩種Hints：FULL 和 ROWID H。FULL hint 告訴ORACLE使使用全表掃描的方方式訪問指定表. H。例如: SELECT /*+ FULL(EMP) */ * FROM EMP WHERE EMPNO = 7893; ROWID hint 告訴ORACLE使使用TABLE ACCESS BY ROWID的的操作訪問表. 0。通常, 你需要采用TABLE ACCESS BY ROWID的的方式特別是當(dāng)訪訪問大表的時(shí)候, 使用這種方式, 你需要知道ROIWD的的值或者使用索引引。

30、c。如果一個(gè)大表沒有被設(shè)設(shè)定為緩存(CACHED)表而你希望它的的數(shù)據(jù)在查詢結(jié)束束是仍然停留在SGA中中,你就可以使用用CACHE hint 來告訴優(yōu)化器把把數(shù)據(jù)保留在SGA中中。 通常CACHE hint 和 FULL hint 一起使用。 v。例如: SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ * v。FROM WORK; 索引hint 告訴ORACLE使使用基于索引的掃掃描方式. 你不不必說明具體的索索引名稱 P。例如: SELECT /*+ INDEX(a index_name) */ LODGING L。FROM LODGING aWHERE MANAGER = BILL GATES; ORACLE hints 還包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等。可以根據(jù)具體情情況具體使用。T。第三章oracle和和sybase的的SQL區(qū)別一、大小寫SYBASE的SQL中中數(shù)據(jù)庫名、表名名和列名分大小寫寫，應(yīng)遵循定義時(shí)時(shí)的寫法；ORACLE 并不區(qū)分。O。二