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

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

2、z u HYPERLINK l _Toc ORACLE 數(shù)據(jù)庫操作作手冊2.00 PAGEREF _Toc h 1 HYPERLINK l _Toc 第一章數(shù)據(jù)庫庫使用注意事事項 PAGEREF _Toc h 5 HYPERLINK l _Toc 二、不使用數(shù)據(jù)據(jù)庫時請及時時關閉數(shù)據(jù)庫庫連接，但是是也不能頻繁繁的連接和斷斷開 PAGEREF _Toc h 5 HYPERLINK l _Toc 三、執(zhí)行了DMML操作，請請按業(yè)務規(guī)則則，不要忘記記執(zhí)行COMMMIT或ROLLBBACK。 PAGEREF _Toc h 5 HYPERLINK l _Toc 四、如果是查詢詢和統(tǒng)計不涉涉及到當天的的業(yè)

3、務時，不不要在生產環(huán)環(huán)境里操作，在在BCV庫中操操作。BCVV每天晚上122點同步一次次，數(shù)據(jù)和用用戶口令、密密碼和生產環(huán)環(huán)境相同。 PAGEREF _Toc h 55 HYPERLINK l _Toc 五、關聯(lián)表都很很大的查詢和和統(tǒng)計也盡量量用BCV庫。 PAGEREF _Toc h 6 HYPERLINK l _Toc 六、生產環(huán)境營營業(yè)時間（特特別是營業(yè)高高峰時間，目目前是上午88:00-110:00，下下午3:000-4:000）禁止做大大數(shù)據(jù)量的查查詢和統(tǒng)計，每每個查詢的執(zhí)執(zhí)行時間要控控制在分鐘鐘內。 PAGEREF _Toc h 6 HYPERLINK l _Toc 七、不要執(zhí)行索

4、索引和表的信信息的收集。 PAGEREF _Toc h 6 HYPERLINK l _Toc 八、編寫程序的的時候，注意意語句句規(guī)范，盡量量使用變量綁綁定，減少共共享池的使用用。 PAGEREF _Toc h 6 HYPERLINK l _Toc 九、按照標準要要求編寫pll/sql等等程序，注意意事務的提交交、回滾和對對各種異常情情況的處理。 PAGEREF _Toc h 6 HYPERLINK l _Toc 十、要查看表字字段名或隨機機的少量數(shù)據(jù)據(jù)時候，使用用desc、也也可以使用wwhere11=2或者rowccountn來查看，而而盡量不要直直接執(zhí)行seelect * froom ta

5、bblenamme，然后killl會話。 PAGEREF _Toc h 6 HYPERLINK l _Toc 十一、盡量使用用索引，避免免出現(xiàn)全表掃掃描，性能影影響比單機更更大。 PAGEREF _Toc h 6 HYPERLINK l _Toc 十二、對分區(qū)表表建立索引時時，使用loocal選項項。 PAGEREF _Toc h 6 HYPERLINK l _Toc 十三、不要在事事務中引入TTriggeer，建議在在事務中實現(xiàn)現(xiàn)。 PAGEREF _Toc h 6 HYPERLINK l _Toc 十四、批量更新新數(shù)據(jù)的大的的事務分次提提交。 PAGEREF _Toc h 6 HYPERL

6、INK l _Toc 十五、客戶端的的配置。 PAGEREF _Toc h7 HYPERLINK l _Toc 十六、修改密碼碼。 PAGEREF _Toc h 7 HYPERLINK l _Toc 第二章SQLL編寫注意事事項 PAGEREF _Toc h 9 HYPERLINK l _Toc 一、SELECCT 子句中中避免使用* PAGEREF _Toc h 9 HYPERLINK l _Toc 二、查找總記錄錄數(shù)時，盡量量不要用coount(*)，而要指指定一個有索索引的字段。 PAGEREF _Toc h 9 HYPERLINK l _Toc 三、將大的歷史史表創(chuàng)建為分分區(qū)表，便于于

7、數(shù)據(jù)轉儲和和刪除。 PAGEREF _Toc h 9 HYPERLINK l _Toc 四、使用分區(qū)表表進行查詢時時，盡量把分分區(qū)鍵作為查查詢條件的第第一個條件。 PAGEREF _Toc h 9 HYPERLINK l _Toc 五、Sequeence采用用cachee/noorrder，如如果在使用ssequennce上的列列建索引，建建議加大caache值。 PAGEREF _Toc h 9 HYPERLINK l _Toc 六、在FROMM子句中包含含多個表的情情況下，選擇擇記錄條數(shù)最最少的表作為為基礎表，放放在FROMM子句的最后后面。 PAGEREF _Toc h 9 HYPERL

