ORACLE数据库操作手册210

1、ORACLE 数据库操作作手册2.0中国通信集团公公司安徽有限限公司计费业务部7年111月修改记录版本日期编辑者编辑内容1.02006/2/10见春蕾形成初稿1.12007/8/31见春蕾根据实际情况修修订部分章节节2.02007/111/05见春蕾一、在第一章（数数据库使用注注意事项）中中增加了关于于批量更新数数据的大事务务分次提交的的要求、客户户端的配置方方法、修改密密码方法；二、在第二章（SSQL编写注注意事项）中中增加了关于于提示(Hiints)的的使用。三、增加了第四四章（跟踪SSQL执行计计划），阐述述了相关的理理论知识和SSQL执行计计划的跟踪方方法。目录TOC o 1-3 h

2、z u HYPERLINK l _Toc ORACLE 数据库操作作手册2.00 PAGEREF _Toc h 1 HYPERLINK l _Toc 第一章数据库库使用注意事事项 PAGEREF _Toc h 5 HYPERLINK l _Toc 二、不使用数据据库时请及时时关闭数据库库连接，但是是也不能频繁繁的连接和断断开 PAGEREF _Toc h 5 HYPERLINK l _Toc 三、执行了DMML操作，请请按业务规则则，不要忘记记执行COMMMIT或ROLLBBACK。 PAGEREF _Toc h 5 HYPERLINK l _Toc 四、如果是查询询和统计不涉涉及到当天的的业

3、务时，不不要在生产环环境里操作，在在BCV库中操操作。BCVV每天晚上122点同步一次次，数据和用用户口令、密密码和生产环环境相同。 PAGEREF _Toc h 55 HYPERLINK l _Toc 五、关联表都很很大的查询和和统计也尽量量用BCV库。 PAGEREF _Toc h 6 HYPERLINK l _Toc 六、生产环境营营业时间（特特别是营业高高峰时间，目目前是上午88:00-110:00，下下午3:000-4:000）禁止做大大数据量的查查询和统计，每每个查询的执执行时间要控控制在分钟钟内。 PAGEREF _Toc h 6 HYPERLINK l _Toc 七、不要执行索

4、索引和表的信信息的收集。 PAGEREF _Toc h 6 HYPERLINK l _Toc 八、编写程序的的时候，注意意语句句规范，尽量量使用变量绑绑定，减少共共享池的使用用。 PAGEREF _Toc h 6 HYPERLINK l _Toc 九、按照标准要要求编写pll/sql等等程序，注意意事务的提交交、回滚和对对各种异常情情况的处理。 PAGEREF _Toc h 6 HYPERLINK l _Toc 十、要查看表字字段名或随机机的少量数据据时候，使用用desc、也也可以使用wwhere11=2或者rowccountn来查看，而而尽量不要直直接执行seelect * froom ta

5、bblenamme，然后killl会话。 PAGEREF _Toc h 6 HYPERLINK l _Toc 十一、尽量使用用索引，避免免出现全表扫扫描，性能影影响比单机更更大。 PAGEREF _Toc h 6 HYPERLINK l _Toc 十二、对分区表表建立索引时时，使用loocal选项项。 PAGEREF _Toc h 6 HYPERLINK l _Toc 十三、不要在事事务中引入TTriggeer，建议在在事务中实现现。 PAGEREF _Toc h 6 HYPERLINK l _Toc 十四、批量更新新数据的大的的事务分次提提交。 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 二、查找总记录录数时，尽量量不要用coount(*)，而要指指定一个有索索引的字段。 PAGEREF _Toc h 9 HYPERLINK l _Toc 三、将大的历史史表创建为分分区表，便于于

