Oracle SQL语句优化总结

一、合理使用索引

创建索引的根本目的：提高查询效率

使用规则：

1.在条件中经常用到的不同值较多的列上建立索引

2.在不同值少的列上，不要建立索引及位图索引（不但不会提高查询效率，反而会降低更新速度）

3.在经常进行连接的列上建立索引 

4.在频繁进行排序或分组的列上建立索引

如：order by D,E   需要注意：

在建立复合索引时，D列、E列和排序后面的顺序要相同

5.如果条件中经常对某个列应用某个函数后再指定查询条件，则应建立函数索引

如：select * from test where UPPER(owner)='BI'; 即使为条件列(owner列)建立了索引，当索引列上使用函数进行条件匹配，执行计划将不会选择索引扫描，这时可考虑建立函数索引：create index func_owner on test(UPPER(owner));

user_expressions 视图，主要针对函数索引，可以查看具体的函数信息。

以下情况，不会用到列上的索引：

1.存在数据类型的隐式转换时

select * from emp where empno = '123';  --empno为数字型，oracle会先将字符串'123'隐式转化为数字123.

2.列上有数学运算时

select * from emp where sal*2 < 1000 ; --在索引列上只有sal的值，并没有sal*2的值

3.使用不等于(<>)运算时 : where deptno<>10;

4.使用substr字符串函数时: where substr(ename,1,3) ='UZI';

5. '%'通配符放在前面时： where ename like '%UZI' ;

6.字符串连接( || )时 :  where ename || 'abc' ='UZIabc';

 

索引限制

即使相关字段上有索引，在如下4种情况，也不会走索引：

(1)    使用不等于操作符（<>、!=）    

(2)    使用IS NULL 或IS NOT NULL

(3)    使用函数

(4)    比较不匹配的数据类型  

所以在SQL 编写过程中，尽量避免以上4种情况。

 

10053事件

利用10053事件生成一个 SQL 分析过程的 trace 文件，分析一下执行计划的生成过程，看看到底是没有使用索引还是因为索引的成本比较大而选择了全表扫描。
1.先进行表分析：
exec dbms_ststs.gather_table_stats('user','tablename',cascade=>true);
2.开启10053事件：
alter session set events '10053 trace name context forever,level 1';
3.执行sql：
explain plan for  要运行的sql
4.关闭10053事件：
alter session set events '10053 trace name context off';

trace 文件的目录：$ORACLE_BASE/admin/sid/udump

 

查看真实执行计划：select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

二、避免或简化排序

1.以下是需要排序的情况，应当简化或避免对大型表进行重复的排序：

SQL中包含Group by、Order by、Distinct、Minus或Union字句；in子句中的SQL子查询。

以下情况不能有效利用索引：

1.排序列没有全部建立索引：如 order by D,E ; 而表中只在D列建立了索引

2.排序列与索引列的顺序不同：如 Creat index emp_idx1 on emp(empno,ename)   ;    order by ename,empno ;

3.排序列来自不同的表 （索引不能跨表）

 

三、消除对大表的扫描

1.两个表进行连接时，对连接列创建索引

2.使用并集来避免顺序存取。

例如:（a,b列上都创建了索引） select * from abc where a>10 or b<10; 应当改写为：

select * from abc where a>10 

union 

select * from abc where b<10

 

四、Exists和IN运算符

1.带IN的关联子查询是多余的，因为IN字句和子查询中相关操作的功能是一样的。

如：select name from emp_mem em where emp_id  in (select emp_id from emp_info ei where em.emp_id = ei.emp_id )

2.非关联子查询不要使用Exists子句，因为这样会产生笛卡尔积

3.尽量不适用NOT IN，使用 minus 会更快一些

 

分页查询

方法一：适合小数据量

select * form

(select rownum as rn,d.* from emp d

where rownum <=20 )

where rn>=11 ;

方法二 速度教稳定 推荐

select * form

(select row_number() over(order by ID)  as rn,d.* from emp d )

where rn between 11 and 20 ;

方法三  不适合大数据量

select * from emp where rownum <= 20

minus

select * from emp  where rownum < 11 ;