oracle sql优化总结

最新推荐文章于 2024-04-26 16:42:17 发布

鸿风

最新推荐文章于 2024-04-26 16:42:17 发布

阅读量706

点赞数

分类专栏： Oracle 文章标签： oracle sql 优化数据库 null ibatis

Oracle 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

这两天闲着没事情，项目也快上线了，就把以前在项目里写的SQL语句都看了一遍。

参照往年优化SQL的经验和网上对SQL语句优化的方法总结，加上oracle这么强大的SQL分析工具，把写的不是很优的SQL拿来优化了。

总结如下：

一、固定的SQL书写习惯，相同的查询尽量保持相同

二、存储过程的效率较高。
三、编写与其格式一致的语句，包括字母的大小写、标点符号、换行的位置等都要一致。

　　　　sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行 .
四、写查询语句的时候，尽量不要出现 ' * '，把需要的列查询出来就可以。

1.尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替、not in 用not EXISTS代替.

特别注意：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

不是说所有的in或者not in 都可以用EXISTS和not EXISTS代替.

用IN来替换OR
这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的．　
低效:
SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
高效
SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);

用UNION替换OR (适用于索引列)

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描.

注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.

在下面的例子中, LOC_ID 和REGION上都建有索引.
高效:
SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE”
低效:
SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

2.不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，可以用“<” or “>”代替。
例如：
a<>0 改为 a>0 or a<0
a<>’’ 改为 a>’’

3.Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。

可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作

用其它相同功能的操作运算代替，如：
      1、a is not null 改为 a>0 或a>’’等。
      2、不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。
      3、建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）
如果某列存在空值，即使对该列建索引也不会提高性能。任何在WHERE子句中使用is null或is not null的语句优化器是不允许使用索引的。

4、当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用 .
例如： select u.name from user u where u.name like '%huang%'
应该改为： select u.name from user u where u.name like 'huang%'

5、对于有连接的列“||”，最后一个连接列索引会无效。尽量避免连接，可以分开连接或者使用不作用在列上的函数替代。

6、如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。
例如：creadate有索引
查询：select a.paicUmNum from ps_NumBer a where to_char(a.creadate,'yyyy-MM-dd')>'2010-10-12'不使用索引
select a.paicUmNum from ps_NumBer a where a.creadate>to_date('2010-10-12','yyyy-MM-dd')使用索引

7、Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。
例如：select s.numer from student s where s.age-20 = 50;
应该改为：select s.numer from student s where s.age = 30或者(50-20);

8、对数据类型不同的列进行比较时，会使索引失效。
例如：age为number类型，score 为varcher类型
select s.* from student s where s.age>score 在age和score 上的索引将会失效

避免改变索引列的类型.:
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
假设 EMPNO是一个数值类型的索引列.
　　　　SELECT … FROM EMP WHERE EMPNO = ‘123'
实际上,经过ORACLE类型转换, 语句转化为:
　　　　SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123')
幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.

现在,假设EMP_TYPE是一个字符类型的索引列.
　　　　SELECT … FROM EMP WHERE EMP_TYPE = 123
这个语句被ORACLE转换为:
　　　　SELECT … FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来.

注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型

9、采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。UNION，在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

10.总是使用索引的第一个列：
如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引

11、在含有子查询的SQL语句中,要特别注意减少对表的查询.

例子：
SELECT EMP_NO FROM EMP WHERE (GROUP,NAME) = ( SELECT
COLUMN1,COLUMN2 FROM TEST WHERE TEST_ID = 604)

以上对sql的优化建议，都是通过本人亲身经历和点点滴滴总结出来的。如有不对的地方请指教。

　　　　　　　　　　　　　　　　　　　　顺便收藏了一些好东西，拿出来分享下
　　　　
1、最高效的删除重复记录方法 ( 因为使用了ROWID)

例子：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

2、参数用法很好

<select id="searchAppEntityDeptId" parameterClass="java.util.Map"
resultClass="appEntityDeptDTO">
<![CDATA[
     select t.entity_id appTempletId
       from oms_entity_dept t
]]>
<isNotEmpty prepend="where" property="userList">
<iterate property="userList" conjunction="OR">
　　　　(t.dept_no = #userList[].deptNo# and t.entity_no = #userList[].entityNo#)
</iterate>
</isNotEmpty>
</select>

3、ibatis调用proc

<parameterMap id="swapParameters" class="map">
<parameter property="paicUniqueDeptid" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="paicUmNum" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
</parameterMap>
　　　　<procedure id="swapEmailAddresses" parameterMap="swapParameters">
　　　　{call oms_sync_ps_pkg.proc_oms_prd_emp_io(?, ?)}
</procedure>

另附索引的一些概念（转载）

一、索引的概念
        索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

二、索引的特点
    1.索引可以加快数据库的检索速度
    2.索引降低了数据库插入、修改、删除等维护任务的速度
    3.索引创建在表上，不能创建在视图上
    4.索引既可以直接创建，也可以间接创建
    5.可以在优化隐藏中，使用索引
    6.使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引

三、索引的优点
    1.创建唯一性索引，保证数据库表中每一行数据的唯一性
    2.大大加快数据的检索速度，这也是创建索引的最主要的原因
    3.加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
    4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
    5.通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

四、索引的缺点
    1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
    2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
    3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

五、索引分类
    1.直接创建索引和间接创建索引
    直接创建索引： CREATE INDEX mycolumn_index ON mytable (myclumn)
    间接创建索引：定义主键约束或者唯一性键约束，可以间接创建索引
    2.普通索引和唯一性索引
    普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)
    唯一性索引：保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用
    CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
    3.单个索引和复合索引
    单个索引：即非复合索引
    复合索引：又叫组合索引，在索引建立语句中同时包含多个字段名，最多16个字段
    CREATE INDEX name_index ON username(firstname,lastname)
    4.聚簇索引和非聚簇索引(聚集索引，群集索引)
   聚簇索引：物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列
    CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH
    ALLOW_DUP_ROW(允许有重复记录的聚簇索引)
   非聚簇索引：CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

六、索引的使用
        应该在这些列上创建索引，例如：
1在经常需要搜索的列上，可以加快搜索的速度；
2在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不应该创建索引的的这些列具有下列特点：
第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

鸿风

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
oracle sql优化总结

这两天闲着没事情，项目也快上线了，就把以前在项目里写的SQL语句都看了一遍。参照往年优化SQL的经验和网上对SQL语句优化的方法总结，加上oracle这么强大的SQL分析工具，把写的不是很优的SQL拿来优化了。总结如下：一、固定的SQL书写习惯，相同的查询尽量保持相同二、存储过
复制链接

扫一扫