《ORACLE数据库操作手册2[1]06663.docx》由会员分享，可在线阅读，更多相关《ORACLE数据库操作手册2[1]06663.docx（36页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、ORACLE 数据库操作作手册2.0中国通信集团公公司安徽有限限公司计费业务部7年111月修改记录版本日期编辑者编辑内容1.02006/2/10见春蕾形成初稿1.12007/8/31见春蕾根据实际情况修修订部分章节节2.02007/111/05见春蕾一、在第一章（数数据库使用注注意事项）中中增加了关于于批量更新数数据的大事务务分次提交的的要求、客户户端的配置方方法、修改密密码方法；二、在第二章（SSQL编写注注意事项）中中增加了关于于提示(Hiints)的的使用。三、增加了第四四章（跟踪SSQL执行计计划），阐述述了相关的理理论知识和SSQL执行计计划的跟踪方方法。目录ORACLE 数据库操作
2、作手册2.001第一章数据库库使用注意事事项5二、不使用数据据库时请及时时关闭数据库库连接，但是是也不能频繁繁的连接和断断开5三、执行了DMML操作，请请按业务规则则，不要忘记记执行COMMMIT或ROLLBBACK。5四、如果是查询询和统计不涉涉及到当天的的业务时，不不要在生产环环境里操作，在在BCV库中操操作。BCVV每天晚上122点同步一次次，数据和用用户口令、密密码和生产环环境相同。5五、关联表都很很大的查询和和统计也尽量量用BCV库。6六、生产环环境营业时间间（特别是营营业高峰时间间，目前是上上午8:000-10:000，下午3:000-4:000）禁止做做大数据量的的查询和统计计，
3、每个查询询的执行时间间要控制在分钟内。6七、不要执行索索引和表的信信息的收集。6八、编写程序的的时候，注意意语句句规范，尽量量使用变量绑绑定，减少共共享池的使用用。6九、按照标准要要求编写pll/sql等等程序，注意意事务的提交交、回滚和对对各种异常情情况的处理。6十、要查看表字字段名或随机机的少量数据据时候，使用用desc、也也可以使用wwhere11=2或者rowccountn来查看，而而尽量不要直直接执行seelect * froom tabblenamme，然后killl会话。6十一、尽量使用用索引，避免免出现全表扫扫描，性能影影响比单机更更大。6十二、对分区表表建立索引时时，使用lo
4、ocal选项项。6十三、不要在事事务中引入TTriggeer，建议在在事务中实现现。6十四、批量更新新数据的大的的事务分次提提交。6十五、客户端的的配置。7十六、修改密码码。7第二章SQLL编写注意事事项9一、SELECCT 子句中中避免使用*9二、查找总记录录数时，尽量量不要用coount(*)，而要指指定一个有索索引的字段。9三、将大的历史史表创建为分分区表，便于于数据转储和和删除。9四、使用分区表表进行查询时时，尽量把分分区键作为查查询条件的第第一个条件。9五、Sequeence采用用cachee/noorrder，如如果在使用ssequennce上的列列建索引，建建议加大caache值
5、。9六、在FROMM子句中包含含多个表的情情况下，选择择记录条数最最少的表作为为基础表，放放在FROMM子句的最后后面。9七、WHEREE子句中的连连接顺序10八、在需要无条条件删除表中中数据时，用用trunccate代替替delette。11九、语句中尽量量使用表的索索引字段，避避免做大表的的全表扫描。11十、 带通配符符（%）的likee语句11十一、用EXIISTS替代代IN11十二、用NOTT EXISSTS替代NOT INN12十三、尽可能的的用UNIOON-ALLL 替换UNIOON12十四、Ordeer by语语句建议13十五、避免使用用NOT13十六、使用DEECODE函函数来
6、减少处处理时间14十七、删除重复复记录14十八、如果可以以使用wheere条件，尽尽量不要在hhavingg中限制数据据14十九、尽量不要要使数据排序序14二十、使用提示示(Hintts)15第三章oraccle和sybasse的SQL区别15一、大小写115二、限制记录数数量15三、列的选择16四、连接166五、字符串函数数16六、日期函数16七、数数据类型转换换函数:17八、空值替代函函数:17九、sybasse的wheree语句执行 正则符号号，但是orracle99i不支持。17十、数字取取舍17第四章 跟踪SSQL执行计计划18一、理论188（一）ORACCLE优化器器18（二）访问
7、TAABLE的方方式18（三） 索引访访问方式19二、SET TTRACE跟跟踪sql执行计计划19第一章数据库库使用注意事事项一、对BOSSS1.5营帐帐库，营业网网址严格按照照要求进行配配置，不可随随意更换。营业网址要求按按照下面方式式进行分配配配置，如果随随意更换，会会增加营业主主机间的数据据交互，影响响数据库性能能，降低营业业工作效率。合肥、六安、阜阜阳、宿州、亳亳州、淮北、黄黄山、铜陵配配置: http:/10.1447.1322.5:70001/WeebRoott/logiin.jspp或者http:/main.webA11.amccc/WebRRoot/llogin.jsp芜湖、
8、蚌埠、淮淮南、马鞍山山、安庆、滁滁州、宣城、巢巢湖、池州，配配置如下:http:/10.1447.1322.6:80001/WeebRoott/logiin.jspp或者http:/main.webB11.amccc/WebRRoot/llogin.jsp二、不使用数据据库时请及时时关闭数据库库连接，但是是也不能频繁繁的连接和断断开数据库连接也是是数据库的宝宝贵资源，数数据库支持的的数据库连接接有限，当不不需要使用数数据库时，请请“优雅”的退出数据据库吧，如果果能正常退出出，请别“结束任务”或KILLL -9。如果正在执执行SQL的时候突然异异常终端，请请联系数据库库管理员检查查处理，以防防止
9、数据库一一直占用该SSQL相关资资源。三、执行了DMML操作，请请按业务规则则，不要忘记执行行COMMIIT或ROLLLBACKK。不要只执行语句句，而不控制制事务。当你你执行一条DDML语句时时，数据库会会为你分配锁锁、回滚段、RREDO LLOG BUUFFER等等资源。事务务结束后，这这些资源才能能得以释放。四、如果是查询询和统计不涉涉及到当天的的业务时，不不要在生产环环境里操作，在BBCV库中操作。BCV每天晚上上12点同步步一次，数据据和用户口令令、密码和生生产环境相同同。bcv是一个节节点的数据库库，所有的地地市的查询的的连接配置是是同一个，如如下： YZDBBCCV = (DES
10、CCRIPTIION = (ADDDRESSS_LISTT = (ADDREESS = (PROTTOCOL = TCPP)(HOSST = 110.1533.192.45)(POORT = 1521) ) (COONNECTT_DATAA = (SERVIICE_NAAME = yzdb) ) )五、关联表都很很大的查询和和统计也尽量量用BCV库库。六、生产环境营营业时间（特特别是营业高高峰时间，目目前是上午88:00-110:00，下下午3:000-4:000）禁止做大大数据量的查查询和统计，每个查询的执行时间要控制在分钟内。七、不要执行索索引和表的信息的的收集。八、编写程序的的时候，注意
11、意语句规规范，尽量使使用变量绑定定，减少共享享池的使用。九、按照标准要要求编写pll/sql等等程序，注意意事务的提交交、回滚和对对各种异常情情况的处理。十、要查看表字字段名或随机机的少量数据据时候，使用用desc、也也可以使用wwhere11=2或者rowccount 500000AND JJOB = MANAAGERAND 225 (SELECCT COUUNT(*) FROMM EMP WHEREE MGR=E.EMPPNO);(高效,执执行时间100.6秒)SELECCT *FROM EMP EEWHEREE 25 500000AND JOBB = MMANAGEER;八、在需要无条条
12、件删除表中中数据时，用用trunccate代替替delette。九、语句中尽量量使用表的索索引字段，避避免做大表的的全表扫描。例如Wheree子句中有联联接的列，即即使最后的联联接值为一个个静态值，也也不会使用索索引。 select * froom empployeeewhere ffirst_name|last_name =Beiill Clliton; 这条语句没有使使用基于laast_naame创建的的索引。 当采用下面这种种SQL语句句的编写，OOraclee系统就可以以采用基于llast_nname创建建的索引。 Select * froom empployeee where ffi
13、rst_name =Beiill aand laast_naame =Clitoon; 十、 带通配符符（%）的llike语句句例如SQLL语句： seleect * from emplooyee wwhere last_name like %cliiton%; 由于通配符符（%）在搜搜寻词首出现现，所以Orracle系系统不使用llast_nname的索索引。通配符符如此使用会会降低查询速速度。当通配配符出现在字字符串其他位位置时，优化化器就能利用用索引。在下下面的查询中中索引得到了了使用： seleect * from emplooyee wwhere last_name like c%;
14、十一、用EXIISTS替代代IN 在许多基于基础础表的查询中中,为了满足足一个条件,往往需要对对另一个表进进行联接.在在这种情况下下, 使用EEXISTSS(或NOTT EXISSTS)通常常将提高查询询的效率.低效: SELECT * FROM EMMP (基础础表) WHERE EEMPNO 0 AND DEPPTNO IIN (SEELECT DEPTNNO FROM DEEPT WHERE LLOC =MELB) 高效: SELECT * FROM EMMP (基础础表) WHERE EEMPNO 0 AND EXIISTS (SELECCT X FROM DEEPT WHERE DD
15、EPT.DDEPTNOO = EMMP.DEPPTNO AND LOCC = MELB)十二、用NOTT EXISSTS替代NNOT INN在子查询中,NNOT INN子句将执行行一个内部的的排序和合并并. 无论在在哪种情况下下,NOT IN都是最最低效的 (因为它对子子查询中的表表执行了一个个全表遍历). 为了避避免使用NOOT IN ,我们可以以把它改写成成外连接(OOuter Joinss)或NOTT EXISSTS. 例如: SELECT FROM EMMP WHERE DDEPT_NNO NOTT IN (SELECCT DEPPT_NO FROM DEEPT WHERE DDEPT
16、_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 DEEPT D WHERE DD.DEPTT_NO = E.DEEPT_NOO AND DEPPT_CATT = A);十三、尽可能的的用UNIOON-ALLL 替换UNNION
17、 当SQL语句需需要UNIOON两个查询询结果集合时时,这两个结结果集合会以以UNIONN-ALL的的方式被合并并, 然后在在输出最终结结果前进行排排序. 如果用UNIOON ALLL替代UNIION, 这这样排序就不不是必要了. 效率就会会因此得到提提高. 举例: 低效： SELECT ACCT_NUM, BALANNCE_AMMT FROM DEEBIT_TTRANSAACTIONNS WHERE TTRAN_DDATE = 31-DEC-995 UNION SELECT ACCT_NUM, BALANNCE_AMMT FROM DEEBIT_TTRANSAACTIONNS WHERE T
18、TRAN_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-DEC-995十四、Ordeer by语语句建议ORDER BBY语句决定定了Oraccle如何将将返回的查询询结果排序。OOrder by语句对对要排序的列列没有什么特特别的限
19、制，也也可以将函数数加入列中（象象联接或者附附加等）。任任何在Ordder byy语句的非索索引项或者有有计算表达式式都将降低查查询速度。 仔细检查ordder byy语句以找出出非索引项或或者表达式，它它们会降低性性能。解决这这个问题的办办法就是重写写orderr by语句句以使用索引引，也可以为为所使用的列列建立另外一一个索引，同同时应绝对避避免在ordder byy子句中使用用表达式。十五、避免使用用NOT 在查询时经常在在wheree子句使用一一些逻辑表达达式，如大于于、小于、等等于以及不等等于等等，也也可以使用aand（与）、oor（或）以以及not（非非）。NOTT可用来对任任何逻
20、辑运算算符号取反。下下面是一个NNOT子句的的例子： . wheree not (stattus =VALIDD) 如果要使用用NOT，则则应在取反的的短语前面加加上括号，并并在短语前面面加上NOTT运算符。NNOT运算符符包含在另外外一个逻辑运运算符中，这这就是不等于于（）运运算符。换句句话说，即使使不在查询wwhere子子句中显式地地加入NOTT词，NOTT仍在运算符符中，见下例例： . wheree stattus INVAALID; 再看看下面这个例例子： sellect * fromm emplloyee wheree salaary33000; 对这个个查询，可以以改写为不使使用N
21、OT： sellect * fromm emplloyee wheree salaary30000; 虽然这两两种查询的结结果一样，但但是第二种查查询方案会比比第一种查询询方案更快些些。第二种查查询允许Orracle对对salarry列使用索索引，而第一一种查询则不不能使用索引引。十六、使用DEECODE函函数来减少处处理时间 使用DECODDE函数可以以避免重复扫扫描相同记录录或重复连接接相同的表. 例如: SELECT COUNTT(*)，SSUM(SAAL) FROMEMMP WHERE DDEPT_NNO = 00020 AND ENAAME LIIKESMITHH%; SELECT
22、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, SUM(DECCODE(DDEPT_NNO,00220,SALL,NULLL) D00020_SSAL, SUM(DECCODE(DDEPT_NNO,00330,S
23、ALL,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); 十八、如果可以以使用wheere条件，尽尽量不要在hhavingg中限制数据据十九、尽量不要要使数据排序序引起排序的条件件- Orderr by- Groupp
24、by- Unionn,inteersectt,minuus- Distiinct二十、使用提示示(Hintts)对于表的访问,可以使用两两种Hintts：FULLL 和 RROWID l FULL hiint 告诉诉ORACLLE使用全表表扫描的方式式访问指定表表. 例如: SELECT /*+ FFULL(EEMP) */ * FROM EMMP WHERE EEMPNO = 78993; l ROWID hhint 告告诉ORACCLE使用TTABLE ACCESSS BY ROWIDD的操作访问问表. 通常, 你需要要采用TABBLE ACCCESS BY ROOWID的方方式特别是当当
25、访问大表的的时候, 使使用这种方式式, 你需要要知道ROIIWD的值或或者使用索引引。 如果一个大表没没有被设定为为缓存(CAACHED)表而你希望望它的数据在在查询结束是是仍然停留在在SGA中,你就可以使使用CACHHE hinnt 来告诉诉优化器把数数据保留在SSGA中。 通常CAACHE hhint 和和 FULLL hintt 一起使用用。 例如: SELECT /*+ FFULL(WWORKERR) CACCHE(WOORKER)*/ * FROM WOORK; 索引hint 告诉ORAACLE使用用基于索引的的扫描方式. 你不必说说明具体的索索引名称 例如: SELECT /*+
26、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中中限制纪录的的数量,
27、需要要用 EXEEC SQLL SET ROWCOOUNT nn， 用完完需要执行EEXEC SSQL SEET ROWWCOUNTT 0 恢复复； 而 ORACLLE 中只需需要在 SQQL中用 SSELECTT * FRROM tbbl_namme wheere roowcounntn来限限制即可。三、列的选择ORACLE执执行数据查询询的时候，SSELECTT语句必须选选择针对的数数据表。在OOraclee数据库内有有一种特殊的的表DUALL。从DUAAL表选择数数据常被用来来通过SELLECT语句句计算常数表表达式，由于于DUAL只只有一行数据据，所以常数数只返回一次次。Oracle下
28、下的DUALL查询如下所所示：SELECCT x FROMM duall在sybasee中，查询则则是下面这个个样子：SELECCT x四、连接Oraclee用| 符符号作为连接接符，而syybase的的连接符是加加号：+ 。Oraclee查询如下所所示：Selecct Name | Last Name Fromm tablleNamee对应的sybbase查询询如下所示：Selecct Name + Last Name五、字符串函数数返回字符串长度度函数sybase: chaar_lenngth(sstringg) 或daatalenngth(sstringg)oracle: lenngth
29、(sstringg)LENGTH和和LENsybase: SELEECT LEEN(SQQLMAG) Leength in chharacttersoracle: SELEECT LEENGTH(SQLMMAG) Lenggth inn charracterrs FRROM DUUAL;六、日期函数取当前系统日期期时间sybase: geetdatee() 返返回dateetime eeg:sellect ggetdatte() oracle: syssdate 返回ddate egg:seleect syysdatee fromm duall;日期的加法sybase:seleect daat
30、eaddd(mm,112,gettdate()oracle:seleect addd_monnths(ssysdatte,12) fromm duall日期的减法syybase:SELECCT dattedifff(dd, GetDaate(),dateaadd(mmm,12,ggetdatte()oracle:SELECCT syssdate -add_monthhs(syssdate,12) FFROM ddual七、数据类型转转换函数:sybase中中转换函数为为conveert(daatatyppe,exppressiion,sstyle)在oraclee中不可用,应用以下转转换函数:
31、日期转换字符tto_chaar(datte)例如:seleect too_charr(sysddate,yyyy/mm/ddd hh244:mi:sss) froom duaal;结果: 19999/099/08 116:25:30 selecct to_char(sysdaate,yyyymmmdd) froom duaal;结果: 1999909008数字转换字符tto_chaar(nummbers)字符转换日期tto_datte(strring)例如:seleect too_datee(1999/09/088 16:225:30,yyyy/mm/ddd hh244:mi:sss) fro
32、m duual;字符转换数字tto_ nuumber(strinng)八、空值替代函函数:sybase中中用isnulll(exppr1,exxpr2) oracle中不能用iisnulll(),只能能用nvl(expr11,exprr2)例如:sybase：selecct isnnull(ppro_taable_sstatuss, 0) froom poss.prodduct；oracle：selecct nvll(pro_tablee_stattus, 0) froom poss.prodduct；九、sybasse的wheere语句执执行 正正则符号，但是ooraclee9i不支持持。十
33、、数字取舍Oraclle数据库内内有一个TRRUNC函数数，该函数返返回m位十进进制数的n位位；如果省略略m则n就是是0位。m的的值可以为负负，表示截去去小数点左边边m位数字。在Sybaase下可以以用Rounnd或者Flloor。以下是Orracle查查询：SELECCT TRUUNC(155.79,11) Trruncatte FRROM DUUAL;下面是同类类查询的syybase版版本：SELECCT ROUUND(155.79, 0) rooundedd , ROOUND(115.79, 0,1) trunncateddSELECT FLOORR(ROUNND(15.79, 00),
34、 FFLOOR(ROUNDD(15.779, 0,1) )第四章 跟踪SSQL执行计计划一、理论（一）ORACCLE优化器器l Oracle的的优化器有33种 基于于规则 RUULE 基于于成本 COOST 基于于选择 CHHOOSEl 设置缺省的优化化器,可以通通过对iniit.oraa文件中OPPTIMIZZER_MOODE参数设设置，也可以以在会话(ssessioon)级对其其进行覆盖.l 如果OPTIMMZER_MMODE=RRULE，则则激活基于规规则的优化器器(RBO)。基于规则则的优化器按按照一系列的的语法规则来来推测可能执执行路径和比比较可替换的的执行路径。l 如果OPTIMM
35、ZER_MMODE=CCOST，则则激活基于成成本的优化器器(CBO)。它使用AANALYZZE语句来生生成数据库对对象的统计数数据。这些统统计数据包括括表的行数、平平均长度及索索引中不同的的关键字数等等。基于这些些统计数据，成成本优化器可可以计算出可可获得的执行行路径的成本本。并选择具具有最小的成成本执行路径径。在CBOO模式下，需需要经常运行行ANALYYZE 命令令来确保数据据的准确性。l 如果OPTIMMZER_MMODE=CCHOOSEE，实际的优优化器模式将将和是否运行行过anallyze命令令有关. 如如果tablle已经被aanalyzze过, 优优化器模式将将自动成为CCBO
36、 , 反之，数据据库将采用RRULE形式式的优化器。（二）访问TAABLE的方方式ORACLE 采用两种访访问表中记录录的方式:l 全表扫描全表扫描就是顺顺序地访问表表中每条记录录.ORACLLE采用一次次读入多个数数据块的方式式优化全表扫扫描。l ROWID定为为访问ORACLEE采用索引实实现了数据和和存放数据的的物理位置(ROWIDD)之间的联联系。 通常常索引提供了了快速访问RROWID的的方法,因此此那些基于索索引列的查询询就可以得到到性能上的提提高。（三） 索引访访问方式 Oracle有有两种索引访访问方式l 索引唯一扫描(INDEXXUNIQUUESCAN) 如:表LOADDIN
37、G有两两个索引:建立在LOOADINGG列上的唯一一性索引LOOADINGG_PK和建建立在MANNAGER列列上的非唯一一性索引IDDX_MANNAGER。SELECT* FROMLOADIING WHEREELOADIING=ROSEHILL;在内部，上上述SQL将将被分成两步步执行，首先先，LOADDING_PPK索引将通过过索引唯一扫扫描的方式被被访问，获得得相对应的RROWID，通通过ROWIID访问表的的方式执行下下一步检索。如果被检检索返回的列列包括INDDEX列中，OORACLEE将不执行第第二步的处理理(通过ROOWID访问问表)。因为检索数数据保存在索索引中，单单单访问索引就就可以完全满满足查询结果果。SELECCTLOADIINGFROMMLOADDING WHEREELOADIING=ROSEEHILL;l 2、索引范围查查询(INDDEXRANGEESCAN)适用于两种情况况:1