ORACLE数据库操作手册210

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

2、 l _Toc ORACLE 数据库库操作手册2.0 PAGEREF _Toc h 1f。 HYPERLINK l _Toc 第一章数据库使用注注意事项 PAGEREF _Toc h 5b。 HYPERLINK l _Toc 二、不使用数据库时请请及时关闭数据库库连接，但是也不不能频繁的连接和和断开 PAGEREF _Toc h 5F。 HYPERLINK l _Toc 三、执行了DML操作作，请按业务规则则，不要忘记执行行COMMIT或ROLLBACK。 PAGEREF _Toc h 5l。 HYPERLINK l _Toc 四、如果是查询和统计计不涉及到当天的的业务时，不要在在生产环境里操

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

4、oc h 6S。 HYPERLINK l _Toc 八、编写程序的时候，注注意语句规规范，尽量使用变变量绑定，减少共共享池的使用。 PAGEREF _Toc h 6a。 HYPERLINK l _Toc 九、按照标准要求编写写pl/sql等程程序，注意事务的的提交、回滚和对对各种异常情况的的处理。 PAGEREF _Toc h 6h。 HYPERLINK l _Toc 十、要查看表字段名或或随机的少量数据据时候，使用desc、也可以以使用where1=2或者rowcountn来查看，而尽尽量不要直接执行行select * from tablename，然后kill会话。 PAGEREF _To

5、c h 6g。 HYPERLINK l _Toc 十一、尽量使用索引，避避免出现全表扫描描，性能影响比单单机更大。 PAGEREF _Toc h 6s。 HYPERLINK l _Toc 十二、对分区表建立索索引时，使用local选项。 PAGEREF _Toc h 6A。 HYPERLINK l _Toc 十三、不要在事务中引引入Trigger，建建议在事务中实现现。 PAGEREF _Toc h 66。 HYPERLINK l _Toc 十四、批量更新数据的的大的事务分次提提交。 PAGEREF _Toc h 62。 HYPERLINK l _Toc 十五、客户端的配置。 PAGEREF

6、_Toc h 7y。 HYPERLINK l _Toc 十六、修改密码。 PAGEREF _Toc h 7b。 HYPERLINK l _Toc 第二章SQL编写注注意事项 PAGEREF _Toc h 9f。 HYPERLINK l _Toc 一、SELECT 子子句中避免使用* PAGEREF _Toc h 9k。 HYPERLINK l _Toc 二、查找总记录数时，尽尽量不要用count(*)，而要指定定一个有索引的字字段。 PAGEREF _Toc h 9a。 HYPERLINK l _Toc 三、将大的历史表创建建为分区表，便于于数据转储和删除除。 PAGEREF _Toc h 9

7、s。 HYPERLINK l _Toc 四、使用分区表进行查查询时，尽量把分分区键作为查询条条件的第一个条件件。 PAGEREF _Toc h 9i。 HYPERLINK l _Toc 五、Sequence采用cache/noorder，如果在使用sequence上的列建索引，建议加大cache值。 PAGEREF _Toc h 9T。 HYPERLINK l _Toc 六、在FROM子句中中包含多个表的情情况下，选择记录录条数最少的表作作为基础表，放在在FROM子句的最最后面。 PAGEREF _Toc h 9v。 HYPERLINK l _Toc 七、WHERE子句中中的连接顺序 PAGE

8、REF _Toc h 10C。 HYPERLINK l _Toc 八、在需要无条件删除除表中数据时，用用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函数数来减少处理时间间 PAGEREF _Toc h 14R。 HYPERLINK l _Toc 十七、删除重复记录 PAGEREF _Toc h 145。 HY

10、PERLINK l _Toc 十八、如果可以使用where条件，尽尽量不要在having中限限制数据 PAGEREF _Toc h 14f。 HYPERLINK l _Toc 十九、尽量不要使数据据排序 PAGEREF _Toc h 14U。 HYPERLINK l _Toc 二十、使用提示(Hints) PAGEREF _Toc h 15z。 HYPERLINK l _Toc 第三章oracle和和sybase的SQL区别 PAGEREF _Toc h 15f。 HYPERLINK l _Toc 一、大小写 PAGEREF _Toc h 152。 HYPERLINK l _Toc 二、限制记