7、数据转储和和删除。 PAGEREF _Toc h 9 HYPERLINK l _Toc 四、使用分区表表进行查询时时，尽量把分分区键作为查查询条件的第第一个条件。 PAGEREF _Toc h 9 HYPERLINK l _Toc 五、Sequeence采用用cachee/noorrder，如如果在使用ssequennce上的列列建索引，建建议加大caache值。 PAGEREF _Toc h 9 HYPERLINK l _Toc 六、在FROMM子句中包含含多个表的情情况下，选择择记录条数最最少的表作为为基础表，放放在FROMM子句的最后后面。 PAGEREF _Toc h 9 HYPERL

8、INK l _Toc 七、WHEREE子句中的连连接顺序 PAGEREF _Toc h 100 HYPERLINK l _Toc 八、在需要无条条件删除表中中数据时，用用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函函数来减少处处理时间 PAGEREF _Toc h 144 HYPERLINK l _Toc

10、十七、删除重复复记录 PAGEREF _Toc h 14 HYPERLINK l _Toc 十八、如果可以以使用wheere条件，尽尽量不要在hhavingg中限制数据据 PAGEREF _Toc h 14 HYPERLINK l _Toc 十九、尽量不要要使数据排序序 PAGEREF _Toc h 14 HYPERLINK l _Toc 二十、使用提示示(Hintts) PAGEREF _Toc h 15 HYPERLINK l _Toc 第三章oraccle和sybasse的SQL区别 PAGEREF _Toc h 15 HYPERLINK l _Toc 一、大小写 PAGEREF _To

11、c h 155 HYPERLINK l _Toc 二、限制记录数数量 PAGEREF _Toc h 15 HYPERLINK l _Toc 三、列的选择 PAGEREF _Toc h 116 HYPERLINK l _Toc 四、连接 PAGEREF _Toc h 16 HYPERLINK l _Toc 五、字符串函数数 PAGEREF _Toc h 16 HYPERLINK l _Toc 六、日期函数 PAGEREF _Toc h 116 HYPERLINK l _Toc 七、数据类型转转换函数: PAGEREF _Toc h 17 HYPERLINK l _Toc 八、空值替代函函数: P

12、AGEREF _Toc h 17 HYPERLINK l _Toc 九、sybasse的wheree语句执行 正则符号号，但是orracle99i不支持。 PAGEREF _Toc h 17 HYPERLINK l _Toc 十、数字取舍 PAGEREF _Toc h 117 HYPERLINK l _Toc 第四章跟踪SQQL执行计划划 PAGEREF _Toc h 18 HYPERLINK l _Toc 一、理论 PAGEREF _Toc h 18 HYPERLINK l _Toc （一）ORACCLE优化器器 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执行计计划 PAGEREF _Toc h 19第一章数据库库使用注意事事项一、对BOSSS1.5营帐帐库，营业网网址严格按照照要求进行配配置，不可随随意更换。营业网址要求按按照下面方式式进行分配配配置，如果随随意更换，会会增加营业主主机间的数据据交互，影响响数据库性能能，降低营业业工作效率。合肥、六安、阜阜阳、宿州、亳州、淮北北、黄山、铜铜陵配置: 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二、不使用数据据库时请及时时关闭数据库库连接，但是是也不能频繁繁的连接和断断开数据库连接也是是数据库的宝宝贵资源，数数据库支持的的数据库连接接有限，当不不需要使用数数据库时，请请

15、“优雅”的退出数据据库吧，如果果能正常退出出，请别“结束任务”或KILLL -9。如果正在执执行SQL的时候突然异异常终端，请请联系数据库库管理员检查查处理，以防防止数据库一一直占用该SSQL相关资资源。三、执行了DMML操作，请请按业务规则则，不要忘记执行行COMMIIT或ROLLLBACKK。不要只执行语句句，而不控制制事务。当你你执行一条DDML语句时时，数据库会会为你分配锁锁、回滚段、REDO LOG BBUFFERR等资源。事事务结束后，这这些资源才能能得以释放。四、如果是查询询和统计不涉涉及到当天的的业务时，不不要在生产环环境里操作，在BBCV库中操作。BCV每天晚上上12点同步步

