ORACLE SQL优化

1、湖南视拓科技发展有限公司ORACLE SQL性能优化性能优化湖南视拓科技发展有限公司湖南视拓科技发展有限公司二二00六年十月六年十月湖南视拓科技发展有限公司1、合理进行数据库的设计、合理进行数据库的设计 很多的数据库很多的数据库DBA都把系统优化的焦点放在数据都把系统优化的焦点放在数据库参数的调整、碎片的清理等方面。但实际上最重要库参数的调整、碎片的清理等方面。但实际上最重要的莫过于在数据库设计的初期就着眼于数据库的优化的莫过于在数据库设计的初期就着眼于数据库的优化，在这个阶段就要根据设计准则规范操作，设计出高，在这个阶段就要根据设计准则规范操作，设计出高效合理的数据库模型。数据库的设计通常分

2、两步，即效合理的数据库模型。数据库的设计通常分两步，即逻辑设计和物理设计。逻辑设计和物理设计。 在数据库逻辑设计过程中，按照关系数据库的规在数据库逻辑设计过程中，按照关系数据库的规范化要求，为了保证数据库的一致性和完整性，设计范化要求，为了保证数据库的一致性和完整性，设计人员往往会设计过多的表间关联，以尽可能地降低数人员往往会设计过多的表间关联，以尽可能地降低数据冗余。表间关联是一种强制性措施（触发器实现）据冗余。表间关联是一种强制性措施（触发器实现），建立后，对父表和子表的插入、更新、删除操作均，建立后，对父表和子表的插入、更新、删除操作均要占用系统的开销。要占用系统的开销。湖南视拓科技发展

3、有限公司1、合理进行数据库的设计、合理进行数据库的设计 另外，在进行数据查询时也增加了表间连另外，在进行数据查询时也增加了表间连接查询的操作，从而使数据库的性能大为降低接查询的操作，从而使数据库的性能大为降低。如果此时不考虑优化的问题，那么就算是编。如果此时不考虑优化的问题，那么就算是编写的写的SQL语句优化做的再好，也是不可能提高语句优化做的再好，也是不可能提高数据库的性能。数据库的性能。 因此物理设计需折中考虑，根据业务规则因此物理设计需折中考虑，根据业务规则，确定对关联表的数据量大小、数据项的访问，确定对关联表的数据量大小、数据项的访问频度，对比较频繁的数据表关联查询应适当提频度，对比较

4、频繁的数据表关联查询应适当提高数据冗余设计。以下方法经实践验证往往能高数据冗余设计。以下方法经实践验证往往能提高性能。提高性能。 湖南视拓科技发展有限公司1、合理进行数据库的设计、合理进行数据库的设计1）如果规范化设计产生了许多）如果规范化设计产生了许多4路或更多路合并关系路或更多路合并关系，就可以考虑在数据库表中加入重复列。（不同表中，就可以考虑在数据库表中加入重复列。（不同表中相同数据列的数据一致性可以在具体的应用中通过触相同数据列的数据一致性可以在具体的应用中通过触发器等过程来实现）发器等过程来实现） 2）常用的计算字段（如总计、最大值等）和通过复）常用的计算字段（如总计、最大值等）和通

5、过复杂计算的到的字段可以考虑存储到数据库表中。这点杂计算的到的字段可以考虑存储到数据库表中。这点相当重要，因为很多情况下，计算这些值的开销往往相当重要，因为很多情况下，计算这些值的开销往往大于访问的开销。大于访问的开销。 3）把频繁被访问的数据同较少被访问的数据分开存）把频繁被访问的数据同较少被访问的数据分开存储。常用的做法是：储。常用的做法是： 湖南视拓科技发展有限公司1、合理进行数据库的设计、合理进行数据库的设计a)按列把一个表分割开（把所有的列分成多组）。这种做法要按列把一个表分割开（把所有的列分成多组）。这种做法要求在每个表中复制首要关键字。这样产生的设计有利于并行处求在每个表中复制首

6、要关键字。这样产生的设计有利于并行处理，可以利用数据库的理，可以利用数据库的parallel选项充分利用多余的选项充分利用多余的cpu和系和系统资源进行分布操作，并将产生列数较少的表。统资源进行分布操作，并将产生列数较少的表。 b)按行把一个表分割开（把所有的行分成多组）。这种方法适按行把一个表分割开（把所有的行分成多组）。这种方法适用于那些将包含大量数据的表。在应用中常要保留历史记录，用于那些将包含大量数据的表。在应用中常要保留历史记录，但是历史记录很少用到。因此可以把频繁被访问的数据同较少但是历史记录很少用到。因此可以把频繁被访问的数据同较少被访问的历史数据分开，分开的依据一般为时间或地点

