Oracle数据库规范:SQL开发规范

SQL开发规范
• Select语句中不可以用*，必须select字段列表，以节省内存，提
高效率
• 避免频繁commit，尤其是把commit 写在循环体中每次循环都进行
commit。避免在一个事务中出现2此commit的现象。如果执行一
半执行一次commit，执行完另外一半又执行一次commit
• like 子句尽量前端匹配，如写成'like string%'，不要写成
'%string%'。
• 批量insert 大数据量时可以采用append和nologging方式，提高处
理速度
• exp时可以采用direct=y,index=no的方式提高处理效率，imp时可
以指定较大的buffer。如果是oracle 10g以上则可以使用expdp和
impdp来提高处理的速度
• exp和imp时，如果系统中存在主外键约束，在imp时可以设置
constraints=n,避免在imp时因为主外键而报错。
28
© 2010 Oracle Corporation
SQL开发规范
• 在使用for update子句时一定注意限制条件，避免锁定全表或者不
需要被锁定的行记录。readonly 的情况不能用for update。
• PLSQL开发中一定注意解析的问题。比如
execute immediate „select ….. from table where xx=‟ using „…‟而
不要直接写成execute immediate „…‟
• 尽量少用not exist/not in等否则写法。如果一定要用时，尽量选择
not exist，not in可能用不到index,not exist效率更高，速度更快
。
• exist和in的选择一般遵循以下原则
• 当查询的数据较少或者字段值比较时用in比较好，比如select * from t where
t.id in (….);
• 当查询的值比较多或者是子查询时用exist比较好，比如select * from t where
exist(select ….)
• exist中的子查询用常量是速度比select *快,比如select * from t where exist
(select 1 from …)
29
© 2010 Oracle Corporation
SQL开发规范
• 尽量不用select嵌套写法，如select …from tb1,(select…tb2)…尽
可能把select的嵌套转化为连接方式，如select …. From tb1,tb2
…
• 尽量少用is null/ is not null等null 的处理。
• 对于大表查询中的列项应尽量避免进行诸如to_char()、to_date()
、to_numbre()等转换。
• 尽量避免进行全表扫描，限制条件尽可能多，以便更快搜索到要
查询的数据
• 在做全表扫描时，可以先修改session的
db_file_multiple_read_count值再进行全表扫描以提高执行效率。
alter session set db_file_multiple_read_count=32 ….
30
© 2010 Oracle Corporation
SQL开发规范
• 创建database 对象的时候，对象名不能用“”引上，否则可能会带来找不
到对象的问题。
• hash join时注意表连接顺序。且需要注意选择合适的hash key，选择
distinct value很多的字段来作为hash key，如果重复值太多，在进行hash
探测的时候效率将会很低。
• hash join较多的系统，需要设置较大的pga_aggregate_target参数，以便
进行最优的hash join
• 收集统计信息时，尽量在维护时间段进行，否则会造成大面积SQL重新
解析，可能导致较为严重的问题。
• 在parallel查询的使用过程中，尽量指定hint来使用而不是在表上加degree
参数，这样会导致对该表的所有查询都将偏向于走全表扫描，同时可能
会带来内存的大量消耗。对于并行度的问题，不繁忙的系统可以设置并
行度为CPU的个数，对于繁忙的OLTP系统，最好不要使用并行查询。
• 在SQL开发的过程中，SQL的字母大小写采用一致规范，避免因为大小
写的问题造成SQL的多次解析。
31
© 2010 Oracle Corporation
SQL开发规范
• in 替换or ，比如select * from t where id=1 or id=2 or id=3,select * from t
where id in (1,2,3)
• union 与or ，union有时比or更有效，or有时比union更有效，综合union
替换or或者or 替换union后是否能用到索引，以及条件的选择性来综合判
断和评估。
• >=替换>，比如select count(1) from t where id>=3 而不用select count(1)
from t where id>2
• order by在对索引列进行排序时需要注意
• order by 后面的字段顺序，应该和索引列(复合索引)的顺序保持一致。
• order by后面的字段如果为null，则不能用到索引
select id from t order by id. 索引在id列上，如果id列为null，则该语句
要用全表扫描再排序，如果为not null，则直接用索引扫描即可。
如果id列为null，则可以通过用where和order by置换的方式，比如
select id from t order by id 全表扫描再排序
select id from t where id>0 直接扫描索引
32
© 2010 Oracle Corporation
SQL开发规范
• 避免数据类型转换，如果id列为number型,select * from t where
id=‟1‟则会发生数据转换id=to_number(„1‟),如果类似转换发生在索
引列上将导致索引不能被用到。
• group by ，order by 尽量放到SQL的最末尾，这样可以把不需要
返回的行过滤掉，减少需要进行group by order by 操作的集。比
如select name from t group by name having name=‟xxxx‟ 效率肯
定不如select name from t where name=‟xxxx‟ group byname;
• !=操作符
除非必须必要，否则坚决避免使用!=操作符，因为使用!=会使
ORACLE无法使用索引，Oracle内核会假定查询会返回表中的多
数记录，因此就会走FULL TABLE SCAN。
33
© 2010 Oracle Corporation
如何评估SQL语句的性能
• 分析与SQL执行相关的配置
• 参数：optimizer_index_cost_adj
• 参数：optimizer_mode
• 参数：sql_trace
• 参数：db_file_multiblock_read_count
• 分析SQL执行计划
• 查看SQL执行计划有三种方式：
• 设置set autotrace:
• 通过explain plan for
• 通过tkprof工具
34
© 2010 Oracle Corporation
通过SQL TRACE分析SQL的执行
SQL_TRACE是Oracle提供的用于进行SQL跟踪的手段，是强有力
的辅助诊断工具.在日常的数据库问题诊断和解决中，SQL_TRACE
是非常常用的方法。
• SQL_TRACE使用
SQL_TRACE可以作为初始化参数在全局启用，也可以通过命令行方式
在具体session启用。
• 10046使用
10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强.
10046事件可以设置以下四个级别:
• 1 - 启用标准的SQL_TRACE功能,等价于sql_trace
• 4 - Level 1 加上绑定值(bind values)
• 8 - Level 1 + 等待事件跟踪
• 12 - Level 1 + Level 4 + Level 8
• 分析
分析sql在执行过程中发生的解析次数，逻辑读次数，变量绑定，所花时
间。改变SQL写法，变换查询条件等因素，再进行trace分析解析次数，
逻辑读次数，变量绑定等指标，最后判断最优化的SQL。
35
© 2010 Oracle Corporation
PL/SQL控制结构编码
PL/SQL提供了三种控制程序流程的语句：
• 条件控制语句IF：使用ELSEIF 对相互排斥的条件进行
判断。
• 循环语句FOR 、WHILE 、LOOP：不使用EXIT和
RETURN语句退出循环，在一个LOOP循环中只能有一
个出口；将静态的和固定不变的表达（代码）移到循
环体外。
• 跳转语句GOTO：不允许使用goto语句。
36
© 2010 Oracle Corporation
异常处理
• 过程、函数必须在过程体中作异常捕获，并将异常信息通过
os_Msg变量输出。
• –1 ~ -19999的异常为Oracle定义的异常代码。
• 过程、函数必须包含两个输出参数，on_Flag（number）及
os_Msg（varchar2），分别用于标识过程的执行状态及过程提示
信息（包括异常情况下的异常信息）。其中on_Flag有三种取值情
况，0表示过程执行成功但无提示信息；大于0表示过程执行成功
但有提示信息；小于1表示过程执行失败且有提示信息。
• 找不到数据“NO_DATA_FOUND”时的异常代码为100，异常处理
过程中必须进行特殊判断。
• "WHEN OTHERS"必须放置在异常处理代码的最后面作为缺省处
理器处理没有显式处理的异常。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27573546/viewspace-761482/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27573546/viewspace-761482/