ORACLE数据库操作手册21

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 l _Toc182711276 ORACLE

2、 数据库操作手册2.0 PAGEREF _Toc182711276 h 1 HYPERLINK l _Toc182711277 第一章数据库运用本卷须知 PAGEREF _Toc182711277 h 5 HYPERLINK l _Toc182711278 二、不运用数据库时请及时封锁数据库衔接，但是也不能频繁的衔接和断开 PAGEREF _Toc182711278 h 5 HYPERLINK l _Toc182711279 三、执行了DML操作，请按业务规那么，不要忘记执行COMMIT或ROLLBACK。 PAGEREF _Toc182711279 h 5 HYPERLINK l _Toc1

3、82711280 四、假设是查询和统计不涉及到当天的业务时，不要在消费环境里操作，在BCV库中操作。BCV每天晚上12点同步一次，数据和用户口令、密码和消费环境一样。 PAGEREF _Toc182711280 h 5 HYPERLINK l _Toc182711281 五、关联表都很大的查询和统计也尽量用BCV库。 PAGEREF _Toc182711281 h 6 HYPERLINK l _Toc182711282 六、消费环境营业时间特别是营业顶峰时间，目前是上午8:00-10:00，下午3:00-4:00制止做大数据量的查询和统计，每个查询的执行时间要控制在分钟内。 PAGEREF _

4、Toc182711282 h 6 HYPERLINK l _Toc182711283 七、不要执行索引和表的信息的搜集。 PAGEREF _Toc182711283 h 6 HYPERLINK l _Toc182711284 八、编写程序的时候，留意语句规范，尽量运用变量绑定，减少共享池的运用。 PAGEREF _Toc182711284 h 6 HYPERLINK l _Toc182711285 九、按照规范要求编写pl/sql等程序，留意事务的提交、回滚和对各种异常情况的处置。 PAGEREF _Toc182711285 h 6 HYPERLINK l _Toc182711286 十、要查

5、看表字段名或随机的少量数据时候，运用desc、也可以运用where1=2或者rowcountn来查看，而尽量不要直接执行select * from tablename，然后kill会话。 PAGEREF _Toc182711286 h 6 HYPERLINK l _Toc182711287 十一、尽量运用索引，防止出现全表扫描，性能影响比单机更大。 PAGEREF _Toc182711287 h 6 HYPERLINK l _Toc182711288 十二、对分区表建立索引时，运用local选项。 PAGEREF _Toc182711288 h 6 HYPERLINK l _Toc182711

6、289 十三、不要在事务中引入Trigger，建议在事务中实现。 PAGEREF _Toc182711289 h 6 HYPERLINK l _Toc182711290 十四、批量更新数据的大的事务分次提交。 PAGEREF _Toc182711290 h 6 HYPERLINK l _Toc182711291 十五、客户端的配置。 PAGEREF _Toc182711291 h 7 HYPERLINK l _Toc182711292 十六、修正密码。 PAGEREF _Toc182711292 h 7 HYPERLINK l _Toc182711293 第二章SQL编写本卷须知 PAGERE

7、F _Toc182711293 h 9 HYPERLINK l _Toc182711294 一、SELECT 子句中防止运用* PAGEREF _Toc182711294 h 9 HYPERLINK l _Toc182711295 二、查找总记录数时，尽量不要用count(*)，而要指定一个有索引的字段。 PAGEREF _Toc182711295 h 9 HYPERLINK l _Toc182711296 三、将大的历史表创建为分区表，便于数据转储和删除。 PAGEREF _Toc182711296 h 9 HYPERLINK l _Toc182711297 四、运用分区表进展查询时，尽量把

8、分区键作为查询条件的第一个条件。 PAGEREF _Toc182711297 h 9 HYPERLINK l _Toc182711298 五、Sequence采用cache/noorder，假设在运用sequence上的列建索引，建议加大cache值。 PAGEREF _Toc182711298 h 9 HYPERLINK l _Toc182711299 六、在FROM子句中包含多个表的情况下，选择记录条数最少的表作为根底表，放在FROM子句的最后面。 PAGEREF _Toc182711299 h 9 HYPERLINK l _Toc182711300 七、WHERE子句中的衔接顺序 PAG

9、EREF _Toc182711300 h 10 HYPERLINK l _Toc182711301 八、在需求无条件删除表中数据时，用truncate替代delete。 PAGEREF _Toc182711301 h 11 HYPERLINK l _Toc182711302 九、语句中尽量运用表的索引字段，防止做大表的全表扫描。 PAGEREF _Toc182711302 h 11 HYPERLINK l _Toc182711303 十、 带通配符%的like语句 PAGEREF _Toc182711303 h 11 HYPERLINK l _Toc182711304 十一、用EXISTS替代

