文档章节

oracle中sql语句的优化

A
 Aman_li
发布于 2016/08/25 23:44
字数 2494
阅读 79
收藏 7

一、执行顺序及优化细则

1.表名顺序优化 
(1) 基础表放下面,当两表进行关联时数据量少的表的表名放右边
表或视图: 
Student_info   (30000条数据)
Description_info (30条数据)  
select *
  from description_info di
      ,student_info     si --学生信息表
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'      
与    
select *
  from student_info     si--学生信息表
      ,description_info di
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'   
以student_info作为基础表,你会发现运行的速度会有很大的差距。
   
   
(2) 当出现多个表时,关联表被称之为交叉表,交叉表作为基础表
select *
  from description_info di
    ,description_info di2
      ,student_info     si --学生信息表
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'
   and si.school_id = di.lookup_code(+)
   and di.lookup_type(+) = 'SCHOOL_ID'

select *
  from student_info     si--学生信息表
      ,description_info di
      ,description_info di2
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'
   and si.school_id = di.lookup_code(+)
   and di.lookup_type(+) = 'SCHOOL_ID'
以student_info作为基础表,你会发现运行的速度会有很大的差距,
当基础表放在后面,这样的执行速度会明显快很多。

2.where执行顺序
where执行会从至下往上执行
select *
from student_info si --学生信息表
where si.school_id=10 --学院ID
and  si.system_id=100--系ID
摆放where子句时,把能过滤大量数据的条件放在最下边

3. is null 和is not null
当要过滤列为空数据或不为空的数据时使用
select *
from student_info si --学生信息表
where si.school_id is null(当前列中的null为少数时用is not null,否则is null)

4.使用表别名
当查询时出现多个表时,查询时加上别名,
避免出现减少解析的时间字段歧义引起的语法错误。

5. where执行速度比having快
尽可能的使用where代替having

select  from student_info si
group by si.student_id
having si.system_id!=100
  and si.school_id!=10
(select  from student_info si
wehre si.system_id!=100
and si.school_id!=10
group by si.student_id)  
  
6.  * 号引起的执行效率
尽量减少使用select * 来进行查询,当你查询使用*,
数据库会进行解析并将*转换为全部列。


二、替代优化
1、用>=替代> 
select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id>=10
  与
  select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id>9
  执行时>=会比>执行得要快 
  
2、用UNION替换OR (适用于索引列) 
select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id=10
  union 
select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id=2  
   上面语句可有效避免全表查询
   select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id=10 
  or ui.student_id=2
  如果坚持要用OR, 可以把返回记录最少的索引列写在最前面
    
3、用in 代替or
select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id=10
  or ui.student_id=20
  or ui.student_id=30
  改成
  select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id in (10,20,30)
  执行会更有效率
    
4、 Union All 与Union
Union All重复输出两个结果集合中相同记录
如果两个并集中数据都不一样.那么使用Union All 与Union是没有区别的
,
select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id=10
  union All
select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id=2  
  与
  select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id=10
  union 
select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id=2  
但Union All会比Union要执行得快

5、分离表和索引 
总是将你的表和索引建立在另外的表空间内 
决不要将这些对象存放到SYSTEM表空间里

三、一些优化技巧


1、计算表的记录数时

select count(si.student_id) 
from Student_info si(student_id为索引)


select count(*) from Student_info si
执行时.上面的语句明显会比下面没有用索引统计的语句要快

2.使用函数提高SQL执行速度

当出现复杂的查询sql语名,可以考虑使用函数来提高速度
查询学生信息并查询学生(李明)个人信息与的数学成绩排名
如 
select di.description student_name
      ,(select res.order_num--排名
         from result res
        where res.student_id = di.student_id
        order by result_math) order_num
  from description_info di
      ,student_info     si --学生信息表
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'
   and di.description = '李明'
   
而且我们将上面order_num排名写成一个fuction时
create or replace package body order_num_pkg is
function order_num(p_student_id number) return_number is
  v_return_number number;
begin
  select res.order_num --排名
    into v_return_number
    from result res
   where res.student_id = di.student_id
   order by result_math;
  return v_return_number;
exception
  when others then
    null;
    return null;
end;
end order_num_pkg;
执行
select di.description student_name
      ,order_num_pkg.order_num(di.student_id) order_num
  from description_info di
      ,student_info     si --学生信息表
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'
   and di.description = '李明'
执行查询时的速度也会有所提高     
 
3.减少访问数据库的次数

执行次数的减少(当要查询出student_id=100的学生和student_id=20的学生信息时)
select address_id
from student_info si --学生信息表
where si.student_id=100

select address_id
from student_info si --学生信息表
where si.student_id=20
都进行查询.这样的效率是很低的
而进行
(
select si.address_id,si2.address_id
from student_info si --学生信息表
,student_info si2 
where si.student_id=100
and si2.student_id=20 

select decode(si.student_id,100,address_id)
   ,decode(si.student_id,20,address_id)
from student_info si
)
执行速度是提高了,但可读性反而差了..
所以这种写法个人并不太推荐