16、一次，数据据和用户口令令、密码和生生产环境相同同。bcv是一个节节点的数据库库，所有的地地市的查询的的连接配置是是同一个，如如下： YZDBBCCV = (DESCCRIPTIION = (ADDDRESSS_LISTT = (ADDREESS = (PROTTOCOL = TCPP)(HOSST = 110.1533.192.45)(POORT = 1521) ) (COONNECTT_DATAA = (SERVIICE_NAAME = yzdb) ) )五、关联表都很很大的查询和和统计也尽量量用BCV库库。六、生产环境营营业时间（特特别是营业高高峰时间，目目前是上午88:00-110:00

17、，下下午3:000-4:000）禁止做大大数据量的查查询和统计，每个查询的执行时间要控制在分钟内。七、不要执行索索引和表的信息的的收集。八、编写程序的的时候，注意意语句规规范，尽量使使用变量绑定定，减少共享享池的使用。九、按照标准要要求编写pll/sql等等程序，注意意事务的提交交、回滚和对对各种异常情情况的处理。十、要查看表字字段名或随机机的少量数据据时候，使用用desc、也可以使用用wheree1=2或者者rowcoount 500000AND JJOB = MANAAGERAND 225 (SELECCT COUUNT(*) FROMM EMP WHEREE MGR=E.EMPPNO);

18、(高效,执执行时间100.6秒)SELECT *FROM EMMP EWHEREE 25 500000AND JOBB = MMANAGEER;八、在需要无条条件删除表中中数据时，用用trunccate代替替delette。九、语句中尽量量使用表的索索引字段，避避免做大表的的全表扫描。例如Wheree子句中有联联接的列，即即使最后的联联接值为一个个静态值，也也不会使用索索引。 select * froom empployeeewhere ffirst_name|last_name =Beiill Clliton; 这条语句没有使使用基于laast_naame创建的的索引。 当采用下面这种种SQ

19、L语句句的编写，OOraclee系统就可以以采用基于llast_nname创建建的索引。 Select * froom empployeee where ffirst_name =Beiill aand laast_naame =Clitoon;十、 带通配符符（%）的llike语句句例如SQL语句句： sselectt * frrom emmployeee wheere laast_naame liike %clitoon%; 由于于通配符（%）在搜寻词词首出现，所所以Oraccle系统不不使用lasst_namme的索引。通配符如此此使用会降低低查询速度。当通配符出出现在字符串串其他位置时

20、时，优化器就就能利用索引引。在下面的的查询中索引引得到了使用用： sselectt * frrom emmployeee wheere laast_naame liike cc%;十一、用EXIISTS替代代IN在许多基于基础础表的查询中中,为了满足足一个条件,往往需要对对另一个表进进行联接.在在这种情况下下, 使用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子句将执行行一个内部的的排序和合并并. 无论在在哪种情况下下,NOT IN都是最最低效的 (因为它对子子查询中的表表执行了一个个全表遍历). 为了避避免使用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语句句对要排序的的列没有什么么特别的限制制，也可以将将函数加入列列中（象联接接或者附加等等）。任何在在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函函数来减少处处理时间使用DECODDE函数可以以避免重复扫扫描相同记录录或重复连接接相同的表. 例如: 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函数高高效地得到相相同结果 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函数数也可以运用用于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中限制数据据十九、尽量不要要使数据排序序引起排序的条件件- 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)表而你希望望它的数据在在查询结束是是仍然停留在在SGA中,你就可以使使用CACHHE hinnt 来告诉诉优化器把数数据保留在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 等等等。可以根据具具体情况具体体使用。第三章oraccle和syybase的的SQL区别别一、大小写SYBASE的的SQL中数数据库名、表表名和列名分分大小写，应应遵循定义时时的写法；OORACLEE 并不区分分。二、限制记录数数量在SYBASEE SQL中中限制纪录的的数量,需要要用