oracle中查询效率的优化Word文档下载推荐.docx

1、（2） 当出现多个表时,关联表被称之为交叉表,交叉表作为基础表 ,description_info di2 and si.school_id = di.lookup_code（+）SCHOOL_ID与以student_info作为基础表,你会发现运行的速度会有很大的差距,当基础表放在后面,这样的执行速度会明显快很多。2.where执行顺序where执行会从至下往上执行from student_info si -学生信息表where si.school_id=10 -学院IDand si.system_id=100-系ID摆放where子句时,把能过滤大量数据的条件放在最下边3. is null

2、 和is not null当要过滤列为空数据或不为空的数据时使用where si.school_id is null（当前列中的null为少数时用is not null,否则is null）4.使用表别名当查询时出现多个表时,查询时加上别名,避免出现减少解析的时间字段歧义引起的语法错误。5. where执行速度比having快尽可能的使用where代替havingselect from student_info sigroup by si.student_idhaving si.system_id!=100 and si.school_id!=10（selectwehre si.system_

3、id!and si.school_id!group by si.student_id）6. * 号引起的执行效率尽量减少使用select * 来进行查询,当你查询使用*,数据库会进行解析并将*转换为全部列。二、替代优化1、用=替代select ui.user_name from user_info ui-员工信息表 where ui.student_id 与 select ui.user_name9 执行时=会比执行得要快2、用UNION替换OR （适用于索引列） where ui.student_id=10 union where ui.student_id=2 上面语句可有效避免全表查询

4、where ui.student_id=10 or ui.student_id=2 如果坚持要用OR, 可以把返回记录最少的索引列写在最前面3、用in 代替or or ui.student_id=20 or ui.student_id=30 改成 where ui.student_id in （10,20,30） 执行会更有效率4、 Union All 与UnionUnion All重复输出两个结果集合中相同记录如果两个并集中数据都不一样.那么使用Union All 与Union是没有区别的, union All但Union All会比Union要执行得快5、分离表和索引总是将你的表和索引建立

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-

6、排名 from result res where res.student_id = di.student_id order by result_math） order_num and di.description = 李明而且我们将上面order_num排名写成一个fuction时create or replace package body order_num_pkg isfunction order_num（p_student_id number） return_number is v_return_number number;begin select res.order_num -排名 i

7、nto v_return_number order by result_math; return v_return_number;exception when others then null; return null;end;end order_num_pkg;执行 ,order_num_pkg.order_num（di.student_id） order_num执行查询时的速度也会有所提高3.减少访问数据库的次数执行次数的减少（当要查询出student_id=100的学生和student_id=20的学生信息时）select address_idwhere si.student_id=10

8、0where si.student_id=20都进行查询.这样的效率是很低的而进行（select si.address_id,si2.address_id,student_info si2and si2.student_id=20select 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

9、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 where exists （select X from user_info ui -员工信息表 where di.dept_no = ui.dept_no）6、用表连接代替Exists 通过表的关联

10、来代替exists会使执行更有效率where exists （select x from departments_info di-部门表 where di.dept_no = ui.dept_no and ui.dept_cat = IT）;执行是比较快,但还可以使用表的连接取得更快的查询效率 from departments_info di where ui.dept_no = di.dept_no and ui.department_type_code = 代码是经测试并进行优化所写,以上只例子,具体使用还是要针对各个不同的具体的业务使用用Exists（Not Exists）代替In（No

11、t In）四、索引篇1、运算导致的索引失效 and si.student_id+0=100/*student_id索引将失效*/2、类型转换导致的索引失效 and di.student_id=100student_id为number类型的索引,当执行下列语句,oracle会自动转换成 and di.student_id=to_number（所幸,只是解析并转换类型,并没有导到失效,但要是写成下面,将会使用其失效 and to_char（di.student_id）=3、在索引列上进行计算引起的问题 and di.student_id-2=10在索引列中进行运算,将会不使用索引而使用全表扫描而将

12、 and di.student_id=10+2将会得到高效的运行速度4、 Is not null引起的问题（student_id为索引）不要把存在空值的列做为索引,否则无法使用索引 where ui.student_id is not null-索引失效=-1-索引有效5、Order by导致索引失效（student_id为索引） group by ui.student_id而使用=-1 将使其有效, 在order by中只存在两种条件下可以使用索引 （ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序ORDER BY中所有的列必须定义为非空. ）6、自动选择索引如果表中

13、有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引7、 !=导致索引失效 where ui.student_id!=0在Where中使用!=将会把索引失效8、%导致的索引失效 and di.look_code Like %12/*look_code为索引,索引将失效*/而12%/*索引有效*/以上只例子,具体还是要针对各个不同的具体的业务使用五、oracle 中的not Exists与Not in的性能巨大差异Not Exists与Not in的作用同样是排除数据,在oracle 中使用not in并不象mysql中的执行那么快,如（select jt1.doc_num -单据号码 ,oalc.d