4、用Exists(Not Exists)代替In(Not In)

   在执行当中使用Exists或者Not Exists可以高效的进行查询

5、Exists取代Distinct取唯一值的

   取出关联表部门对员工时,这时取出员工部门时,出现多条..
select distinct di.dept_name 
  from departments_info di --部门表
      ,user_info        ui --员工信息表
where ui.dept_no = di.dept_no
   可以修改成
  select di.dept_name
    from departments_info di --部门表
   where  exists (select 'X'
            from user_info ui --员工信息表
           where di.dept_no = ui.dept_no)
6、用表连接代替Exists
   通过表的关联来代替exists会使执行更有效率
select ui.user_name
  from user_info ui--员工信息表
where exists (select 'x '
          from departments_info di--部门表
         where di.dept_no = ui.dept_no
           and ui.dept_cat = 'IT');
执行是比较快,但还可以使用表的连接取得更快的查询效率
   select ui.user_name
    from departments_info di
        ,user_info        ui --员工信息表
   where ui.dept_no = di.dept_no
     and ui.department_type_code = 'IT'

代码是经测试并进行优化所写,
以上只例子,具体使用还是要针对各个不同的具体的业务使用用Exists(Not Exists)代替In(Not In)

四、索引篇

1、运算导致的索引失效

select di.description student_name
      ,(select res.order_num--排名
         from result res
        where res.student_id = di.student_id
        order by result_math) order_num
  from description_info di
      ,student_info     si --学生信息表
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'
   and si.student_id+0=100/*student_id索引将失效*/ 
   
2、类型转换导致的索引失效

select di.description student_name
      ,(select res.order_num--排名
         from result res
        where res.student_id = di.student_id
        order by result_math) order_num
  from description_info di
      ,student_info     si --学生信息表
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'
   and di.student_id='100'
  
student_id为number类型的索引,当执行下列语句,
oracle会自动转换成
select di.description student_name
      ,(select res.order_num--排名
         from result res
        where res.student_id = di.student_id
        order by result_math) order_num
  from description_info di
      ,student_info     si --学生信息表
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'
   and di.student_id=to_number('100')
所幸,只是解析并转换类型,并没有导到失效,
但要是写成下面,将会使用其失效
select di.description student_name
      ,(select res.order_num--排名
         from result res
        where res.student_id = di.student_id
        order by result_math) order_num
  from description_info di
      ,student_info     si --学生信息表
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'
   and to_char(di.student_id)='100'
   
3、在索引列上进行计算引起的问题

select di.description student_name
      ,(select res.order_num--排名
         from result res
        where res.student_id = di.student_id
        order by result_math) order_num
  from description_info di
      ,student_info     si --学生信息表
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'
   and di.student_id-2=10
在索引列中进行运算,将会不使用索引而使用全表扫描
而将
select di.description student_name
      ,(select res.order_num--排名
         from result res
        where res.student_id = di.student_id
        order by result_math) order_num
  from description_info di
      ,student_info     si --学生信息表
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'
   and di.student_id=10+2
将会得到高效的运行速度

4、 Is not null引起的问题(student_id为索引)

不要把存在空值的列做为索引,否则无法使用索引
select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id is not null--索引失效 
  
select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id>=-1--索引有效 

5、Order by导致索引失效(student_id为索引)

select ui.user_name
  from user_info ui--员工信息表
  group by ui.student_id   
而使用
select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id>=-1
  将使其有效,
  在order by中只存在两种条件下可以使用索引
  (ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序
ORDER BY中所有的列必须定义为非空. )
  
6、自动选择索引
 
如果表中有两个以上(包括两个)索引,其中有一个唯一性索引,而其他是非唯一性.
在这种情况下,ORACLE将使用唯一性索引而完全忽略非唯一性索引.

7、 !=导致索引失效
 
select ui.user_name
  from user_info ui--员工信息表
  where ui.student_id!=0
在Where中使用!=将会把索引失效

8、%导致的索引失效

select di.description student_name
      ,(select res.order_num--排名
         from result res
        where res.student_id = di.student_id
        order by result_math) order_num
  from description_info di
      ,student_info     si --学生信息表
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'
   and di.look_code Like '%12'/*look_code为索引,索引将失效*/ 

select di.description student_name
      ,(select res.order_num--排名
         from result res
        where res.student_id = di.student_id
        order by result_math) order_num
  from description_info di
      ,student_info     si --学生信息表
where si.student_id = di.lookup_code(+)
   and di.lookup_type(+) = 'STUDENT_ID'
   and di.look_code Like '12%'/*索引有效*/ 
以上只例子,具体还是要针对各个不同的具体的业务使用

五、oracle 中的not Exists与Not in的性能巨大差异