10、IN PAGEREF _Toc182711304 h 11 HYPERLINK l _Toc182711305 十二、用NOT EXISTS替代NOT IN PAGEREF _Toc182711305 h 12 HYPERLINK l _Toc182711306 十三、尽能够的用UNION-ALL 交换UNION PAGEREF _Toc182711306 h 12 HYPERLINK l _Toc182711307 十四、Order by语句建议 PAGEREF _Toc182711307 h 13 HYPERLINK l _Toc182711308 十五、防止运用NOT PAGEREF _

11、Toc182711308 h 13 HYPERLINK l _Toc182711309 十六、运用DECODE函数来减少处置时间 PAGEREF _Toc182711309 h 14 HYPERLINK l _Toc182711310 十七、删除反复记录 PAGEREF _Toc182711310 h 14 HYPERLINK l _Toc182711311 十八、假设可以运用where条件，尽量不要在having中限制数据 PAGEREF _Toc182711311 h 14 HYPERLINK l _Toc182711312 十九、尽量不要使数据排序 PAGEREF _Toc1827113

12、12 h 14 HYPERLINK l _Toc182711313 二十、运用提示(Hints) PAGEREF _Toc182711313 h 15 HYPERLINK l _Toc182711314 第三章oracle和sybase的SQL区别 PAGEREF _Toc182711314 h 15 HYPERLINK l _Toc182711315 一、大小写 PAGEREF _Toc182711315 h 15 HYPERLINK l _Toc182711316 二、限制记录数量 PAGEREF _Toc182711316 h 15 HYPERLINK l _Toc182711317 三

13、、列的选择 PAGEREF _Toc182711317 h 16 HYPERLINK l _Toc182711318 四、衔接 PAGEREF _Toc182711318 h 16 HYPERLINK l _Toc182711319 五、字符串函数 PAGEREF _Toc182711319 h 16 HYPERLINK l _Toc182711320 六、日期函数 PAGEREF _Toc182711320 h 16 HYPERLINK l _Toc182711321 七、数据类型转换函数: PAGEREF _Toc182711321 h 17 HYPERLINK l _Toc1827113

14、22 八、空值替代函数: PAGEREF _Toc182711322 h 17 HYPERLINK l _Toc182711323 九、sybase的where语句执行 正那么符号，但是oracle9i不支持。 PAGEREF _Toc182711323 h 17 HYPERLINK l _Toc182711324 十、数字取舍 PAGEREF _Toc182711324 h 17 HYPERLINK l _Toc182711325 第四章 跟踪SQL执行方案 PAGEREF _Toc182711325 h 18 HYPERLINK l _Toc182711326 一、实际 PAGEREF _

15、Toc182711326 h 18 HYPERLINK l _Toc182711327 一ORACLE优化器 PAGEREF _Toc182711327 h 18 HYPERLINK l _Toc182711328 二访问TABLE的方式 PAGEREF _Toc182711328 h 18 HYPERLINK l _Toc182711329 三 索引访问方式 PAGEREF _Toc182711329 h 19 HYPERLINK l _Toc182711330 二、SET TRACE跟踪sql执行方案 PAGEREF _Toc182711330 h 19第一章数据库运用本卷须知一、对BOS

16、S1.5营帐库，营业网址严厉按照要求进展配置，不可随意改换。营业网址要求按照下面方式进展分配配置，假设随意改换，会添加营业主机间的数据交互，影响数据库性能，降低营业任务效率。合肥、六安、阜阳、宿州、亳州、淮北、黄山、铜陵配置: 10.147.132.5:7001/WebRoot/login.jsp或者main.webA1.amcc/WebRoot/login.jsp芜湖、蚌埠、淮南、马鞍山、安庆、滁州、宣城、巢湖、池州，配置如下:10.147.132.6:8001/WebRoot/login.jsp或者main.webB1.amcc/WebRoot/login.jsp二、不运用数据库时请及时封

17、锁数据库衔接，但是也不能频繁的衔接和断开数据库衔接也是数据库的珍贵资源，数据库支持的数据库衔接有限，当不需求运用数据库时，请“优雅的退出数据库吧，假设能正常退出，请别“终了义务或KILL -9。假设正在执行SQL的时候忽然异常终端，请联络数据库管理员检查处置，以防止数据库不断占用该SQL相关资源。三、执行了DML操作，请按业务规那么，不要忘记执行COMMIT或ROLLBACK。不要只执行语句，而不控制事务。当他执行一条DML语句时，数据库会为他分配锁、回滚段、REDO LOG BUFFER等资源。事务终了后，这些资源才干得以释放。四、假设是查询和统计不涉及到当天的业务时，不要在消费环境里操作，

18、在BCV库中操作。BCV每天晚上12点同步一次，数据和用户口令、密码和消费环境一样。bcv是一个节点的数据库，一切的地市的查询的衔接配置是同一个，如下： YZDBBCV = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.153.192.45)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = yzdb) ) )五、关联表都很大的查询和统计也尽量用BCV库。六、消费环境营业时间特别是营业顶峰时间，目前是上午8:00-10:00，下午3:00-4:00制止做大数据

