oracle中查询效率的优化.docx
- 文档编号:12208340
- 上传时间:2023-04-17
- 格式:DOCX
- 页数:12
- 大小:18.25KB
oracle中查询效率的优化.docx
《oracle中查询效率的优化.docx》由会员分享,可在线阅读,更多相关《oracle中查询效率的优化.docx(12页珍藏版)》请在冰豆网上搜索。
oracle中查询效率的优化
Oracle中怎样提高查询效率(sql优化)
一、执行顺序及优化细则
1.表名顺序优化
(1)基础表放下面,当两表进行关联时数据量少的表的表名放右边
表或视图:
Student_info (30000条数据)
Description_info(30条数据)
select*
fromdescription_infodi
,student_info si--学生信息表
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
与
select*
fromstudent_info si--学生信息表
,description_infodi
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
以student_info作为基础表,你会发现运行的速度会有很大的差距。
(2)当出现多个表时,关联表被称之为交叉表,交叉表作为基础表
select*
fromdescription_infodi
,description_infodi2
,student_info si--学生信息表
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
andsi.school_id=di.lookup_code(+)
anddi.lookup_type(+)='SCHOOL_ID'
与
select*
fromstudent_info si--学生信息表
,description_infodi
,description_infodi2
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
andsi.school_id=di.lookup_code(+)
anddi.lookup_type(+)='SCHOOL_ID'
以student_info作为基础表,你会发现运行的速度会有很大的差距,
当基础表放在后面,这样的执行速度会明显快很多。
2.where执行顺序
where执行会从至下往上执行
select*
fromstudent_infosi--学生信息表
wheresi.school_id=10--学院ID
and si.system_id=100--系ID
摆放where子句时,把能过滤大量数据的条件放在最下边
3.isnull和isnotnull
当要过滤列为空数据或不为空的数据时使用
select*
fromstudent_infosi--学生信息表
wheresi.school_idisnull(当前列中的null为少数时用isnotnull,否则isnull)
4.使用表别名
当查询时出现多个表时,查询时加上别名,
避免出现减少解析的时间字段歧义引起的语法错误。
5.where执行速度比having快
尽可能的使用where代替having
select fromstudent_infosi
groupbysi.student_id
havingsi.system_id!
=100
andsi.school_id!
=10
(select fromstudent_infosi
wehresi.system_id!
=100
andsi.school_id!
=10
groupbysi.student_id)
6. *号引起的执行效率
尽量减少使用select*来进行查询,当你查询使用*,
数据库会进行解析并将*转换为全部列。
二、替代优化
1、用>=替代>
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_id>=10
与
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_id>9
执行时>=会比>执行得要快
2、用UNION替换OR(适用于索引列)
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_id=10
union
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_id=2
上面语句可有效避免全表查询
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_id=10
orui.student_id=2
如果坚持要用OR,可以把返回记录最少的索引列写在最前面
3、用in代替or
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_id=10
orui.student_id=20
orui.student_id=30
改成
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_idin(10,20,30)
执行会更有效率
4、UnionAll与Union
UnionAll重复输出两个结果集合中相同记录
如果两个并集中数据都不一样.那么使用UnionAll与Union是没有区别的,
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_id=10
unionAll
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_id=2
与
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_id=10
union
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_id=2
但UnionAll会比Union要执行得快
5、分离表和索引
总是将你的表和索引建立在另外的表空间内
决不要将这些对象存放到SYSTEM表空间里
三、一些优化技巧
1、计算表的记录数时
selectcount(si.student_id)
fromStudent_infosi(student_id为索引)
与
selectcount(*)fromStudent_infosi
执行时.上面的语句明显会比下面没有用索引统计的语句要快
2.使用函数提高SQL执行速度
当出现复杂的查询sql语名,可以考虑使用函数来提高速度
查询学生信息并查询学生(李明)个人信息与的数学成绩排名
如
selectdi.descriptionstudent_name
,(selectres.order_num--排名
fromresultres
whereres.student_id=di.student_id
orderbyresult_math)order_num
fromdescription_infodi
,student_info si--学生信息表
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
anddi.description='李明'
而且我们将上面order_num排名写成一个fuction时
createorreplacepackagebodyorder_num_pkgis
functionorder_num(p_student_idnumber)return_numberis
v_return_numbernumber;
begin
selectres.order_num--排名
intov_return_number
fromresultres
whereres.student_id=di.student_id
orderbyresult_math;
returnv_return_number;
exception
whenothersthen
null;
returnnull;
end;
endorder_num_pkg;
执行
selectdi.descriptionstudent_name
,order_num_pkg.order_num(di.student_id)order_num
fromdescription_infodi
,student_info si--学生信息表
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
anddi.description='李明'
执行查询时的速度也会有所提高
3.减少访问数据库的次数
执行次数的减少(当要查询出student_id=100的学生和student_id=20的学生信息时)
selectaddress_id
fromstudent_infosi--学生信息表
wheresi.student_id=100
与
selectaddress_id
fromstudent_infosi--学生信息表
wheresi.student_id=20
都进行查询.这样的效率是很低的
而进行
(
selectsi.address_id,si2.address_id
fromstudent_infosi--学生信息表
student_infosi2
wheresi.student_id=100
andsi2.student_id=20
与
selectdecode(si.student_id,100,address_id)
,decode(si.student_id,20,address_id)
fromstudent_infosi
)
执行速度是提高了,但可读性反而差了..
所以这种写法个人并不太推荐
4、用Exists(NotExists)代替In(NotIn)
在执行当中使用Exists或者NotExists可以高效的进行查询
5、Exists取代Distinct取唯一值的
取出关联表部门对员工时,这时取出员工部门时,出现多条..
selectdistinctdi.dept_name
fromdepartments_infodi--部门表
,user_info ui--员工信息表
whereui.dept_no=di.dept_no
可以修改成
selectdi.dept_name
fromdepartments_infodi--部门表
where exists(select'X'
fromuser_infoui--员工信息表
wheredi.dept_no=ui.dept_no)
6、用表连接代替Exists
通过表的关联来代替exists会使执行更有效率
selectui.user_name
fromuser_infoui--员工信息表
whereexists(select'x'
fromdepartments_infodi--部门表
wheredi.dept_no=ui.dept_no
andui.dept_cat='IT');
执行是比较快,但还可以使用表的连接取得更快的查询效率
selectui.user_name
fromdepartments_infodi
,user_info ui--员工信息表
whereui.dept_no=di.dept_no
andui.department_type_code='IT'
代码是经测试并进行优化所写,
以上只例子,具体使用还是要针对各个不同的具体的业务使用用Exists(NotExists)代替In(NotIn)
四、索引篇
1、运算导致的索引失效
selectdi.descriptionstudent_name
,(selectres.order_num--排名
fromresultres
whereres.student_id=di.student_id
orderbyresult_math)order_num
fromdescription_infodi
,student_info si--学生信息表
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
andsi.student_id+0=100/*student_id索引将失效*/
2、类型转换导致的索引失效
selectdi.descriptionstudent_name
,(selectres.order_num--排名
fromresultres
whereres.student_id=di.student_id
orderbyresult_math)order_num
fromdescription_infodi
,student_info si--学生信息表
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
anddi.student_id='100'
student_id为number类型的索引,当执行下列语句,
oracle会自动转换成
selectdi.descriptionstudent_name
,(selectres.order_num--排名
fromresultres
whereres.student_id=di.student_id
orderbyresult_math)order_num
fromdescription_infodi
,student_info si--学生信息表
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
anddi.student_id=to_number('100')
所幸,只是解析并转换类型,并没有导到失效,
但要是写成下面,将会使用其失效
selectdi.descriptionstudent_name
,(selectres.order_num--排名
fromresultres
whereres.student_id=di.student_id
orderbyresult_math)order_num
fromdescription_infodi
,student_info si--学生信息表
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
andto_char(di.student_id)='100'
3、在索引列上进行计算引起的问题
selectdi.descriptionstudent_name
,(selectres.order_num--排名
fromresultres
whereres.student_id=di.student_id
orderbyresult_math)order_num
fromdescription_infodi
,student_info si--学生信息表
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
anddi.student_id-2=10
在索引列中进行运算,将会不使用索引而使用全表扫描
而将
selectdi.descriptionstudent_name
,(selectres.order_num--排名
fromresultres
whereres.student_id=di.student_id
orderbyresult_math)order_num
fromdescription_infodi
,student_info si--学生信息表
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
anddi.student_id=10+2
将会得到高效的运行速度
4、Isnotnull引起的问题(student_id为索引)
不要把存在空值的列做为索引,否则无法使用索引
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_idisnotnull--索引失效
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_id>=-1--索引有效
5、Orderby导致索引失效(student_id为索引)
selectui.user_name
fromuser_infoui--员工信息表
groupbyui.student_id
而使用
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_id>=-1
将使其有效,
在orderby中只存在两种条件下可以使用索引
(ORDERBY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序
ORDERBY中所有的列必须定义为非空.)
6、自动选择索引
如果表中有两个以上(包括两个)索引,其中有一个唯一性索引,而其他是非唯一性.
在这种情况下,ORACLE将使用唯一性索引而完全忽略非唯一性索引.
7、!
=导致索引失效
selectui.user_name
fromuser_infoui--员工信息表
whereui.student_id!
=0
在Where中使用!
=将会把索引失效
8、%导致的索引失效
selectdi.descriptionstudent_name
,(selectres.order_num--排名
fromresultres
whereres.student_id=di.student_id
orderbyresult_math)order_num
fromdescription_infodi
,student_info si--学生信息表
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
anddi.look_codeLike'%12'/*look_code为索引,索引将失效*/
而
selectdi.descriptionstudent_name
,(selectres.order_num--排名
fromresultres
whereres.student_id=di.student_id
orderbyresult_math)order_num
fromdescription_infodi
,student_info si--学生信息表
wheresi.student_id=di.lookup_code(+)
anddi.lookup_type(+)='STUDENT_ID'
anddi.look_codeLike'12%'/*索引有效*/
以上只例子,具体还是要针对各个不同的具体的业务使用
五、oracle中的notExists与Notin的性能巨大差异
NotExists与Notin的作用同样是排除数据,在oracle中使用notin并不象mysql中的执行那么快,如(
selectjt1.doc_num--单据号码
,oalc.d
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 查询 效率 优化