Not Exists与Not in的作用同样是排除数据,在oracle 中使用not in并不象mysql中的执行那么快,如(
select jt1.doc_num --单据号码
      ,oalc.description school_name --学校名称
      ,oalc2.description system_name --系名称
      ,oalc.description class_name --班级名称
  from java_table1            jt1
      ,java_table_description oalc
      ,java_table_description oalc2
      ,java_table_description oalc3
where oalc.lookup_type(+) = 'JAVA_SCHOOL_NAME'
   and jt1.school_id = oalc.lookup_code(+)
   and oalc2.lookup_type(+) = 'JAVA_SYSTEM_NAME'
   and jt1.system_id = oalc2.lookup_code(+)
   and oalc3.lookup_type(+) = 'JAVA_CLASS_NAME'
   and jt1.class_id = oalc3.lookup_code(+)
   and not exists
(select jt2.header_id
          from java_table2 jt2 jt1.header_id = jt2.header_id))

select jt1.doc_num --单据号码
      ,oalc.description school_name --学校名称
      ,oalc2.description system_name --系名称
      ,oalc.description class_name --班级名称
  from java_table1            jt1
      ,java_table_description oalc
      ,java_table_description oalc2
      ,java_table_description oalc3
where oalc.lookup_type(+) = 'JAVA_SCHOOL_NAME'
   and jt1.school_id = oalc.lookup_code(+)
   and oalc2.lookup_type(+) = 'JAVA_SYSTEM_NAME'
   and jt1.system_id = oalc2.lookup_code(+)
   and oalc3.lookup_type(+) = 'JAVA_CLASS_NAME'
   and jt1.class_id = oalc3.lookup_code(+)
   and jt1.header_id not in (select jt2.header_id from java_table2 jt2)

当jt2表中的数据比较大时,就会出现巨大的差异,以上只能是我的个人理解与测试结果(java_table1 视图测试

数据量为36749,java_table2 为300条)

© 著作权归作者所有

A
粉丝 1
博文 6
码字总数 10165
作品 0
深圳
私信 提问
ORACLE将执行过的SQL语句存放在内存的共享池

Oracle SQL性能优化深入浅出 ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中,可以被所有的数据库用户共享。当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执...

技术小甜
2017/11/09
0
0
ORACLE SQL性能优化系列 (一) black_snai

ORACLE SQL性能优化系列 (一) 原创 2003年07月21日 18:34:00 标签: oracle / sql / 性能优化 / 优化 / 数据库 / statistics <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:......

rootliu
2018/04/12
7
0
《高性能MySQL》读书笔记之MySQL 优化

关于MySQL的执行过程: 1 MySQL中存在对SQL语句的改写,通过改写SQL达到优化SQL语句的目的,如将子查询改为关联查询. 2 MySQL并没有像Oracle 11g那样缓存执行计划,在Java开发中PreparedStateme...

周翔
2016/11/02
131
0
Oracle性能优化(SQL)

尽量少用IN操作符,基本上所有的IN操作符都可以用EXISTS代替。 不用NOT IN操作符,可以用NOT EXISTS或者外连接+替代。 Oracle在执行IN子查询时,首先执行子查询,将查询结果放入临时表再执行...

遵从本心
2013/08/12
993
0
Oracle SQL 性能优化技巧

1.选用适合的ORACLE优化器  ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZERMODE参数的各种声明,...

ghostwl
2014/12/19
35
0

没有更多内容

加载失败,请刷新页面

加载更多

1、Docker学习,第一天

Docker学习,第一天 一、Docker简介 环境配置如此之麻烦,换台机器,重来一次,费事费力。安装的时候,把原始环境一模一样的复制过来。开发人员利用Docker可以消除写作编码时,”在我的机器上...

有一个小阿飞
15分钟前
3
0
10.23

一、编写一个程序,把用分钟表示的时间转换成用小时和分钟表示的时间。使用#define或const创建一个表示60的符号常量或const变量。通过while循环让用户重复输入值,直到用户输入小于或等于0...

197王彧涛
41分钟前
3
0
手机视频如何制作GIF动图

很多小伙伴都喜欢用GIF动图在各大社交软件上与好友斗图,那你知道这些好玩有趣的GIF动图是如何制作的吗?下面教你一个将手机视频制作成GIF动图的方法,让你都可以随时随地制作有趣的表情包,...

白米稀饭2019
45分钟前
5
0
Spring Security 实战干货:实现自定义退出登录

1. 前言 上一篇对 Spring Security 所有内置的 Filter 进行了介绍。今天我们来实战如何安全退出应用程序。 2. 我们使用 Spring Security 登录后都做了什么 这个问题我们必须搞清楚!一般登录...

码农小胖哥
今天
10
0
JVM核心知识-类加载机制

JVM中类的生命周期包括7个阶段,加载、准备、验证、解析、初始化、使用、卸载。其中准备、验证、解析被归为连接阶段。 加载 jvm在这个阶段完成的工作 通过类名获取类的二进制字节流 将这个字...

moon888
今天
8
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部