`
lorry1113
  • 浏览: 264326 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

oracle sql 优化

阅读更多
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 SQL优化技巧

    Oracle SQL 优化技巧 Oracle SQL 优化是提高数据库性能的关键技巧之一。以下是 Oracle SQL 优化的 12 个技巧: 1. 选择最有效率的表名顺序 在基于规则的优化器中,Oracle 的解析器按照从右到左的顺序处理 FROM ...

    oracle sql优化

    在Oracle数据库环境中,SQL优化是提升系统性能的关键环节。它涉及到如何有效地检索数据,减少查询时间,减轻数据库服务器的负载,以及改善整体系统的响应速度。本文将深入探讨Oracle SQL优化的相关知识点,帮助你...

    Oracle SQL优化文档

    Oracle SQL优化文档提供了对Oracle数据库进行SQL语句优化的一系列方法和工具,旨在帮助数据库管理员和开发人员提高SQL查询性能和数据库效率。文档内容涵盖了从基本的SQL优化思路到具体的分析工具,详细介绍了如何...

    OracleSQL的优化.pdf

    Oracle SQL 优化 Oracle SQL 优化是数据库性能优化的关键部分。为了提高数据库的性能,我们需要从五个方面进行调整:去掉不必要的大型表的全表扫描、缓存小型表的全表扫描、检验优化索引的使用、检验优化的连接技术...

    oracle sql优化100条

    ### Oracle SQL优化100条概览 在Oracle数据库管理与优化领域,SQL查询的效率直接关系到系统性能和用户体验。以下将详细解读部分Oracle SQL优化原则,涵盖查询执行计划、表扫描方式、SQL语句优化及数据访问顺序等...

    Oracle SQL 优化与调优技术详解-附录:SQL提示

    在Oracle数据库中,SQL优化是一个至关重要的环节,它能够显著提高数据库查询的效率和性能。本文将详细介绍Oracle SQL中的“提示”(HINT)技术,这是Oracle SQL优化中使用的一项辅助手段,通过为查询提供额外的优化...

    oracle sql优化实战案例

    主要讲述oracle sql 的开发以及优化,对低效率的sql的优化方法和诊断技巧

    ORACLE SQL优化工具sqlhc

    Oracle SQL优化是数据库管理中的关键任务,用于提升查询性能,减少资源消耗,进而改善整体系统效率。`SQLHC`(SQL Health Check)是Oracle提供的一种实用工具,它可以帮助DBA(数据库管理员)诊断和优化SQL语句。在...

    落落 Oracle SQL优化与改写培训教程

    大量优化实战方法:将主要SQL优化点一一剖析,分享大量SQL优化的实际工作经验 50余改写调优案例:覆盖大多数DBA日常工作场景,具有相当大的实用价值 技巧+案例:可以作为DBA的参考手册,也可以作为开发人员编写SQL...

    Oracle sql优化.pdf

    Oracle SQL 优化 Oracle SQL 优化是指通过调整和优化 SQL 语句、数据库服务器设置和应用程序配置来提高数据库性能的过程。该过程涉及到多个方面,包括 SQL 语句优化、索引优化、数据库服务器配置优化、应用程序...

    ORACLE SQL 优化 存储过程 PROCEDURE

    ORACLE SQL 优化存储过程 PROCEDURE ORACLE SQL 优化存储过程 PROCEDURE 是一篇结合实际开发经验和理论知识的文章,旨在帮助开发者提高 SQL 开发效率和优化存储过程。文章涵盖了广泛的知识点,从基本的 SQL 语句到...

    Oracle SQL优化法则

    Oracle SQL优化是数据库管理的关键环节,它涉及到提升SQL语句执行效率,降低资源消耗,从而提高整体系统性能。以下是对Oracle SQL优化53个规则的详细解读,主要围绕选择优化器、访问表的方式、共享SQL语句这三大方面...

    oracle sql优化语句

    Oracle SQL 优化是数据库管理...总的来说,Oracle SQL优化涉及到对数据库行为的深入理解,包括优化器的工作原理、索引的使用策略以及执行计划的选择。通过恰当的配置和调优,可以大大提高数据库系统的性能和响应速度。

    oracle sql优化分享案例

    oracle sql案例的分享ppt,具体调优和改写sql的例子

    Oracle sql 优化实例

    不错的东东,通俗易懂,建议学习.赶紧下载吧

    oraclesql优化_Oracle中优化SQL的原则.pdf

    oraclesql优化_Oracle中优化SQL的原则.pdf

    大牛出手Oracle SQL优化实例讲解

    10.基于基本的优化CBO 11.如何统计数据库数据 12.Oracle如何统计操作系统数据 13./*+parallel(t,4)*/在大表查询性能的提现 14.CRAS和create insert的性能测试 15.增加字段时指定default值和先增加再update哪个性能好...

    OracleSQL优化初步

    在Oracle数据库管理中,SQL优化是一项至关重要的任务,它直接影响到数据库系统的性能和效率。"Oracle SQL优化初步"这个主题涵盖了DBA们在日常工作中必须掌握的基础知识和技巧。SQL优化的目标是通过改进查询结构、...

    高清完整版 Oracle 高性能SQL引擎剖析SQL优化与调优机制详解

    Oracle的SQL优化与调优机制复杂多样,涵盖了从SQL语句的编写、执行计划的选择、到资源管理的全方位调优方法。为了深入理解Oracle SQL优化与调优机制,需要掌握以下几个核心知识点。 首先,要掌握Oracle SQL的基本...

    Oracle SQL优化的53个黄金法则

    Oracle SQL优化是数据库管理中的关键任务,以确保高效的数据检索和整体系统性能。本文将深入探讨53个黄金法则中的几个关键原则,帮助你优化Oracle SQL查询。 1. **选择合适的优化器**: - ORACLE提供三种优化器:...

Global site tag (gtag.js) - Google Analytics