19、量的查询和统计，每个查询的执行时间要控制在分钟内。七、不要执行索引和表的信息的搜集。八、编写程序的时候，留意语句规范，尽量运用变量绑定，减少共享池的运用。九、按照规范要求编写pl/sql等程序，留意事务的提交、回滚和对各种异常情况的处置。十、要查看表字段名或随机的少量数据时候，运用desc、也可以运用where1=2或者rowcount 50000AND JOB = MANAGERAND 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);(高效,执行时间10.6秒)SELECT *FROM EMP EWHERE 25 50000AND JOB =

20、MANAGER;八、在需求无条件删除表中数据时，用truncate替代delete。九、语句中尽量运用表的索引字段，防止做大表的全表扫描。例如Where子句中有联接的列，即使最后的联接值为一个静态值，也不会运用索引。 select * from employeewhere first_name|last_name =Beill Cliton; 这条语句没有运用基于last_name创建的索引。 当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。 Select * from employee where first_name =Beill and las

21、t_name =Cliton; 十、 带通配符%的like语句例如SQL语句： select * from employee where last_name like %cliton%; 由于通配符%在搜索词首出现，所以Oracle系统不运用last_name的索引。通配符如此运用会降低查询速度。当通配符出如今字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了运用： select * from employee where last_name like c%;十一、用EXISTS替代IN 在许多基于根底表的查询中,为了满足一个条件,往往需求对另一个表进展联接.在这种情况下, 运用E

22、XISTS(或NOT EXISTS)通常将提高查询的效率.低效: 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子句将执行一个内部的排序和合并. 无论在

23、哪种情况下,NOT IN都是最低效的 (由于它对子查询中的表执行了一个全表遍历). 为了防止运用NOT IN ,我们可以把它改写成外衔接(Outer Joins)或NOT EXISTS. 例如: SELECT FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO 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(+) = A

24、 (方法二: 最高效) 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的方式被合并, 然后在输出最终结果前进展排序. 假设用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 举例: 低效： SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT

25、_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 TRAN_DATE = 31-DE

26、C-95十四、Order by语句建议ORDER BY语句决议了Oracle如何将前往的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数参与列中象联接或者附加等。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。 仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。处理这个问题的方法就是重写order by语句以运用索引，也可以为所运用的列建立另外一个索引，同时应绝对防止在order by子句中运用表达式。十五、防止运用NOT 在查询时经常在where子句运用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以运用and与

27、、or或以及not非。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子： . where not (status =VALID) 假设要运用NOT，那么应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于运算符。换句话说，即使不在查询where子句中显式地参与NOT词，NOT仍在运算符中，见下例： . where status INVALID; 再看下面这个例子： select * from employee where salary3000; 对这个查询，可以改写为不运用NOT： select * from employ

28、ee where salary3000; 虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列运用索引，而第一种查询那么不能运用索引。十六、运用DECODE函数来减少处置时间 运用DECODE函数可以防止反复扫描一样记录或反复衔接一样的表. 例如: SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0020 AND ENAME LIKESMITH%; SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0030 AND ENAME LIKES

29、MITH%; 可以用DECODE函数高效地得到一样结果 SELECT COUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,X,NULL) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030_SAL FROM EMP WHERE ENAME LIKE SMITH%; 类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.十七、删除反复记

30、录DELETE FROM EMP E WHERE E.ROWID (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO); 十八、假设可以运用where条件，尽量不要在having中限制数据十九、尽量不要使数据排序引起排序的条件- Order by- Group by- Union,intersect,minus- Distinct二十、运用提示(Hints)对于表的访问,可以运用两种Hints：FULL 和 ROWID FULL hint 通知ORACLE运用全表扫描的方式访问指定表. 例如: SELECT /*+ FULL(EM

31、P) */ * FROM EMP WHERE EMPNO = 7893; ROWID hint 通知ORACLE运用TABLE ACCESS BY ROWID的操作访问表. 通常, 他需求采用TABLE ACCESS BY ROWID的方式特别是当访问大表的时候, 运用这种方式, 他需求知道ROIWD的值或者运用索引。 假设一个大表没有被设定为缓存(CACHED)表而他希望它的数据在查询终了是依然停留在SGA中,他就可以运用CACHE hint 来通知优化器把数据保管在SGA中。 通常CACHE hint 和 FULL hint 一同运用。 例如: SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ * FROM WORK; 索引hint 通知ORACLE运用基于索引的扫描方式. 他不用阐明详细的索引称号 例如: SELECT /*+ INDEX(a index_name) */ LODGING FROM LODGING aWHERE MANAGER = BILL GATES; ORACLE hints 还包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等。可以根据详细情