11、录数量 PAGEREF _Toc h 15Y。 HYPERLINK l _Toc 三、列的选择 PAGEREF _Toc h 16z。 HYPERLINK l _Toc 四、连接 PAGEREF _Toc h 168。 HYPERLINK l _Toc 五、字符串函数 PAGEREF _Toc h 16r。 HYPERLINK l _Toc 六、日期函数 PAGEREF _Toc h 16f。 HYPERLINK l _Toc 七、数据类型转换函数数: PAGEREF _Toc h 175。 HYPERLINK l _Toc 八、空值替代函数: PAGEREF _Toc h 17f。 HYPE

12、RLINK l _Toc 九、sybase的where语句执执行 正则符号，但但是oracle9i不支持。 PAGEREF _Toc h 17k。 HYPERLINK l _Toc 十、数字取舍舍 PAGEREF _Toc h 17a。 HYPERLINK l _Toc 第四章 跟踪SQL执执行计划 PAGEREF _Toc h 18w。 HYPERLINK l _Toc 一、理论 PAGEREF _Toc h 18T。 HYPERLINK l _Toc （一）ORACLE优优化器 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执行计划 PAGEREF _Toc h 19q。第一章数据库使用注注意事项一、对BOSS1.5营营帐库，营业网址址严格按照要求进进行配置，不可随随意更换。D。营业网址要求按照下面面方式进行分配配配置，如果随意更更换，会增加营业业主机间的数据交交互，影响数据库库性能，降低营业业工作效率。U。合肥、六安、阜阳、宿宿州、亳州、淮北北、黄山、铜陵配配置: :7001/WebRoot/login.jspV。或者

14、http:/main.webA1.amcc/WebRoot/login.jspm。芜湖、蚌埠、淮南、马马鞍山、安庆、滁滁州、宣城、巢湖湖、池州，配置如如下:I。:8001/WebRoot/login.jsph。或者http:/main.webB1.amcc/WebRoot/login.jspu。二、不使用数据库时请请及时关闭数据库库连接，但是也不不能频繁的连接和和断开3。数据库连接也是数据库库的宝贵资源，数数据库支持的数据据库连接有限，当当不需要使用数据据库时，请“优雅”的退出数据库吧吧，如果能正常退退出，请别“结束任务”或KILL -9。如果正在执行SQL的时候突然异常终终端，请联系数据据库

15、管理员检查处处理，以防止数据据库一直占用该SQL相相关资源。y。三、执行了DML操作作，请按业务规则，不要忘记执行COMMIT或或ROLLBACK。3。不要只执行语句，而不不控制事务。当你你执行一条DML语语句时，数据库会会为你分配锁、回回滚段、REDO LOG BUFFER等资源。事务结束后，这些资源才能得以释放。h。四、如果是查询和统计计不涉及到当天的的业务时，不要在在生产环境里操作，在BCV库库中操作。BCV每天晚上12点点同步一次，数据据和用户口令、密密码和生产环境相相同。D。bcv是一个节点的数数据库，所有的地地市的查询的连接接配置是同一个，如如下： 3。 YZDBBCV = (DE

16、SCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 5)(PORT = 1521)1。 ) (CONNECT_DATA = (SERVICE_NAME = yzdb) ) )五、关联表都很大的查查询和统计也尽量量用BCV库。六、生产环境营业时间间（特别是营业高高峰时间，目前是是上午8:00-10:00，下下午3:00-4:00）禁止做大大数据量的查询和和统计，每个查询询的执行时间要控控制在分钟内。E。七、不要执行索引和表表的信息的收集。八、编写程序的时候，注意语句规范，尽量使用变量绑定，减少共享池的使用。Q。九、按照标准

17、要求编写写pl/sql等等程序，注意事务务的提交、回滚和和对各种异常情况况的处理。6。十、要查看表字段名或或随机的少量数据据时候，使用desc、也可以使用where1=2或者rowcount 50000AND JOB = MANAGERAND 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);t。(高效,执行时间间10.6秒)SELECT *FROM EMP EWHERE 25 50000AND JOB = MANAGER;八、在需要无条件删除除表中数据时，用用truncate代代替delete。N。九、语句中尽量使用表表的索引字段，避避免做大表的

