A
Aman_li

# 一、执行顺序及优化细则

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'

(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'

2.where执行顺序
where执行会从至下往上执行
select *
from student_info si --学生信息表
where si.school_id=10 --学院ID
and  si.system_id=100--系ID

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快

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.  * 号引起的执行效率

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重复输出两个结果集合中相同记录

,
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

5、分离表和索引

1、计算表的记录数时

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

select count(*) from Student_info si

2.使用函数提高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 = '李明'

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.减少访问数据库的次数

from student_info si --学生信息表
where si.student_id=100

from student_info si --学生信息表
where si.student_id=20

(
from student_info si --学生信息表
,student_info si2
where si.student_id=100
and si2.student_id=20

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'

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、自动选择索引

7、 !=导致索引失效

select ui.user_name
from user_info ui--员工信息表
where ui.student_id!=0

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%'/*索引有效*/

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 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(+)

A

### Aman_li

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 优化

2016/11/02
131
0
Oracle性能优化（SQL）

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

197王彧涛
41分钟前
3
0

45分钟前
5
0
Spring Security 实战干货：实现自定义退出登录

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

10
0
JVM核心知识-类加载机制

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

moon888

8
0