7、等某一被访问的历史数据分开，分开的依据一般为时间或地点等某一字段，在实际使用中可通过视图把这些表综合起来使用。在字段，在实际使用中可通过视图把这些表综合起来使用。在Oracle8i就引入分区表的概念来解决此类问题。而且如果数据就引入分区表的概念来解决此类问题。而且如果数据行是作为子集被逻辑工作组（部门、销售分区、科目类别、地行是作为子集被逻辑工作组（部门、销售分区、科目类别、地理区域等）访问的，那么这种方法也很有好处。理区域等）访问的，那么这种方法也很有好处。 湖南视拓科技发展有限公司2、建立优化索引、建立优化索引 当要改进查询的性能时，索引是最便捷的，常常当要改进查询的性能时，索引是最便捷的

8、，常常也是最好的技术。多数新应用程序的存取速度问题是也是最好的技术。多数新应用程序的存取速度问题是由于缺少合适的索引。使用索引可以使性能发生巨大由于缺少合适的索引。使用索引可以使性能发生巨大的变化，而且确定需要什么样的索引也相对简单，合的变化，而且确定需要什么样的索引也相对简单，合理的索引设计要建立在对各种查询的分析和预测上。理的索引设计要建立在对各种查询的分析和预测上。一般来说一般来说: a) 有大量重复值、且经常有范围查询（有大量重复值、且经常有范围查询（between, , =, 25000; 高效高效: SELECT . FROM DEPT WHERE SAL 25000/12; 湖南

9、视拓科技发展有限公司4、注意组合索引的顺序、注意组合索引的顺序 由于列的顺序不同而导致索引的作用效果不同。由于列的顺序不同而导致索引的作用效果不同。例如：例如：(AGE，ADDRESS)顺序的结合索引。顺序的结合索引。WHERE AGE=28 AND ADDRESS=BEI JING &组合索引有效组合索引有效WHERE AGE=28 &组合索引有效组合索引有效WHERE ADDRESS=BEI JING &组合索引无效组合索引无效 湖南视拓科技发展有限公司5、选择最有效率的表名顺序、选择最有效率的表名顺序（只在基于规则的优化器中有效）（只在基于规则的优化器中有效） O

10、RACLE的解析器按照从右到左的顺序处理的解析器按照从右到左的顺序处理FROM子句中的表名子句中的表名,因此因此FROM子句中写在最后的表子句中写在最后的表(基础表基础表driving table)将被最先处理将被最先处理. 在在FROM子句中子句中包含多个表的情况下包含多个表的情况下,你必须选择记录条数最少的表你必须选择记录条数最少的表作为基础表作为基础表.当当ORACLE处理多个表时处理多个表时, 会运用排序及会运用排序及合并的方式连接它们合并的方式连接它们.首先首先,扫描第一个表扫描第一个表(FROM子句子句中最后的那个表中最后的那个表)并对记录进行排序并对记录进行排序,然后扫描第二个然

11、后扫描第二个表表(FROM子句中最后第二个表子句中最后第二个表),最后将所有从第二个最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。表中检索出的记录与第一个表中合适记录进行合并。 湖南视拓科技发展有限公司6、WHERE子句中的连接顺序子句中的连接顺序 （只在基于规则的优化器中有效）（只在基于规则的优化器中有效） ORACLE采用自下而上的顺序解析采用自下而上的顺序解析WHERE子句子句,根据这根据这个原理个原理,表之间的连接必须写在其他表之间的连接必须写在其他WHERE条件之前条件之前, 那些可那些可以过滤掉最大数量记录的条件必须写在以过滤掉最大数量记录的条件必须写在WHERE

12、子句的末尾。子句的末尾。 例如：例如： (低效低效,执行时间执行时间156.3秒秒) SELECT FROM EMP E WHERE SAL 50000 AND JOB = MANAGER AND 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); (高效高效,执行时间执行时间10.6秒秒) 湖南视拓科技发展有限公司6、WHERE子句中的连接顺序子句中的连接顺序 （只在基于规则的优化器中有效）（只在基于规则的优化器中有效） SELECT FROM EMP E WHERE 25 50000 AND JOB = MANAGER; 湖南视拓科技发展有限公

13、司7、SELECT子句中避免使用子句中避免使用 * 当你想在当你想在SELECT子句中列出所有的子句中列出所有的COLUMN时时,使用动态使用动态SQL列引用列引用* 是一个方便的方法是一个方便的方法.不幸不幸的是的是,这是一个非常低效的方法这是一个非常低效的方法. 实际上实际上,ORACLE在在解析的过程中解析的过程中, 会将会将* 依次转换成所有的列名依次转换成所有的列名, 这个这个工作是通过查询数据字典完成的工作是通过查询数据字典完成的, 这意味着将耗费更这意味着将耗费更多的时间。多的时间。 湖南视拓科技发展有限公司8、减少访问数据库的次数、减少访问数据库的次数 当执行每条当执行每条SQ

14、L语句时语句时, ORACLE在内部执行了许多工作在内部执行了许多工作: 解解析析SQL语句语句, 估算索引的利用率估算索引的利用率, 绑定变量绑定变量, 读数据块等等读数据块等等. 由由此可见此可见, 减少访问数据库的次数减少访问数据库的次数, 就能实际上减少就能实际上减少ORACLE的的工作量。工作量。例如：例如： 以下有三种方法可以检索出雇员号等于以下有三种方法可以检索出雇员号等于0342或或0291的职员的职员. 方法方法1 (最低效最低效) SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342; SELECT EMP

