oracle_sql语句优化规则

oracle在sql语句优化规则：

1：多表关联表名顺序规则：
FROM最后在表是被作为基础表在，所以小的表应该放在最后，如果是三个以上的表可以考虑
把交叉表作为基础表，交叉表是指被其它表所应用的表。

2：where字句中的链接顺序：
  可以过滤掉最大数据量的条件放在where字句的最后。

3：select 子句中避免使用‘*’；
   因为oracle需要把*装换成列名。耗费时间。

4：减少访问数据库的次数（应该是对整个大程序而言，能少访问就少）
oracle在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等。

5：在SQL*PLUS,SQL*FORMS和PRO*C中，重新设置ARRAYSIZE参数，可以增加每次数据库访问的数据量，建议值是200。

6：使用DECODE函数来减少处理时间：
使用DECODE函数可以避免重复扫描相同记录或重复链接相同的表。

7：整合简单，无关联的数据库访问：
如果你有几个简单的数据库查询语句，你可以把它们整合到一个数据库查询中，即使他们没有什么关联。但是要避免出现大表之间的关联。

8：删除重复记录：
最高效的删除重复记录的方法（因为使用了rowid）：
DELETE FROM EMPE WHERE E.ROWID>(SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

9：用TRUNCATE替代DELETE：

10：操作了数据库后，尽快COMMIT

11:用where子句替换HAVING子句：（暂没明白ON的语法）
优先使用的次序：ON,WHERE,HAVING，比较它们的用法。

12：减少对表的查询 （没明白）

13：通过内部函数提高SQL效率： （没明白）

14：使用表的别名：
    在多个表查询时，每个字段前加表别名，可以减少ORACLE解析和引起歧义

15：用EXISTS替代IN，用NOT EXISTS替代NOT IN
在子查询中，NOT in子句将执行一个内部排序和合并，无论在哪种情况下，NOT IN 都是最低效的，他对子查询中的表实行了遍历。可以改成外连接或者NOT EXISTS.
EXP高效： SELECT * FROM EMP WHERE EMPNO>0 AND EXISTS (SELECT 'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')
低效：SELECT * FROM EMP WHERE EMPNO>0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC='MELB')
16:识别‘低效执行’的SQL语句：
SELECT EXECUTIONS,DISK_READS,BUFFER_GETS,ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)HIT_RADIO,ROUND(DISK_READS/EXECUTIONS,2)READS_PER_RUN,SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS>0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8 ORDER BY 4 DESC;

17:用索引提高效率：
特别是在多表关联时特别有效，但是要索引会降低INSERT DELETE UPDATE操作的效率。
重构索引是必要的：ALTER INDEX<INDEXNAME> REBUILD <TABLESPACENAME>

18:用EXISTS 替换DISTINCT: （估计有很多时候是无法替换的）
EXP低效：
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO
高效：SELECT DEPT_NO,DEPT_FNAME FROM DEPT D WHERE EXISTS (SELECT 'X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

19:SQL 语句用大写的：因为oracle总是先解析sql语句，并把小写字母转换成大写。

20：java代码中尽量少用连接符“+”连接字符串。
21：通常避免在索引列上使用NOT：
避免在索引列上使用NOT，NOT会产生和在索引列上使用函数相同的效果，ORACLE遇到NOT，它就会停止使用索引转而执行全表扫描。
22：避免在索引列上使用计算。
WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。
低效：SELECT ...FROM DEPT WHERE SAL*12>2500;
高效：SELECT ...FROM DEPT WHERE SAL>2500/12;

23:用>=替代>
高效：SELECT * FROM EMP WHERE DEPTNO>= 4;
低效：SELECT * FROM EMP WHERE DEPTNO>3;

24:用UNION 替换 OR（使用于索引列）
OR将引起全表扫描，只要是针对字段是索引字段。以下LOC_ID 和REGION上都建有索引。
高效：
SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID = 10 UNION
SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE REGION = "MELBOURNE"
低效：
SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = "MELBOURNE";
前提条件一定是索引列，否则效率也许会变得更慢。

25：用IN 来替换OR，实际执行效果还须检验。
低效：SELECT ...FROM LOCATION WHERE LOC_ID =10 OR LOC_ID=20 OR LOC_ID =30;
高效：SELECT ...FROM LOCATION WHERE LOC_ID IN (10,20,30);

26:避免在索引列上使用IS NULL 和IS NOT NULL
对于单列索引，如果只为空，索引中将不存在此记录，对于复合索引，如果每个列都为空，索引页不存在该记录。即，可以往表里面插入多条索引为空的相同的行。
因为空值不存在于索引列中，所以WHERE子句对索引列做空值比较，将使ORACLE停用该索引。
低效：
SELECT ... FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
高效：SELECT ...FROM DEPARTMENT WHERE DEPT_CODE >=0;

27:总是使用索引的第一个列：
如果索引是建立在多个列上，只有在它的第一个列被WHERE 子句引用时，优化器才会选择使用该索引。如果只使用了其它的列，优化器将使用全表扫描，而忽略了索引。
28：UNION-ALL 替换UNION(如果有可能的话）
UNION会先按照UNION ALL方式查询出两个结果集合，然后排序。如果用UNION ALL的话就不需要排序了。但是值得注意的是，UNION ALL 将重复输出两个结果集中相同的记录。估计业务大多不允许。
29:用WHERE 替代ORDER BY :(业务上未必能使用，用WHERE没有排序能达到要求？）
ORDER BY 子句只在两种严格的条件下使用索引
ORDER BY 中所有的列必须包含在相同的索引中并保持在索引中的排列顺序
ORDER BY 中所有的列必须定义为非空。
WHERE 子句使用的索引和ORDER BY 子句中
30：避免改变索引列的类型：
SELECT ...FROM EMP WHERE EMPNO = '123'
ORACLE将转换为：SELECT ... FROM EMP WHERE EMPNO = TO_NUMBER('123');
SELECT ... FROM EMP WHERE EMP_TYPE = 123
ORACLE将转换为：SELECT ... FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123
如果EMP_TYPE是字符型的索引，将用不到索引了。
31：需要当心的WHERE子句：
  a)'!=',将不使用索引，记住，索引只能告诉你什么存在表中，而不能告诉你什么不在表中。
  b）‘||’，‘+’，这些操作就像其他函数一样，停用了索引。
  c）相同的索引列不能互相比较，这将启用全表扫描。
32：a。如果检索的数据量超过30%的表中记录数，使用索引将没有显著的效率提高。
    b。在特定情况下，使用索引也许会比全表扫描慢，但是上到一个数据量级后，使用索引要快几倍到几千倍。

33：避免使用耗费资源的操作：带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY 的sql语句会启动SQL引擎，执行耗费资源的排序（sort）功能。DISTINCT需要一次排序操作，其他的药多次。
一般可以考虑用其它方式重写。
调配好SORT_area_size，使用UNION,MINUS,INTERSECT也是可以考虑的，毕竟它们的可读性很强。

34：优化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;