8、INK l _Toc 七、WHEREE子句中的連連接順序 PAGEREF _Toc h 100 HYPERLINK l _Toc 八、在需要無條條件刪除表中中數(shù)據(jù)時，用用trunccate代替替delette。 PAGEREF _Toc h 11 HYPERLINK l _Toc 九、語句中盡量量使用表的索索引字段，避避免做大表的的全表掃描。 PAGEREF _Toc h 11 HYPERLINK l _Toc 十、帶通配符（%）的likee語句 PAGEREF _Toc h 11 HYPERLINK l _Toc 十一、用EXIISTS替代代IN PAGEREF _Toc h 11 HYPE

9、RLINK l _Toc 十二、用NOTT EXISSTS替代NOT INN PAGEREF _Toc h 12 HYPERLINK l _Toc 十三、盡可能的的用UNIOON-ALLL 替換UNIOON PAGEREF _Toc h 12 HYPERLINK l _Toc 十四、Ordeer by語語句建議 PAGEREF _Toc h 133 HYPERLINK l _Toc 十五、避免使用用NOT PAGEREF _Toc h 13 HYPERLINK l _Toc 十六、使用DEECODE函函數(shù)來減少處處理時間 PAGEREF _Toc h 144 HYPERLINK l _Toc

10、十七、刪除重復復記錄 PAGEREF _Toc h 14 HYPERLINK l _Toc 十八、如果可以以使用wheere條件，盡盡量不要在hhavingg中限制數(shù)據(jù)據(jù) PAGEREF _Toc h 14 HYPERLINK l _Toc 十九、盡量不要要使數(shù)據(jù)排序序 PAGEREF _Toc h 14 HYPERLINK l _Toc 二十、使用提示示(Hintts) PAGEREF _Toc h 15 HYPERLINK l _Toc 第三章oraccle和sybasse的SQL區(qū)別 PAGEREF _Toc h 15 HYPERLINK l _Toc 一、大小寫 PAGEREF _To

11、c h 155 HYPERLINK l _Toc 二、限制記錄數(shù)數(shù)量 PAGEREF _Toc h 15 HYPERLINK l _Toc 三、列的選擇 PAGEREF _Toc h 116 HYPERLINK l _Toc 四、連接 PAGEREF _Toc h 16 HYPERLINK l _Toc 五、字符串函數(shù)數(shù) PAGEREF _Toc h 16 HYPERLINK l _Toc 六、日期函數(shù) PAGEREF _Toc h 116 HYPERLINK l _Toc 七、數(shù)據(jù)類型轉轉換函數(shù): PAGEREF _Toc h 17 HYPERLINK l _Toc 八、空值替代函函數(shù): P

12、AGEREF _Toc h 17 HYPERLINK l _Toc 九、sybasse的wheree語句執(zhí)行 正則符號號，但是orracle99i不支持。 PAGEREF _Toc h 17 HYPERLINK l _Toc 十、數(shù)字取舍 PAGEREF _Toc h 117 HYPERLINK l _Toc 第四章跟蹤SQQL執(zhí)行計劃劃 PAGEREF _Toc h 18 HYPERLINK l _Toc 一、理論 PAGEREF _Toc h 18 HYPERLINK l _Toc （一）ORACCLE優(yōu)化器器 PAGEREF _Toc h 18 HYPERLINK l _Toc （二）訪

13、問TAABLE的方方式 PAGEREF _Toc h 18 HYPERLINK l _Toc （三）索引訪問問方式 PAGEREF _Toc h 19 HYPERLINK l _Toc 二、SET TTRACE跟跟蹤sql執(zhí)行計計劃 PAGEREF _Toc h 19第一章數(shù)據(jù)庫庫使用注意事事項一、對BOSSS1.5營帳帳庫，營業(yè)網網址嚴格按照照要求進行配配置，不可隨隨意更換。營業(yè)網址要求按按照下面方式式進行分配配配置，如果隨隨意更換，會會增加營業(yè)主主機間的數(shù)據(jù)據(jù)交互，影響響數(shù)據(jù)庫性能能，降低營業(yè)業(yè)工作效率。合肥、六安、阜阜陽、宿州、亳州、淮北北、黃山、銅銅陵配置: http:/10.1447

