ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle数据库规范:SQL开发规范

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

Linux操作系统 作者:yanyumao1984 时间:2015-10-20 11:24:49 0 删除 编辑
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/30075367/viewspace-1815106/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-12-22

  • 博文量
    97
  • 访问量
    101088