15、_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291; 湖南视拓科技发展有限公司8、减少访问数据库的次数、减少访问数据库的次数 方法方法2 (次低效次低效) DECLARE CURSOR C1 (E_NO NUMBER) IS SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO = E_NO; BEGIN OPEN C1(342); FETCH C1 INTO ,.,. ; . OPEN C1(291); FETCH C1 INTO ,.,. ; CLOSE C1; END; 湖南视拓科技发展有限

16、公司8、减少访问数据库的次数、减少访问数据库的次数 方法方法3 (高效高效) SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE FROM EMP A,EMP B WHERE A.EMP_NO = 342 AND B.EMP_NO = 291; 湖南视拓科技发展有限公司9、使用、使用DECODE函数来减少处理时间函数来减少处理时间 使用使用DECODE函数可以避免重复扫描相同记录或重复连接相函数可以避免重复扫描相同记录或重复连接相同的表。同的表。 例如例如: SELECT COUNT(*)，SUM(SA

17、L) FROM EMP WHERE DEPT_NO = 0020 AND ENAME LIKE SMITH%; SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE SMITH%; 湖南视拓科技发展有限公司9、使用、使用DECODE函数来减少处理时间函数来减少处理时间 你可以用你可以用DECODE函数高效地得到相同结果函数高效地得到相同结果 SELECT COUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,X,NUL

18、L) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030_SAL FROM EMP WHERE ENAME LIKE SMITH%; 类似的类似的,DECODE函数也可以运用于函数也可以运用于GROUP BY 和和ORDER BY子句中子句中 湖南视拓科技发展有限公司10、删除重复记录、删除重复记录 最高效的删除重复记录方法最高效的删除重复记录方法( 因为使用了因为使用了ROWID) DELETE FROM EMP E WHERE E.ROWID (S

19、ELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO); 湖南视拓科技发展有限公司11、用、用TRUNCATE替代替代DELETE 当删除表中的记录时当删除表中的记录时,在通常情况下在通常情况下, 回滚段回滚段(rollback segments) 用来存放可以被恢复的信息。用来存放可以被恢复的信息。 如果你没有如果你没有COMMIT事务事务,ORACLE会将数据恢复到会将数据恢复到删除之前的状态删除之前的状态(准确地说是恢复到执行删除命令之准确地说是恢复到执行删除命令之前的状况前的状况) 而当运用而当运用TRUNCATE时时, 回滚段

20、不再存放任何可被恢回滚段不再存放任何可被恢复的信息复的信息.当命令运行后当命令运行后,数据不能被恢复数据不能被恢复.因此很少的因此很少的资源被调用资源被调用,执行时间也会很短执行时间也会很短. (TRUNCATE只在删除全表适用只在删除全表适用,TRUNCATE是是DDL不是不是DML) 湖南视拓科技发展有限公司12、尽量多使用、尽量多使用COMMIT 只要有可能只要有可能,在程序中尽量多使用在程序中尽量多使用COMMIT, 这样这样程序的性能得到提高程序的性能得到提高,需求也会因为需求也会因为COMMIT所释放所释放的资源而减少：的资源而减少： COMMIT所释放的资源所释放的资源: a.

21、回滚段上用于恢复数据的信息回滚段上用于恢复数据的信息. b. 被程序语句获得的锁被程序语句获得的锁 c. redo log buffer 中的空间中的空间 d. ORACLE为管理上述为管理上述3种资源中的内部花费种资源中的内部花费 湖南视拓科技发展有限公司13、用、用Where子句替换子句替换HAVING子句子句 避免使用避免使用HAVING子句子句, HAVING 只会在检索出所有记录只会在检索出所有记录之后才对结果集进行过滤之后才对结果集进行过滤. 这个处理需要排序这个处理需要排序,总计等操作。总计等操作。 如果能通过如果能通过WHERE子句限制记录的数目子句限制记录的数目,那就能减少这

22、方那就能减少这方面的开销。面的开销。 例如：例如： 低效：低效： SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != SYDNEY AND REGION != PERTH 湖南视拓科技发展有限公司13、用、用Where子句替换子句替换HAVING子句子句 高效：高效： SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != SYDNEY AND REGION != PERTH GROUP BY REGION (

23、HAVING 中的条件一般用于对一些集合函数的比较中的条件一般用于对一些集合函数的比较,如如COUNT() 等等等等. 除此而外除此而外,一般的条件应该写在一般的条件应该写在WHERE子句中子句中) 湖南视拓科技发展有限公司14、减少对表的查询、减少对表的查询 在含有子查询的在含有子查询的SQL语句中语句中,要特别注意减少对表的查询要特别注意减少对表的查询.。 例如：例如： 低效：低效： SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) 湖南视拓科技发展有限公司14、减少对表的查询、减少对表的查询 高效高效 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604) 湖南视拓科技发展有限公司14、减少对表的查询、减少对表的查询 Update 多个多个Colum