oracle-sql语句优化

小灰灰 2022-05-18 09:51 309阅读 0赞

**oracle的SQL优化顺序:**

1.减少数据访问(减少磁盘访问)-->2.返回更少数据(减少网络交互)-->3.减少交互次数(减少网络交互)-->减少CPU开销-->利用更多资源(增加资源)

**总结一些sql优化建议:**

**一:操作符的优化**

**1、IN 操作符**

用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：

ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。

**推荐方案：**在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替。

**2.not in 操作符**

此操作是强列不推荐使用的，因为它不能应用表的索引。

**推荐方案：**用NOT EXISTS 方案代替

**3. IS NULL 或IS NOT NULL操作（判断字段是否为空）**

判断字段是否为空一般是不会应用索引的，因为索引是不索引空值的。

**推荐方案**：用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’’等。不允许字段为空，而用一个缺省值代替空值，如申请中状态字段不允许为空，缺省为申请。

**4、> 及 < 操作符（大于或小于操作符）**

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

**5、LIKE操作符**

LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。

一个实际例子：用YW\_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY\_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY\_BH LIKE ’X5400%’ OR YY\_BH LIKE ’B5400%’ 则会利用YY\_BH的索引进行两个范围的查询，性能肯定大大提高。

**6、UNION操作符**

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：  
select \* from gc\_dfys  
union  
select \* from ls\_jg\_dfys  
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

**推荐方案：**采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

比如:select \* from gc\_dfys  
union all  
select \* from ls\_jg\_dfys

**二、****SQL****书写的影响**

oracle解析Sql的顺序是从右至左,从下至上

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理

Oracle采用自下而上的顺序解析WHERE子句。 根据这个原理,表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

1、Where子句中的连接顺序：  
ORACLE采用自下而上的顺序解析WHERE子句。  
根据这个原理，表之间的连接必须写在其他WHERE条件之前， 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。  
2、Select子句中避免使用 “ \* ”：  
当你想在select子句中列出所有的column时，select \*,ORACLE在解析的过程中，会将 ‘\*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间  
3.尽量用表的别名：  
当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。  
4.用exists替代in（发现好多程序员不知道这个怎么用）：  
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用exists(或not exists)通常将提高查询的效率。  
(低效):select a.name from a where a.bid in (select b.id from b )   
(高效):select a.name from a where exists (select b.id from b where b.id=a.bid)  
5、用not exists替代not in：  
6.用表连接替换exists：  
通常来说，采用表连接的方式比exists更有效率。  
（低效）select ename from emp e where exists (select 1 from dept where dept\_no = e.dept\_no and dept\_cat = ‘W’);  
（高效）select ename from dept d, emp e where e.dept\_no = d.dept\_no and dept\_cat = ‘W’;  
**7.避免在索引列上使用is null和is not null**  
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。  
对于单列索引，如果列包含空值，索引中将不存在此记录；  
对于复合索引，如果每个列都为空，索引中同样不存在此记录；  
如果至少有一个列不为空，则记录存在于索引中。  
举例:  
如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null)，  
ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入)，  
然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空。  
因此你可以插入1000 条具有相同键值的记录,当然它们都是空!  
因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。

8.使用truncate代替delete

当删除表时，使用delete执行操作,是逐行删除，回滚端用来存放可恢复的信息，当没有提交事务的时候，执行回滚事务，数据会恢复到执行delete操作之前，而当用truncate是，回滚端则不会存放可恢复的信息，减少资源的调用。

比如:删除100万条数据的前五十万数据,可以先将后五十万数据存入临时表,然后将目标数据再truncate,再把临时表数据插入即可.

9:用>= 替代 >  
如果num上有一个索引。  
高效:   SELECT \*  FROM EMP  WHERE num>=4      
低效: SELECT \*  FROM EMP WHERE num>3

10:避免在索引列上使用计算  
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．  
低效： SELECT … FROM  DEPT  WHERE SAL \* 12 > 25000;  
高效: SELECT … FROM DEPT WHERE SAL > 25000/12;