数据库操作手册.doc

ORACLE 数据库操作手册2.0中国通信集团公司安徽有限公司计费业务部7年11月修改记录版本日期编辑者编辑内容1.02023/2/10见春蕾形成初稿1.12023/8/31见春蕾根据实际情况修订部分章节2.02023/11/05见春蕾一、在第一章（数据库使用注意事项）中增长了关于批量更新数据的大事务分次提交的规定、客户端的配置方法、修改密码方法；二、在第二章（SQL编写注意事项）中增长了关于提醒(Hints)的使用。三、增长了第四章（跟踪SQL执行计划），阐述了相关的理论知识和SQL执行计划的跟踪方法。目录ORACLE 数据库操作手册2.01第一章数据库使用注意事项5二、不使用数据库时请及时关闭数据库连接，但是也不能频繁的连接和断开5三、执行了DML操作，请按业务规则，不要忘掉执行COMMIT或ROLLBACK。5四、假如是查询和记录不涉及到当天的业务时，不要在生产环境里操作，在BCV库中操作。BCV天天晚上12点同步一次，数据和用户口令、密码和生产环境相同。5五、关联表都很大的查询和记录也尽量用BCV库。6六、生产环境营业时间（特别是营业高峰时间，目前是上午8:00-10:00，下午3:00-4:00）严禁做大数据量的查询和记录，每个查询的执行时间要控制在分钟内。6七、不要执行索引和表的信息的收集。6八、编写程序的时候，注意语句规范，尽量使用变量绑定，减少共享池的使用。6九、按照标准规定编写pl/sql等程序，注意事务的提交、回滚和对各种异常情况的解决。6十、要查看表字段名或随机的少量数据时候，使用desc、也可以使用where1=2或者rowcountn来查看，而尽量不要直接执行select * from tablename，然后kill会话。6十一、尽量使用索引，避免出现全表扫描，性能影响比单机更大。6十二、对分区表建立索引时，使用local选项。6十三、不要在事务中引入Trigger，建议在事务中实现。6十四、批量更新数据的大的事务分次提交。6十五、客户端的配置。7十六、修改密码。7第二章SQL编写注意事项9一、SELECT 子句中避免使用*9二、查找总记录数时，尽量不要用count(*)，而要指定一个有索引的字段。9三、将大的历史表创建为分区表，便于数据转储和删除。9四、使用分区表进行查询时，尽量把分区键作为查询条件的第一个条件。9五、Sequence采用cache/noorder，假如在使用sequence上的列建索引，建议加大cache值。9六、在FROM子句中包含多个表的情况下，选择记录条数最少的表作为基础表，放在FROM子句的最后面。9七、WHERE子句中的连接顺序10八、在需要无条件删除表中数据时，用truncate代替delete。11九、语句中尽量使用表的索引字段，避免做大表的全表扫描。11十、 带通配符（%）的like语句11十一、用EXISTS替代IN11十二、用NOT EXISTS替代NOT IN12十三、尽也许的用UNION-ALL 替换UNION12十四、Order by语句建议13十五、避免使用NOT13十六、使用DECODE函数来减少解决时间14十七、删除反复记录14十八、假如可以使用where条件，尽量不要在having中限制数据14十九、尽量不要使数据排序14二十、使用提醒(Hints)15第三章oracle和sybase的SQL区别15一、大小写15二、限制记录数量15三、列的选择16四、连接16五、字符串函数16六、日期函数16七、数据类型转换函数:17八、空值替代函数:17九、sybase的where语句执行 正则符号，但是oracle9i不支持。17十、数字取舍17第四章 跟踪SQL执行计划18一、理论18（一）ORACLE优化器18（二）访问TABLE的方式18（三） 索引访问方式19二、SET TRACE跟踪sql执行计划19第一章数据库使用注意事项一、对BOSS1.5营帐库，营业网址严格按照规定进行配置，不可随意更换。营业网址规定按照下面方式进行分派配置，假如随意更换，会增长营业主机间的数据交互，影响数据库性能，减少营业工作效率。合肥、六安、阜阳、宿州、亳州、淮北、黄山、铜陵配置: :7001/WebRoot/login.jsp或者芜湖、蚌埠、淮南、马鞍山、安庆、滁州、宣城、巢湖、池州，配置如下:8001/WebRoot/login.jsp或者二、不使用数据库时请及时关闭数据库连接，但是也不能频繁的连接和断开数据库连接也是数据库的宝贵资源，数据库支持的数据库连接有限，当不需要使用数据库时，请“优雅”的退出数据库吧，假如能正常退出，请别“结束任务”或KILL -9。假如正在执行SQL的时候忽然异常终端，请联系数据库管理员检查解决，以防止数据库一直占用该SQL相关资源。三、执行了DML操作，请按业务规则，不要忘掉执行COMMIT或ROLLBACK。不要只执行语句，而不控制事务。当你执行一条DML语句时，数据库会为你分派锁、回滚段、REDO LOG BUFFER等资源。事务结束后，这些资源才干得以释放。四、假如是查询和记录不涉及到当天的业务时，不要在生产环境里操作，在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）严禁做大数据量的查询和记录，每个查询的执行时间要控制在分钟内。七、不要执行索引和表的信息的收集。八、编写程序的时候，注意语句规范，尽量使用变量绑定，减少共享池的使用。九、按照标准规定编写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 = 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 last_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 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或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子句将执行一个内部的排序和合并. 无论在哪种情况下,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 (方法二: 最高效) 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_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-DEC-95十四、Order by语句建议ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将减少查询速度。 仔细检查order by语句以找出非索引项或者表达式，它们会减少性能。解决这个问题的办法就是重写order by语句以使用索引，也可认为所使用的列建立此外一个索引，同时应绝对避免在order by子句中使用表达式。十五、避免使用NOT 在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and（与）、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 employee 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 LIKESMITH%; 可以用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子句中.十七、删除反复记录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 l FULL hint 告诉ORACLE使用全表扫描的方式访问指定表. 例如: SELECT /*+ FULL(EMP) */ * FROM EMP WHERE EMPNO = 7893; l 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 等等。可以根据具体情况具体使用。第三章oracle和sybase的SQL区别一、大小写SYBASE的SQL中数据库名、表名和列名分大小写，应遵循定义时的写法；ORACLE 并不区分。二、限制记录数量在SYBASE SQL中限制纪录的数量,需要用 EXEC SQL SET ROWCOUNT n， 用完需要执行EXEC SQL SET ROWCOUNT 0 恢复； 而 ORACLE 中只需要在 SQL中用 SELECT * FROM tbl_name where rowcount list 1 SELECT * 2 FROM dept, emp 3* WHERE emp.deptno = dept.deptno SQL set autotrace traceonly /*traceonly 可以不显示执行结果*/ SQL / 14 rows selected. Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF EMP 3 1 TABLE ACCESS (BY INDEX ROWID) OF DEPT 4 3 INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE) Statistics - 0 recursive calls 2 db block gets 30 consistent gets 0 physical reads 0 redo size 2598 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed 通过以上分析,可以得出实际的执行环节是: 1. TABLE ACCESS (FULL) OF EMP 2. INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE) 3. TABLE ACCESS (BY INDEX ROWID) OF DEPT 4. NESTED LOOPS (JOINING 1 AND 3) 注: 目前许多第三方的工具如TOAD和ORACLE自身提供的工具如OMS的SQL Analyze都提供了极其方便的EXPLAIN PLAN工具。