ORACLE数据优化建议

一、            技术方面优化

1.  Oracle用INSTR代替LIKE。数据库对不了函数内部优化不同，在数据量小的时候看不出什么差别，但是在大数据量情况下效率差别较为明显。

2.  查询使用比较频繁的字段添加索引，默认索引不支持空值，若字段存在空值使用位图索引。当列中的值比较多而且重复较少的时候适合创建B树索引，当列中的值相对固定只有很小的集合供选择的时候适合的位图索引。

3.  OR条件连接能修改联合结果的最好修改为UNION ALL。同样IN条件能修改的也要进行修改。

4.  在数据库中，UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。而UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL。

5.  查询字段中包含查询条件，如：

 

SELECT (SELECT XXX FROM AAA A WHERE A.ID = B.PID) AS XX FROM BBB B;

 

类似这种写法进行了for循环嵌套，耗费资源较大，建议类似语句进行优化。

SELECT A.XXX AS XX FROM AAA A LEFT JOIN BBB B ON A.ID = B.PID;

  

 

 

6.  使用表的别名(Alias)：
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

 

7.  避免在索引列上进行计算。
WHERE子句中，如果索引列是函数的一部分。优化器将无法使用索引从而使用全表扫描。

任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。
举例:：下列SQL条件语句中的列都建有恰当的索引，但30万行数据情况下执行速度却非常慢： 

SELECT * FROM RECORD WHERE SUBSTRB(CARDNO,1,4)='5378' (13秒) 
SELECT * FROM RECORD WHERE AMOUNT/30< 1000（11秒） 
SELECT * FROM RECORD WHERE TO_CHAR(ACTIONTIME,'YYYYMMDD')='19991201'（10秒）

 

由于where子句中对列的任何操作结果都是在SQL运行时逐行计算得到的，因此它不得不进行表扫描，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表扫描，因此将SQL重写如下：
差别是很明显的！

 

SELECT * FROM RECORD WHERE CARDNO LIKE '5378%'（< 1秒）
SELECT * FROM RECORD WHERE AMOUNT < 1000*30（< 1秒）
SELECT * FROM RECORD WHERE ACTIONTIME= TO_DATE ('19991201' ,'YYYYMMDD')（< 1秒）

 

8.  避免不必要的类型转换

需要注意的是，尽量避免潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致全表扫描。

示例：

表tab1中的列col1是字符型（char)，则以下语句存在类型转换：

SELECT COL1,COL2 FROM TAB1 WHERE COL1>10;

###应该写为： 

SELECT COL1,COL2 FROM TAB1 WHERE COL1>'10';

 

 

 

 

 

9.  优化GROUP BY:

 

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了很多。

 

##低效: 
SELECT JOB, AVG(SAL)

  FROM EMP

 GROUP BY JOB

HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER';
##高效: 
SELECT JOB, AVG(SAL)

  FROM EMP

 WHERE JOB = 'PRESIDENT'

    OR JOB = 'MANAGER'

 GROUP BY JOB;

 

二、            业务方面优化

 

 

1.        按业务查询条件区分拼装查询语句

在对查询条件复杂但业务上又不能对查询条件进行删减的查询语句，可以进行适应性调整。对使用频繁的查询条件和相对使用较少的条件进行分别封装查询语句。比如查询一个流程信息时，查询条件中要有流程节点，节点处理人，代理人……。在这里按处理人查询是一个比较频繁的动作，但按代理人查询是一个使用较少但又不可缺少的条件。这里咱们就可以采取分别封装的办法。

2.        时效性不强的业务使用缓存

对于一些时效性不强的业务，建设使用缓存，而不是每次访问数据库，比如首页面中的企业新闻、企业公告……。像这种人人都会使用，频繁访问，时效性要求不强的业务尽量使用缓存技术来降低数据库负载。

3.        使用默认值

在可能建立索引的列上添加默认值。由于很简单，oracle中存在空值列不使用B树索引。而且列值比较多的列不适合建立位图索引。所以合理使用默认值非常有必要。