Oracle优化总结

ispsh

　　转载：https://blog.csdn.net/dayu236/article/details/52662395
　　分析和优化的基本步骤如下：
　　1、如果是SQL语句的写法问题，我们可以通过在不更改业务逻辑的情况下改写SQL来加以解决；
　　2、如果是不必要的全表扫描/排序而导致了目标SQL的性能问题，我们可以通过建立合适的索引（包括函数索引、位图索引等）来加以解决；
　　3、如果是表或者索引的不良设计导致的目标SQL的性能问题，我们可以通过重新设计表/索引，重新组织表里的数据来加以解决；
　　4、如果上述调整措施都失效，我们可以考虑用并行来缩短目标SQL的执行时间；
　　5、如果上述调整措施、包括并行都失效，我们还可以在联系实际业务的基础上更改目标SQL的执行逻辑，甚至不执行目标SQL，这是最彻底的优化。
　　Oracle数据库优化的方法
　　1、减少访问数据库的次数。
　　2、不要让数据库做得太多。
　　（1）SELECT子句中避免使用' '：ORACLE在解析的过程中，会将' '依次转换成所有的列名， 这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
　　（2）sql语句用大写的：因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行。另外，在java代码中尽量少用连接符“＋”连接字符串！
　　（3）使用表的别名(Alias)：当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
　　（4） 用>=替代>：
　　高效: SELECT FROM EMP WHERE DEPTNO >=4
　　低效: SELECT  FROM EMP WHERE DEPTNO >3
　　两者的区别在于， 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。
　　（5） 用EXISTS替代IN、用NOT EXISTS替代NOT IN：在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接.在这种情况下， 使用EXISTS(或NOT EXISTS)通常将提高查询的效率。 在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。 为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。
　　例子：
　　（高效）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')
　　（6）用EXISTS替换DISTINCT：例如:
　　低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO
　　高效: SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
　　（7）尽量用UNION-ALL 替换UNION ( if possible)
　　当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并， 然后在输出最终结果前进行排序。
　　低效：SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
　　UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
　　高效: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
　　UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
　　（7） 避免在索引列上使用NOT：通常，避免在索引列上使用NOT， NOT会产生在和在索引列上使用函数相同的影响。 当Oracle”遇到”NOT，就会停止使用索引转而执行全表扫描。
　　（8）用索引提高效率：索引是表的一个概念部分，用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构。 通常，通过索引查询数据比全表扫描要快。当Oracle找出执行查询和Update语句的最佳路径时，Oracle优化器将使用索引。同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是，它提供了主键(primary key)的唯一性验证。通常, 在大型表中使用索引特别有效。 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价。 索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O 。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的。
　　Oracle建立索引的建议
　　1、表的主键、外键必须有索引；Oracle中外键不添加索引会引起死锁。当删除父表指定记录时，子表会添加表级锁，另一个进程删除父表记录（即使是不同记录）时，会造成子表死锁。当对子表的外键列添加索引后，死锁被消除，因为这时删除父表记录不需要对子表加表级锁。
　　2、经常与其他表进行连接的表，在连接字段上应该建立索引；
　　3、索引应该建在选择性高的字段上。例如：表示性别的数据列，由于只有男女两种值，就属于选择性低；
　　4、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
　　5、复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替：
　　A、正确选择复合索引中的主列字段（第一个字段），一般是选择性较好的字段；
　　B、复合索引的几个字段是否经常同时以AND方式出现在WHERE子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
　　C、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
　　6、频繁进行数据操作的表，不要建立太多的索引；
　　7、删除无用的索引，避免对执行计划造成负面影响；
　　总之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
　　在大多数情况下,复合索引比单字段索引好.复合索引比单字段索引的效率高，但是，复合索引比单字段索引的内容原理复杂，复合索引有两个重要原则需要把握：前缀性和可选性.如果糊里糊涂的滥用复合索引，效果适得其反。
　　以例子来说明，例子如下:
　　假设在员工表(EMP)的(ENAME,JOB,MGR)3个字段上建了一个索引，例如索引名叫IDX_1.3个字段分别为员工姓名，工作和所属经理号。然后，写如下一个查询语句，并不断进行查询条件和次序的排列组合，例如:
　　[sql] view plain copy
　　SELECT FROM EMP WHERE ENAME = 'A' AND JOB = 'B' AND MGR = 3 ;   
　　SELECT  FROM EMP WHERE JOB = 'B' AND ENAME = 'A' AND MGR = 3 ;
　　SELECT FROM EMP WHERE MGR = 3 AND ENAME = 'A' AND JOB = 'B' ;   
　　SELECT  FROM EMP WHERE MGR = 3 AND JOB = 'B' AND ENAME = 'A' ;
　　SELECT * FROM EMP WHERE JOB = 'B' AND MGR = 3 AND ENAME = 'A' ;
　　回答问题:在各种条件组合情况下,刚才建的索引(IDX_1) 是用还是不用?也就是说对EMP表的访问是全表扫描还是按索引(IDX_1)访问?
　　答案是 :  上述语句中只要有ENAME='A'条件,就能用上索引(IND_1),而不是全表扫描(这就是复合索引的前缀性).