18、全表表扫描。例如Where子句中中有联接的列，即即使最后的联接值值为一个静态值，也也不会使用索引。 E。select * from employeewhere first_name|last_name =Beill Cliton; K。这条语句没有使用基于于last_name创创建的索引。 当采用下面这种SQL语语句的编写，Oracle系系统就可以采用基基于last_name创创建的索引。 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%; 由于于通配符（%）在在搜寻词首出现，所所以Oracle系系统不使用last_name的索引引。通配符如此使使用会降低查询速速度。当通配符出出现在字符串其他他位置时，优化器器就能利用索引。在在下面的查询中索索引得到了使用： select * from employee where last_name like c%;J。十一、用EXISTS替替代IN 在许多基于基础表的查查询中,为了满足足一个条件,往往往需要对另一个表表进行联接.在这这种情况下, 使使用EXISTS(或NOT EXISTS)通

20、常将提高查询询的效率.S。低效: 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子句将执行行一个内部的排序序和合并. 无论论在哪种情况下,NOT IN

21、都是最低效效的 (因为它对对子查询中的表执执行了一个全表遍遍历). 为了避避免使用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 当SQL语句需要UNION两两个查询结果集合合时,这两个结果果集合会以UNION-ALL的方式被被合并, 然后在在输出最终结果前前进行排序. m。如果用UNION ALL替替代UNION, 这样排序就不是是必要了. 效率率就会因此得到提提高. 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如如何将返回的查询询结果排序。Order by语句对要排排序的列没有什么么特别的限制，也也可以将函数加入入列中（象联接或或者附加等）。任任何在Order by语句的非索索引项或者有计算算表达式都将降低低查询速度。 J。仔细检查order by语句以找出出非索引项或者表表达式，它们会降降低性能。解决这这个问题的办法就就是重写order by语句以使用用索引，也可以为为所使用的列建立立另外一个索引，同同时应绝对避免在在order by子子句中使用表达式式。z。十五、避免使用NOT 在查询时

25、经常在where子子句使用一些逻辑辑表达式，如大于于、小于、等于以以及不等于等等，也也可以使用and（与与）、or（或）以以及not（非）。NOT可可用来对任何逻辑辑运算符号取反。下下面是一个NOT子子句的例子： . where not (status =VALID) 如果要要使用NOT，则则应在取反的短语语前面加上括号，并并在短语前面加上上NOT运算符。NOT运运算符包含在另外外一个逻辑运算符符中，这就是不等等于（）运算算符。换句话说，即即使不在查询where子子句中显式地加入入NOT词，NOT仍仍在运算符中，见见下例： . where status INVALID; 再看下下面这个例子：

26、select * from employee where salary3000; 对这个查询询，可以改写为不不使用NOT： select * from employee where salary3000; 虽然这两种查查询的结果一样，但但是第二种查询方方案会比第一种查查询方案更快些。第第二种查询允许Oracle对对salary列列使用索引，而第第一种查询则不能能使用索引。2。十六、使用DECODE函函数来减少处理时时间 使用DECODE函数数可以避免重复扫扫描相同记录或重重复连接相同的表表. 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函函数高效地得到相相同结果 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函函数也可以运用于于GROUP BY 和ORDER BY子句中.E。十七、删除重复记录DELETE FROM EMP E WHERE E.ROWID (SELECT MIN(X.ROWID) 5。FROM EMP X WHERE X.EMP_NO = E.EMP_NO); 十八、如果可以使用where条条件，尽量不要在在having中中限制数据H。十九、尽量不要使数据据排序引起排序的条件- Order by- Group by- Union,int

29、ersect,minus- Distinct二十、使用提示(Hints)对于表的访问,可以使使用两种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的的方式特别是当访访问大表的时候, 使用这种方式, 你需要知道ROIWD的的值或者使用索引引。

30、c。如果一个大表没有被设设定为缓存(CACHED)表而你希望它的的数据在查询结束束是仍然停留在SGA中中,你就可以使用用CACHE hint 来告诉优化器把把数据保留在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 等等。可以根据具体情情况具体使用。T。第三章oracle和和sybase的的SQL区别一、大小写SYBASE的SQL中中数据库名、表名名和列名分大小写写，应遵循定义时时的写法；ORACLE 并不区分。O。二