- 浏览: 256700 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
cuiqi4016:
正在做json转换的功能,帮大忙了,感谢博主分享
java 通过反射获取泛型的类型 -
cxshun:
写得很好,感谢博主的分享
java 通过反射获取泛型的类型 -
joy3229233:
[url][/url][flash=200,200][/fla ...
(转)flex checkbox 选中 -
linkagebest:
盗版可耻。。。。。
(转)flex checkbox 选中 -
shuai0420:
...
flex数据绑定
Oracle SQL的优化规则:
尽量少用IN操作符,基本上所有的IN操作符都可以用EXISTS代替
用IN写出来的SQL的优点是比较容易写及清晰易懂,但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别:
ORACLE 试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用 IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。
Oracle在执行IN子查询时,首先执行子查询,将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询,然后运行子查询直到找到第一个匹配项。
NOT EXISTS比NOT IN效率稍高。但具体在选择IN或EXIST操作时,要根据主子表数据量大小来具体考虑。
推荐方案:在业务密集的SQL当中尽量不采用IN操作符。
不用NOT IN操作符,可以用NOT EXISTS或者外连接+替代
此操作是强列推荐不使用的,因为它不能应用表的索引。
推荐方案:用NOT EXISTS 或(外连接+判断为空)方案代替
不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描,可以用“<” or “>”代替
不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。 推荐方案:用其它相同功能的操作运算代替,如:
1)a<>0 改为 a>0 or a<0
2)a<>’’ 改为 a>’’
Where子句中出现IS NULL或者IS NOT NULL时,Oracle会停止使用索引而执行全表扫描。
可以考虑在设计表时,对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作
IS NULL 或IS NOT NULL操作(判断字段是否为空)
判断字段是否为空一般是不会应用索引的,因为B树索引是不索引空值的。
推荐方案:
用其它相同功能的操作运算代替,如:
1)a is not null 改为 a>0 或a>’’等。
2)不允许字段为空,而用一个缺省值代替空值,如业扩申请中状态字段不允许为空,缺省为申请。
3) 建立位图索引(有分区的表不能建,位图索引比较难控制,如字段值太多索引会使性能下降,多人更新操作会增加数据块锁的现象)
当通配符“%”或者“_”作为查询字符串的第一个字符时,索引不会被使用
对于有连接的列“||”,最后一个连接列索引会无效。尽量避免连接,可以分开连接或者使用不作用在列上的函数替代。
如果索引不是基于函数的,那么当在Where子句中对索引列使用函数时,索引不再起作用。
Where子句中避免在索引列上使用计算,否则将导致索引失效而进行全表扫描。
对数据类型不同的列进行比较时,会使索引失效。
> 及 < 操作符(大于或小于操作符)
大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,
如一个表有100万记录,一个数值型字段A, 30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。
那么执行A>2与A>=3的效果就有很大的区别了,因为 A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。
推荐方案:用“>=”替代“>”。
UNION操作符
UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。
实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。如:
select * from gc_dfys
union
select * from ls_jg_dfys
这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
推荐方案:采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回。
select * from gc_dfys
union all
select * from ls_jg_dfys
LIKE操作符
LIKE 操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,
但是如果用得不好则会产生性能上的问题,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引。
一个实际例子:用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描,
如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高。
SQL书写的影响(共享SQL语句可以提高操作效率)
同一功能同一性能不同写法SQL的影响
如一个SQL在A程序员写的为
Select * from zl_yhjbqk
B程序员写的为
Select * from dlyx.zl_yhjbqk(带表所有者的前缀)
C程序员写的为
Select * from DLYX.ZLYHJBQK(大写表名)
D程序员写的为
Select * from DLYX.ZLYHJBQK(中间多了空格)
以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的,
但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个SQL 都会对其进行一次分析,并且占用共享内存,
如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,
这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。
推荐方案:不同区域出现的相同的Sql语句,要保证查询字符完全相同,以利用SGA共享池,防止相同的Sql语句被多次分析。
WHERE后面的条件顺序影响
Oracle从下到上处理Where子句中多个查询条件,所以表连接语句应写在其他Where条件前,可以过滤掉最大数量记录的条件必须写在Where子句的末尾。
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响,如
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'
以上两个SQL中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,
所以执行的时候都是全表扫描,第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,
在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较,而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较,
以此可以得出第二条SQL的CPU占用率明显比第一条低。
查询表顺序的影响
Oracle从右到左处理From子句中的表名,所以在From子句中包含多个表的情况下,将记录最少的表放在最后。(只在采用RBO优化时有效)
在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接,
由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。(注:如果对表进行了统计分析, ORACLE会自动先进小表的链接,再进行大表的链接)。
Order By语句中的非索引列会降低性能,可以通过添加索引的方式处理。严格控制在Order By语句中使用表达式
当在Sql语句中连接多个表时,使用表的别名,并将之作为每列的前缀。这样可以减少解析时间
多利用内部函数提高Sql效率
SQL语句索引的利用 对操作符的优化(见前面)
对条件字段的一些优化
采用函数处理的字段不能利用索引
如:
substr(hbs_bh,1,4)=’5400’,优化处理:hbs_bh like ‘5400%’
trunc(sk_rq)=trunc(sysdate), 优化处理:sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)
进行了显式或隐式的运算的字段不能进行索引
如:
ss_df+20>50,优化处理:ss_df>30
‘X’||hbs_bh>’X5400021452’,优化处理:hbs_bh>’5400021542’
sk_rq+5=sysdate,优化处理:sk_rq=sysdate-5
hbs_bh=5401002554,优化处理:hbs_bh=’ 5401002554’,注:此条件对hbs_bh 进行隐式的to_number转换,因为hbs_bh字段是字符型。
条件内包括了多个本表的字段运算时不能进行索引
ys_df>cx_df,无法进行优化
qc_bh||kh_bh=’5400250000’,优化处理:qc_bh=’5400’ and kh_bh=’250000’
可能引起全表扫描的操作
在索引列上使用NOT或者“<>”
对索引列使用函数或者计算
NOT IN操作
通配符位于查询字符串的第一个字符
IS NULL或者IS NOT NULL
多列索引,但它的第一个列并没有被Where子句引用
ORACLE在SQL执行分析方面已经比较成熟,如果分析执行的路径不对首先应在数据库结构(主要是索引)、
服务器当前性能(共享内存、磁盘文件碎片)、数据库对象(表、索引)统计信息是否正确这几方面分析。
尽量少用IN操作符,基本上所有的IN操作符都可以用EXISTS代替
用IN写出来的SQL的优点是比较容易写及清晰易懂,但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别:
ORACLE 试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用 IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。
Oracle在执行IN子查询时,首先执行子查询,将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询,然后运行子查询直到找到第一个匹配项。
NOT EXISTS比NOT IN效率稍高。但具体在选择IN或EXIST操作时,要根据主子表数据量大小来具体考虑。
推荐方案:在业务密集的SQL当中尽量不采用IN操作符。
不用NOT IN操作符,可以用NOT EXISTS或者外连接+替代
此操作是强列推荐不使用的,因为它不能应用表的索引。
推荐方案:用NOT EXISTS 或(外连接+判断为空)方案代替
不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描,可以用“<” or “>”代替
不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。 推荐方案:用其它相同功能的操作运算代替,如:
1)a<>0 改为 a>0 or a<0
2)a<>’’ 改为 a>’’
Where子句中出现IS NULL或者IS NOT NULL时,Oracle会停止使用索引而执行全表扫描。
可以考虑在设计表时,对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作
IS NULL 或IS NOT NULL操作(判断字段是否为空)
判断字段是否为空一般是不会应用索引的,因为B树索引是不索引空值的。
推荐方案:
用其它相同功能的操作运算代替,如:
1)a is not null 改为 a>0 或a>’’等。
2)不允许字段为空,而用一个缺省值代替空值,如业扩申请中状态字段不允许为空,缺省为申请。
3) 建立位图索引(有分区的表不能建,位图索引比较难控制,如字段值太多索引会使性能下降,多人更新操作会增加数据块锁的现象)
当通配符“%”或者“_”作为查询字符串的第一个字符时,索引不会被使用
对于有连接的列“||”,最后一个连接列索引会无效。尽量避免连接,可以分开连接或者使用不作用在列上的函数替代。
如果索引不是基于函数的,那么当在Where子句中对索引列使用函数时,索引不再起作用。
Where子句中避免在索引列上使用计算,否则将导致索引失效而进行全表扫描。
对数据类型不同的列进行比较时,会使索引失效。
> 及 < 操作符(大于或小于操作符)
大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,
如一个表有100万记录,一个数值型字段A, 30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。
那么执行A>2与A>=3的效果就有很大的区别了,因为 A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。
推荐方案:用“>=”替代“>”。
UNION操作符
UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。
实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。如:
select * from gc_dfys
union
select * from ls_jg_dfys
这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
推荐方案:采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回。
select * from gc_dfys
union all
select * from ls_jg_dfys
LIKE操作符
LIKE 操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,
但是如果用得不好则会产生性能上的问题,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引。
一个实际例子:用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描,
如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高。
SQL书写的影响(共享SQL语句可以提高操作效率)
同一功能同一性能不同写法SQL的影响
如一个SQL在A程序员写的为
Select * from zl_yhjbqk
B程序员写的为
Select * from dlyx.zl_yhjbqk(带表所有者的前缀)
C程序员写的为
Select * from DLYX.ZLYHJBQK(大写表名)
D程序员写的为
Select * from DLYX.ZLYHJBQK(中间多了空格)
以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的,
但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个SQL 都会对其进行一次分析,并且占用共享内存,
如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,
这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。
推荐方案:不同区域出现的相同的Sql语句,要保证查询字符完全相同,以利用SGA共享池,防止相同的Sql语句被多次分析。
WHERE后面的条件顺序影响
Oracle从下到上处理Where子句中多个查询条件,所以表连接语句应写在其他Where条件前,可以过滤掉最大数量记录的条件必须写在Where子句的末尾。
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响,如
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'
以上两个SQL中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,
所以执行的时候都是全表扫描,第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,
在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较,而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较,
以此可以得出第二条SQL的CPU占用率明显比第一条低。
查询表顺序的影响
Oracle从右到左处理From子句中的表名,所以在From子句中包含多个表的情况下,将记录最少的表放在最后。(只在采用RBO优化时有效)
在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接,
由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。(注:如果对表进行了统计分析, ORACLE会自动先进小表的链接,再进行大表的链接)。
Order By语句中的非索引列会降低性能,可以通过添加索引的方式处理。严格控制在Order By语句中使用表达式
当在Sql语句中连接多个表时,使用表的别名,并将之作为每列的前缀。这样可以减少解析时间
多利用内部函数提高Sql效率
SQL语句索引的利用 对操作符的优化(见前面)
对条件字段的一些优化
采用函数处理的字段不能利用索引
如:
substr(hbs_bh,1,4)=’5400’,优化处理:hbs_bh like ‘5400%’
trunc(sk_rq)=trunc(sysdate), 优化处理:sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)
进行了显式或隐式的运算的字段不能进行索引
如:
ss_df+20>50,优化处理:ss_df>30
‘X’||hbs_bh>’X5400021452’,优化处理:hbs_bh>’5400021542’
sk_rq+5=sysdate,优化处理:sk_rq=sysdate-5
hbs_bh=5401002554,优化处理:hbs_bh=’ 5401002554’,注:此条件对hbs_bh 进行隐式的to_number转换,因为hbs_bh字段是字符型。
条件内包括了多个本表的字段运算时不能进行索引
ys_df>cx_df,无法进行优化
qc_bh||kh_bh=’5400250000’,优化处理:qc_bh=’5400’ and kh_bh=’250000’
可能引起全表扫描的操作
在索引列上使用NOT或者“<>”
对索引列使用函数或者计算
NOT IN操作
通配符位于查询字符串的第一个字符
IS NULL或者IS NOT NULL
多列索引,但它的第一个列并没有被Where子句引用
ORACLE在SQL执行分析方面已经比较成熟,如果分析执行的路径不对首先应在数据库结构(主要是索引)、
服务器当前性能(共享内存、磁盘文件碎片)、数据库对象(表、索引)统计信息是否正确这几方面分析。
发表评论
-
oracle 设置SGA
2013-03-14 11:12 962SQL> show parameter sga; ... -
oracle sequence
2012-03-23 13:35 971create or replace procedure p_c ... -
oracle 触发器
2012-03-23 13:11 1019触发器是指存放在数据库中,并被隐藏执行的存储过程。在Oracl ... -
两个经典的Oracle触发器示例 .
2012-03-23 11:48 1113【案例一】 题目: --触发器: --添加员工信息,流水号作 ... -
查看一个函数被哪些存储过程调用
2011-02-28 14:37 1479查看一个函数被哪些存储过程调用: select * from ... -
Oracle 常用SQL技巧
2010-11-24 17:44 8261. SELECT子句中避免使用 ... -
oracle 存储过程的基本语法
2010-11-05 15:59 10431.基本结构 CREATE OR REPLACE PROCE ... -
oracle存储过程,游标及循环应用实例
2010-07-21 14:45 1521最近在项目中做了一个关于系统与系统之间数据同步的业务,之前使用 ... -
oracle中用START WITH...CONNECT BY PRIOR子句实现递归查询
2010-05-26 11:24 862今天发现在oracle中的select语句可以用START W ... -
ArcSDE系统表的说明
2010-02-01 17:09 1362当基于Oracle或是SQL Server安装ArcSde后, ... -
(转)Oracle常用数据字典的查询使用方法
2010-01-14 16:40 962查看当前用户的缺省表空间 SQL>select use ... -
(转)PL/SQL存储过程编程
2010-01-14 16:34 1706PL/SQL存储过程编程(上) ... -
(转)hibernate中是否支持postgis函数的问题
2010-01-13 16:08 1746今天上午一位朋友问到了,关于hibernate中是否支持pos ... -
oracle 分页语句
2010-01-06 17:28 764select * from (select a.*, ro ... -
数据库设计5步骤
2009-12-23 16:43 781数据库设计5步骤 ... -
数据库设计
2009-12-23 10:07 867说到数据库,我认为不 ... -
oracle 优化
2009-11-25 15:36 931Oracle的优化器有两种优 ... -
oracle 字段类型
2009-11-13 16:49 1034Char 用于描述定长的字符型数据,长度<=2000字节 ... -
oracle PROCEDURE
2009-11-13 16:13 2991.基本结构 CREATE OR REPLACE PROCED ... -
通过一个实际的例子学习Oracle存储过程
2009-11-10 18:10 1468--创建存储过程 CREATE OR REPLACE PROC ...
相关推荐
oracle的SQL语句优化及索引使用技巧
Oracle SQL优化文档,一份oracle数据库方面的优化文档呀,可以学习一下
主要讲述oracle sql 的开发以及优化,对低效率的sql的优化方法和诊断技巧
对Oracle sql 性能优化、调整做了详细的说明
对oracle sql语句做优化处理,提高sql效率
oracle sql案例的分享ppt,具体调优和改写sql的例子
oracle sql常用的优化共100条,很实用
oracle SQL 优化的53个规则详解,全面讲解oracle SQL优化知识,掌握SQL优化,提高SQL语句执行效率。
不错的东东,通俗易懂,建议学习.赶紧下载吧
Oracle SQL 优化
oraclesql优化_Oracle中优化SQL的原则.pdf
10.基于基本的优化CBO 11.如何统计数据库数据 12.Oracle如何统计操作系统数据 13./*+parallel(t,4)*/在大表查询性能的提现 14.CRAS和create insert的性能测试 15.增加字段时指定default值和先增加再update哪个性能好...
OracleSQL的优化.pdf
oracle SQL优化实例.docoracle SQL优化实例.doc
oracle sql优化语句
基于Oracle的SQL优化
Oracle SQL优化规则 Oracle SQL优化规则 Oracle SQL优化规则 Oracle SQL优化规则 Oracle SQL优化规则
【超易懂实用,初学者必看】oracleSQL优化培训,由企业dba切身实际经验总结,超简单易懂超实用,初入门者不看必后悔!!