14、.1322.5:70001/WeebRoott/logiin.jspp或者http:/main.webA11.amccc/WebRRoot/llogin.jsp蕪湖、蚌埠、淮淮南、馬鞍山山、安慶、滁滁州、宣城、巢湖、池州州，配置如下下:http:/10.1447.1322.6:80001/WeebRoott/logiin.jspp或者http:/main.webB11.amccc/WebRRoot/llogin.jsp二、不使用數(shù)據(jù)據(jù)庫時請及時時關閉數(shù)據(jù)庫庫連接，但是是也不能頻繁繁的連接和斷斷開數(shù)據(jù)庫連接也是是數(shù)據(jù)庫的寶寶貴資源，數(shù)數(shù)據(jù)庫支持的的數(shù)據(jù)庫連接接有限，當不不需要使用數(shù)數(shù)據(jù)庫時，請請

15、“優(yōu)雅”的退出數(shù)據(jù)據(jù)庫吧，如果果能正常退出出，請別“結束任務”或KILLL -9。如果正在執(zhí)執(zhí)行SQL的時候突然異異常終端，請請聯(lián)系數(shù)據(jù)庫庫管理員檢查查處理，以防防止數(shù)據(jù)庫一一直占用該SSQL相關資資源。三、執(zhí)行了DMML操作，請請按業(yè)務規(guī)則則，不要忘記執(zhí)行行COMMIIT或ROLLLBACKK。不要只執(zhí)行語句句，而不控制制事務。當你你執(zhí)行一條DDML語句時時，數(shù)據(jù)庫會會為你分配鎖鎖、回滾段、REDO LOG BBUFFERR等資源。事事務結束后，這這些資源才能能得以釋放。四、如果是查詢詢和統(tǒng)計不涉涉及到當天的的業(yè)務時，不不要在生產環(huán)環(huán)境里操作，在BBCV庫中操作。BCV每天晚上上12點同步步

