浅谈油田oracle数据库开发中的SQL优化

2.2.1 我们经常会遇到的是SQL 语句中根本没有 WHERE 条件子句。这样ORACLE系统在执行这条 SQL语句时就会整表扫描,大大降低查询效率。
2008
www.prochina.com.cn
2.2.2 SQL 语句的条件中使用NULL 、IS NOT NULL或是不 相等的条件,将使索引失效,系统也会全表扫描,降低效率,如:
2008
www.prochina.com.cn
2.2.3索引字段被包含在了表达式中,使索引失效,例如: select max(distinct ny) from dba05;
select * from daa01 where upper(qkdy) = 'PTHBB';
update dba05 set skjd= skjd*100 where jh='7P70-66’;
SELECT jb FROM daa01 WHERE bz IS NULL; SELECT jb FROM daa01 WHERE mqjb !=’31’;
以上第一句是因为NULL值并没有被定义。在SQL语句 中使用NULL会有很多的麻烦。因此建议开发人员在建表时， 把需要索引的列设成NOT NULL。如果被设置为索引的列 在某些行中存在NULL值，就不会使用这个索引。
2008
www.prochina.com.cn
2、SQL语句的优化方法
2.1共享SQL语句
根据ORACEL 中SQL 语句的执行过程可知,如果能够共享 共享池中的SQL语句将是比较优化的选择。要共享SQL语句需 满足以下两个条件。
①当前被执行的语句和共享池中的语句必须完全相同,例如:
SELECT JH FROM DAA01 只能与 SELECT JH FROM DAA01 共享, 即需要完全一致, 下面的SQL 语句就不能与之实现共享:
2008 1、ORACLE 中SQL 语句的执行过程
www.prochina.com.cn
如何知道一个SQL 语句的优劣,首先要了解一下 ORACLE 中SQL 语句的执行过程。
一个SQL 语句在ORACEL 中执行大概分为以下6 个过程:
①计算语句值； ②共享池中有无与此语句字符完全匹配的语句； ③准备要运行的SQL 语句； ④为新语句在共享池中创建空间； ⑤将语句存放在共享池中； ⑥执行准备好的SQL 语句。
浅谈油田oracle数据库开发中的SQL优化
2008
www.prochina.com.cn
摘要
大庆油田作为一个大型企业正在向信息化 产业化方向发展, Oracle 数据库的建设和应用 在油田勘探开发的效果评价、效果分析等各个 方面具有重大意义, 尤其是数据库的后台处理非 常重要, 它关系到前台客户对数据库应用的质量 和速度, 特别是以数据库、网络为基础的Web 应用都需要后台数据库的大力支持。本文着重 从实现的角度讨论了我厂大型数据库管理系统 ORACLE 中SQL 语句优化的问题。
由此可以看出,在使用动态查询时,尽量使用一个 绑定变量, 然后具体赋值, 这样可以使用共享SQL ,减 少编译次数,加快SQL 的执行速度。
2008
www.prochina.com.cn
2.2 正确的使用索引
索引可以加快查询的速度,是提高SQL 语句执行速 度的较好方法。但如果SQL 语句编写不当,索引将起不 到任何作用,反而会影响系统的性能。引起索引失效有 以下几种方式:
2008
结束语
www.prochina.com.cn
从以上讨论看出同一查询的不同SQL 语句写法会给查 询性能带来显著的差异。以上提到的是一些在软件开发和 数据库管理中最基本的提高SQL语句效率的方法,其中的关 键就是合理的建立并充分利用索引,但是在更多的情况下,往 往需要反复试验比较不同的语句以得到最佳方案。索引使 查询速度得到了很大的提升,但同时索引也占用了额外的硬 盘空间(当然现在一般硬盘空间不成问题),而且往表中插入 新记录时索引也要随着更新，这也需要一定时间。有些表 如果经常insert,而较少select,就不用加索引了。不然每次写 入数据都要重新改写索引,花费时间。
类似这样的语句,应该将WHERE 子句中的左侧只 写索引字段,而在等号右侧使用表达式,这样就可以利用 索引,达到优化的目的。
2008
www.prochina.com.cn
2.2.4使用LIKE 条件, %放在值之前,使索引失效,例如:
select * from daa01 where jh like '%7P10%';
2008
www.prochina.com.cn
2.2.6 用where子句替代having 字句
避 免 使 用 HAVING 子 句 ， HAVING 只 会 在 检 索 出所有记录之后才对结果集进行过滤。这个处 理需要排序,总计等操作。如果能通过WHERE子 句限制记录的数目，那就能减少这方面的开销， 因此速度较快。
SELECT JH from daa01 不能共享的原因:大小写不同
SELECT jh FROM DAA01 不能共享的原因:空格数不一致
2008
www.prochina.com.cn
②两条语句必须使用相同的名字的绑定变量。 SELECT jh ,kgrq from DAA02 where kzrq = :rq.year该语句不能与下面的语句共享: SELECT jh ,kgrq from people where kzrq = :rq.value 原因:绑定变量变化 SELECT jh ,kgrq from people where kzrq = ’2008’ 原因:绑定变量换成了常量
2008
2.2.7比较不匹配的数据类型
www.prochina.com.cn
比较不匹配的数据类型也是比较难于发现的性能问题之一。
注意下面查询的例子，假设xqkdm是一个VARCHAR2类型的 字段，并且在该字段上有索引。
下面的语句将执行全表扫描。
select jh,cyjh,cw,mqjb
from daa01 where xqkdm=5101; Oracle可以自动把where子句变成
2008
www.prochina.com.cn
当第一次执行某个SQL 语句时,ORACEL 先将其解析然后将该语句存放在全局区域的 共享池中,并可以被所有的数据库用户共享。 当再次执行一个SQL 语句时,如果它和之前执 行过的语句完全相同, ORACEL 将直接从共 享池中获得被解析的语句。
①计算语句值； ②共享池中有无与此语句字符完全匹配的语句； ③准备要运行的SQL 语句； ④为新语句在共享池中创建空间； ⑤将语句存放在共享池中； ⑥执行准备好的SQL 语句。
2008
www.prochina.com.cn
前言
几年来我厂针对油气田管理先后开发了地质应用软件、矿 级日报、月报系统、天然气管理系统等一些实际生产应用软件。 目前我厂大型数据信息统计、生产动态分析、措施效果追踪等 难度较大的工作大部分都是利用开发数据库中的数据信息, 这就 涉及到如何更好的管理和有效的利用数据库中的数据来提高我 们的工作效率。从大多数系统的应用实例来看,随着数据库中数 据的增加,系统的响应速度就成为系统需要解决的最主要的问题 之一。系统优化中一个很重要的方面就是SQL 语句的优化。据 统计,约有90 %的性能问题是由于程序员或用户使用了不恰当的 查询语句造成的,因此SQL 语句的质量对整个系统效率有重大关 系。对于海量数据来说,劣质SQL 语句和优质SQL 语句之间的 速度差别可以达到上百倍,甚至更多。
to_number(xqkdm)=5101， 这样就限制了索引的使用,改成以下查询就可以使用索引:
select jh,cyjh,cw,mqjb
from daa01 where xqkdm=’5101’; 特别注意：不匹配的数据类型之间比较会让Oracle自动限 制索引的使用，即便对这个查询执行Explain Plan也不能让您 明白为什么做了一次“全表扫描”。
如果将字符串值改为: '7P10%'将不会使索引失效, 达到优化的效果。
2008
www.prochina.com.cn
2.2.5 SELBiblioteka BaiduCT子句中尽量避免使用数字
例如: select 1,jh fromdaa01;
因为ORACLE系统在SQL解析过程中,将数 字转换为所有列名,这个过程需要通过查询数据字 典完成,耗费很多时间。应该在子句中直接将所有 的字段列名写出,这样就是比较优化的做法。
此外,SQL 的性能优化是一个复杂的过程,以上着重从 实现的角度讨论了SQL 语句优化,实际上要想根本解决查询 优化问题还会涉及数据库的结构设计与资源配置、网络的 流量控制以及操作系统的总体设计等等。
谢谢
www.xxzx.com