16、一次，數(shù)據(jù)據(jù)和用戶口令令、密碼和生生產環(huán)境相同同。bcv是一個節(jié)節(jié)點的數(shù)據(jù)庫庫，所有的地地市的查詢的的連接配置是是同一個，如如下： YZDBBCCV = (DESCCRIPTIION = (ADDDRESSS_LISTT = (ADDREESS = (PROTTOCOL = TCPP)(HOSST = 110.1533.192.45)(POORT = 1521) ) (COONNECTT_DATAA = (SERVIICE_NAAME = yzdb) ) )五、關聯(lián)表都很很大的查詢和和統(tǒng)計也盡量量用BCV庫庫。六、生產環(huán)境營營業(yè)時間（特特別是營業(yè)高高峰時間，目目前是上午88:00-110:00

17、，下下午3:000-4:000）禁止做大大數(shù)據(jù)量的查查詢和統(tǒng)計，每個查詢的執(zhí)行時間要控制在分鐘內。七、不要執(zhí)行索索引和表的信息的的收集。八、編寫程序的的時候，注意意語句規(guī)規(guī)范，盡量使使用變量綁定定，減少共享享池的使用。九、按照標準要要求編寫pll/sql等等程序，注意意事務的提交交、回滾和對對各種異常情情況的處理。十、要查看表字字段名或隨機機的少量數(shù)據(jù)據(jù)時候，使用用desc、也可以使用用wheree1=2或者者rowcoount 500000AND JJOB = MANAAGERAND 225 (SELECCT COUUNT(*) FROMM EMP WHEREE MGR=E.EMPPNO);

18、(高效,執(zhí)執(zhí)行時間100.6秒)SELECT *FROM EMMP EWHEREE 25 500000AND JOBB = MMANAGEER;八、在需要無條條件刪除表中中數(shù)據(jù)時，用用trunccate代替替delette。九、語句中盡量量使用表的索索引字段，避避免做大表的的全表掃描。例如Wheree子句中有聯(lián)聯(lián)接的列，即即使最后的聯(lián)聯(lián)接值為一個個靜態(tài)值，也也不會使用索索引。 select * froom empployeeewhere ffirst_name|last_name =Beiill Clliton; 這條語句沒有使使用基于laast_naame創(chuàng)建的的索引。 當采用下面這種種SQ

19、L語句句的編寫，OOraclee系統(tǒng)就可以以采用基于llast_nname創(chuàng)建建的索引。 Select * froom empployeee where ffirst_name =Beiill aand laast_naame =Clitoon;十、 帶通配符符（%）的llike語句句例如SQL語句句： sselectt * frrom emmployeee wheere laast_naame liike %clitoon%; 由于于通配符（%）在搜尋詞詞首出現(xiàn)，所所以Oraccle系統(tǒng)不不使用lasst_namme的索引。通配符如此此使用會降低低查詢速度。當通配符出出現(xiàn)在字符串串其他位置時

20、時，優(yōu)化器就就能利用索引引。在下面的的查詢中索引引得到了使用用： sselectt * frrom emmployeee wheere laast_naame liike cc%;十一、用EXIISTS替代代IN在許多基于基礎礎表的查詢中中,為了滿足足一個條件,往往需要對對另一個表進進行聯(lián)接.在在這種情況下下, 使用EEXISTSS(或NOTT EXISSTS)通常常將提高查詢詢的效率.低效: SELECT * FROM EMMP (基礎礎表) WHERE EEMPNO 0 AND DEPPTNO IIN (SEELECT DEPTNNO FROM DEEPT WHERE LLOC =MELB

21、) 高效: SELECT * FROM EMMP (基礎礎表) WHERE EEMPNO 0 AND EXIISTS (SELECCT X FROM DEEPT WHERE DDEPT.DDEPTNOO = EMMP.DEPPTNO AND LOCC = MELB)十二、用NOTT EXISSTS替代NNOT INN在子查詢中,NNOT INN子句將執(zhí)行行一個內部的的排序和合并并. 無論在在哪種情況下下,NOT IN都是最最低效的 (因為它對子子查詢中的表表執(zhí)行了一個個全表遍歷). 為了避避免使用NOOT IN ,我們可以以把它改寫成成外連接(OOuter Joinss)或NOTT EXISS

22、TS. 例如: SELECT FROM EMMP WHERE DDEPT_NNO NOTT IN (SELECCT DEPPT_NO FROM DEEPT WHERE DDEPT_CCAT=AA); 為了提高效率.改寫為: (方法一: 高高效) SELECT . FROM EMMP A,DDEPT BB WHERE AA.DEPTT_NO = B.DEEPT(+) AND B.DDEPT_NNO IS NULL AND B.DDEPT_CCAT(+) = A (方法二: 最最高效) SELECT . FROM EMMP E WHERE NNOT EXXISTS (SELEECT X FROM

23、DEEPT D WHERE DD.DEPTT_NO = E.DEEPT_NOO AND DEPPT_CATT = A);十三、盡可能的的用UNIOON-ALLL 替換UNNION當SQL語句需需要UNIOON兩個查詢詢結果集合時時,這兩個結結果集合會以以UNIONN-ALL的的方式被合并并, 然后在在輸出最終結結果前進行排排序. 如果用UNIOON ALLL替代UNIION, 這這樣排序就不不是必要了. 效率就會會因此得到提提高. 舉例: 低效： SELECT ACCT_NUM, BALANNCE_AMMT FROM DEEBIT_TTRANSAACTIONNS WHERE TTRAN_DDA

24、TE = 31-DEC-995 UNIONSELECT ACCT_NUM, BALANNCE_AMMT FROM DEEBIT_TTRANSAACTIONNS WHERE TTRAN_DDATE = 31-DEC-995 高效: SELECT ACCT_NUM, BALANNCE_AMMT FROM DEEBIT_TTRANSAACTIONNS WHERE TTRAN_DDATE = 31-DEC-995 UNION AALL SELECT ACCT_NUM, BALANNCE_AMMT FROM DEEBIT_TTRANSAACTIONNS WHERE TTRAN_DDATE = 31-DE

25、C-995十四、Ordeer by語語句建議ORDER BBY語句決定定了Oraccle如何將將返回的查詢詢結果排序。Orderr by語句句對要排序的的列沒有什么么特別的限制制，也可以將將函數(shù)加入列列中（象聯(lián)接接或者附加等等）。任何在在Orderr by語句句的非索引項項或者有計算算表達式都將將降低查詢速速度。 仔細檢查ordder byy語句以找出出非索引項或或者表達式，它它們會降低性性能。解決這這個問題的辦辦法就是重寫寫orderr by語句句以使用索引引，也可以為為所使用的列列建立另外一一個索引，同同時應絕對避避免在ordder byy子句中使用用表達式。十五、避免使用用NOT在查詢時

26、經常在在wheree子句使用一一些邏輯表達達式，如大于于、小于、等等于以及不等等于等等，也也可以使用aand（與）、or（或）以以及not（非非）。NOTT可用來對任任何邏輯運算算符號取反。下面是一個個NOT子句句的例子：. wheree not (stattus =VALIDD) 如果要使用用NOT，則則應在取反的的短語前面加加上括號，并并在短語前面面加上NOTT運算符。NNOT運算符符包含在另外外一個邏輯運運算符中，這這就是不等于于（）運運算符。換句句話說，即使使不在查詢wwhere子子句中顯式地地加入NOTT詞，NOTT仍在運算符符中，見下例例：. wheree stattus INVA

27、ALID; 再看看下面這個例例子：sselectt * frrom emmployeee wheere saalary30000; 對對這個查詢，可可以改寫為不不使用NOTT：seelect * froom empployeee wherre sallary33000; 雖然這這兩種查詢的的結果一樣，但但是第二種查查詢方案會比比第一種查詢詢方案更快些些。第二種查查詢允許Orracle對對salarry列使用索索引，而第一一種查詢則不不能使用索引引。十六、使用DEECODE函函數(shù)來減少處處理時間使用DECODDE函數(shù)可以以避免重復掃掃描相同記錄錄或重復連接接相同的表. 例如: SELECT CO

28、UNTT(*)，SSUM(SAAL) FROMEMMP WHERE DDEPT_NNO = 00020 AND ENAAME LIIKESMITHH%; SELECT COUNTT(*)，SSUM(SAAL) FROMEMMP WHERE DDEPT_NNO = 00030 AND ENAAME LIIKESMITHH%; 可以用DECOODE函數(shù)高高效地得到相相同結果 SELECT COUNTT(DECOODE(DEEPT_NOO,00200,X,NULL) D00020_COOUNT, COUNT(DDECODEE(DEPTT_NO,00030,X,NUULL) D00300_COUNNT

29、, SUM(DECCODE(DDEPT_NNO,00220,SALL,NULLL) D00020_SSAL, SUM(DECCODE(DDEPT_NNO,00330,SALL,NULLL) D00030_SSAL FROM EMMP WHEERE ENNAME LLIKE SMITHH%; 類似的,DECCODE函數(shù)數(shù)也可以運用用于GROUUP BY 和ORDEER BY子子句中.十七、刪除重復復記錄DELETE FROM EMP EE WHERE EE.ROWIID (SELECCT MINN(X.ROOWID) FROM EMMP X WHERE XX.EMP_NO = E.EMPP_NO

30、); 十八、如果可以以使用wheere條件，盡盡量不要在hhavingg中限制數(shù)據(jù)據(jù)十九、盡量不要要使數(shù)據(jù)排序序引起排序的條件件- Orderr by- Groupp by- Unionn,inteersectt,minuus- Distiinct二十、使用提示示(Hintts)對于表的訪問,可以使用兩兩種Hintts：FULLL 和 RROWID FULL hiint 告訴訴ORACLLE使用全表表掃描的方式式訪問指定表表. 例如: SELECT /*+ FFULL(EEMP) */ * FROM EMMP WHERE EEMPNO = 78993; ROWID hhint 告告訴ORACC

31、LE使用TTABLE ACCESSS BY ROWIDD的操作訪問問表. 通常, 你需要要采用TABBLE ACCCESS BY ROOWID的方方式特別是當當訪問大表的的時候, 使使用這種方式式, 你需要要知道ROIIWD的值或或者使用索引引。 如果一個大表沒沒有被設定為為緩存(CAACHED)表而你希望望它的數(shù)據(jù)在在查詢結束是是仍然停留在在SGA中,你就可以使使用CACHHE hinnt 來告訴訴優(yōu)化器把數(shù)數(shù)據(jù)保留在SSGA中。 通常CAACHE hhint 和和 FULLL hintt 一起使用用。例如: SELECT /*+ FFULL(WWORKERR) CACCHE(WOORKER

32、)*/ * FROM WOORK; 索引hint 告訴ORAACLE使用用基于索引的的掃描方式. 你不必說說明具體的索索引名稱 例如: SELECT /*+ IINDEX(a inddex_naame) */ LODDGING FROM LOODGINGG aWHERE MMANAGEER = BILL GATESS; ORACLE hintss 還包括AALL_ROOWS, FFIRST_ROWS, RULEE,USE_NL, UUSE_MEERGE, USE_HHASH 等等等?？梢愿鶕?jù)具具體情況具體體使用。第三章oraccle和syybase的的SQL區(qū)別別一、大小寫SYBASE的的SQL中數(shù)數(shù)據(jù)庫名、表表名和列名分分大小寫，應應遵循定義時時的寫法；OORACLEE 并不區(qū)分分。二、限制記錄數(shù)數(shù)量在SYBASEE SQL中中限制紀錄的的數(shù)量,需要要用