ORACLE_SQL性能优化(这个很全的)讲述

1、ORACLEORACLE培训培训-SQL-SQL性能优化性能优化课程主要讨论：课程主要讨论： SQL语句执行的过程、语句执行的过程、ORACLE优化器优化器，表之间的关联，如何得到，表之间的关联，如何得到SQL执行执行计划，如何分析执计划，如何分析执行计划等内容，从而由浅到深的方式行计划等内容，从而由浅到深的方式了解了解SQL优化的过优化的过程，使大家逐步掌握程，使大家逐步掌握SQL优化。优化。1. 优化基础知识优化基础知识2. 性能调整综述性能调整综述3. 有效的应用设计有效的应用设计4. SQL语句的处理过程语句的处理过程5. Oracle的优化器的优化器6. Oracle的执行计划的执行

2、计划7. 注意事项注意事项n性能管理性能管理n性能问题性能问题n调整的方法调整的方法nSQL优化机制优化机制n应用的调整应用的调整nSQL语句的处理过程语句的处理过程共享共享SQLSQL区域区域SQLSQL语句处理的阶段语句处理的阶段共享游标共享游标SQLSQL编码标准编码标准nOracle 优化器介绍优化器介绍nSQL Tunning Tipsn优化优化Tools 尽早开始尽早开始 设立合适目标设立合适目标 边调整边监控边调整边监控 相关人员进行合作相关人员进行合作 及时处理过程中发生的意外和变化及时处理过程中发生的意外和变化 80/2080/20定律定律随着软件技术的不断发展，系统性能越来

3、越重要。随着软件技术的不断发展，系统性能越来越重要。系统性能主要用：系统性能主要用：系统响应时间和并发性来衡量。系统响应时间和并发性来衡量。造成造成SQL语句性能不佳大致有两个原因：语句性能不佳大致有两个原因：1. 开发人员只关注查询结果的正确性，忽视查询语句的效率。开发人员只关注查询结果的正确性，忽视查询语句的效率。2. 开发人员只关注开发人员只关注SQL语句本身的效率，对语句本身的效率，对SQL语句执行原理、影响语句执行原理、影响SQL执行效率的主要因素不清楚。执行效率的主要因素不清楚。* 前者可以通过深入学习前者可以通过深入学习SQL语法及各种语法及各种SQL调优技巧进行解决。调优技巧进

4、行解决。 SQL调优是一个系统工程，熟悉调优是一个系统工程，熟悉SQL语法、掌握各种内嵌函数、分语法、掌握各种内嵌函数、分 析函数的用法只是编写高效析函数的用法只是编写高效SQL的必要条件。的必要条件。* 后者从分析后者从分析SQL语句执行原理入手，指出语句执行原理入手，指出SQL调优应在优化调优应在优化SQL解解 析和优化析和优化CBO上。上。调优领域：调优领域：1. 应用程序级调优：应用程序级调优： * SQL语句调优语句调优 * 管理变化调优管理变化调优2. 实例级调优实例级调优 * 内存内存 * 数据结构数据结构 * 实例配置实例配置3. 操作系统交互操作系统交互 * I/O * SW

5、AP * Parameters本课程内容只讲解讨论应用程序级：本课程内容只讲解讨论应用程序级：Oracle SQL语句调优及管理变化调优语句调优及管理变化调优1. 调整业务功能调整业务功能2. 调整数据设计调整数据设计3. 调整流程设计调整流程设计4. 调整调整SQL语句语句5. 调整物理结构调整物理结构6. 调整内存分配调整内存分配7. 调整调整I/O8. 调整内存竞争调整内存竞争9. 调整操作系统调整操作系统n开发人员不能只注重功能的实现，不开发人员不能只注重功能的实现，不管性能如何管性能如何n开发人员不能把开发人员不能把Oracle当成一个黑盒当成一个黑盒子，必须了解其结构、处理子，必须

6、了解其结构、处理SQL和数和数据的方法据的方法n必需遵守既定的开发规范必需遵守既定的开发规范n未经过未经过SQL语句优化的模块不要上线语句优化的模块不要上线n定位有问题的语句定位有问题的语句n检查执行计划检查执行计划n检查执行过程中优化器的统计信息检查执行过程中优化器的统计信息n分析相关表的记录数、索引情况分析相关表的记录数、索引情况n改写改写SQL语句、使用语句、使用HINT、调整索引、调整索引、表分析表分析n有些有些SQL语句不具备优化的可能，需语句不具备优化的可能，需要优化处理方式要优化处理方式n达到最佳执行计划达到最佳执行计划n尽量简单，模块化尽量简单，模块化n易读、易维护易读、易维护

7、n节省资源节省资源内存内存CPU扫描的数据块要少扫描的数据块要少少排序少排序n不造成死锁不造成死锁 ORACLE将执行过的将执行过的SQL语句存放在内存的共享语句存放在内存的共享池池(shared buffer pool)中，可以被所有的数据库用户共享。中，可以被所有的数据库用户共享。当你执行一个当你执行一个SQL语句语句(有时被称为一个游标有时被称为一个游标)时时,如果它和之前的执行过的语句完全相同如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好就能很快获得已经被解析的语句以及最好的的 执行路径执行路径. 这个功能大大地提高了这个功能大大地提高了SQL的

8、执行性能并节省了内存的使用。的执行性能并节省了内存的使用。为了不重复解析相同的为了不重复解析相同的SQL语句，在第一次解析之后，语句，在第一次解析之后，Oracle将将SQL语句存放在内存中。这块位于系统全局区域语句存放在内存中。这块位于系统全局区域SGA(systemglobal area)的共享池的共享池(shared buffer poo1)中的中的内存可以被所有的数据库用户共享。因此，当你执行一个内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句语句(有时被称为一个游标有时被称为一个游标)时，如果它和之前执行过的语句完全时，如果它和之前执行过的语句完全相同，相同，Oracle

9、就能很快获得已经被解析的语句以及最好的执行就能很快获得已经被解析的语句以及最好的执行方案。方案。Oracle的这个功能大大地提高了的这个功能大大地提高了SQL的执行性能并节省的执行性能并节省了内存的使用。了内存的使用。可惜的是，可惜的是，Oracle只对简单的表提供高速缓冲只对简单的表提供高速缓冲(cache bufferiIlg)，这个功能并不适用于多表连接查询。数据，这个功能并不适用于多表连接查询。数据库管理员必须在启动参数文件中为这个区域设置合适的参数，库管理员必须在启动参数文件中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的当这个内存区域越大，就可以保

10、留更多的语句，当然被共享的可能性也就越大了。当向可能性也就越大了。当向Oracle提交一个提交一个SQL语句时，语句时，Oracle会首先在这块内存中查找相同的语句。会首先在这块内存中查找相同的语句。当前被执行的语句和共享池中的语句必须完全相同当前被执行的语句和共享池中的语句必须完全相同 （包括大小写、空格、换行等）（包括大小写、空格、换行等）两个语句所指的对象必须完全相同两个语句所指的对象必须完全相同 （同义词与表是（同义词与表是不同的对象）不同的对象）两个两个SQL语句中必须使用相同的名字的绑定变量语句中必须使用相同的名字的绑定变量(bind variables) 注意：注意：Oracle

11、对两者采取的是一种严格匹配策略，要达成共享。对两者采取的是一种严格匹配策略，要达成共享。SQL语句必语句必须完全相同须完全相同(包括空格、换行等包括空格、换行等)。能够使用共享的语句必须满足三个。能够使用共享的语句必须满足三个条件：条件： 字符级的比较。字符级的比较。当前被执行的语句和共享池中的语句必须完全相同。当前被执行的语句和共享池中的语句必须完全相同。例如：例如： SELECT * FROM ATABLE；和下面每一个；和下面每一个SQL语句都不同：语句都不同：SELECT *from ATABLESelect * From Atable； 语句所指对象必须完全相同语句所指对象必须完全相

12、同 即两条即两条SQL语句操作的数据库对象必须同一。语句操作的数据库对象必须同一。语句中必须使用相同命名的绑定变量。如：第一组的两个语句中必须使用相同命名的绑定变量。如：第一组的两个SQL语句是相同语句是相同的，可以共享；而第二组中两个语句不同，即使在运行时赋予不同的绑定变的，可以共享；而第二组中两个语句不同，即使在运行时赋予不同的绑定变量以相同的值：量以相同的值： 第一组第一组 select pin，name from people where pin = ：blk1.pin；select pin，name from people where pin =：blk1.pin；第二组第二组 se

13、lect pin，name from people where pin =：blk1.ot_jnd；select pin，name from people where pin = ：blk1.ov_jnd；共享共享SQL区域区域Sql 处理过程处理过程SQL PARSE与共享与共享SQL语句语句当一个当一个Oracle实例接收一条实例接收一条sql后后1、Create a Cursor 创建游标创建游标2、Parse the Statement 分析语句分析语句3、Describe Results of a Query 描述查询的结果集描述查询的结果集4、Define Output of a

14、Query 定义查询的输出数据定义查询的输出数据5、Bind Any Variables 绑定变量绑定变量6、Parallelize the Statement 并行执行语句并行执行语句7、Run the Statement 运行语句运行语句8、Fetch Rows of a Query 取查询出来的行取查询出来的行9、Close the Cursor 关闭游标关闭游标 为什么要为什么要bind variables?n字符级的比较字符级的比较:SELECT * FROM USER_FILES WHERE USER_NO = 10001234; 与与SELECT * FROM USER_FILE

15、S WHERE USER_NO = 10004321;n检查：检查：select name,executionsfrom v$db_object_cachewhere name like select * from user_files%什么叫做重编译问题什么叫做重编译问题什么叫做重编译？什么叫做重编译？下面这个语句每执行一次就需要在下面这个语句每执行一次就需要在SHARE POOL 硬解析一硬解析一次，一百万用户就是一百万次，消耗次，一百万用户就是一百万次，消耗CPU和内存，如果业务和内存，如果业务量大，很可能导致宕库量大，很可能导致宕库如果绑定变量，则只需要硬解析一次，重复调用即可如果绑定

16、变量，则只需要硬解析一次，重复调用即可select * from dConMsg where contract_no = 32013484095139绑定变量解决重编译问题绑定变量解决重编译问题未使用绑定变量的语句未使用绑定变量的语句sprintf(sqlstr, insert into scott.test1 (num1, num2) values (%d,%d),n_var1, n_var2);EXEC SQL EXECUTE IMMEDIATE :sqlstr ;EXEC SQL COMMIT; 使用绑定变量的语句使用绑定变量的语句 strcpy(sqlstr, insert into

17、test (num1, num2) values (:v1, :v2);EXEC SQL PREPARE sql_stmt FROM :sqlstr;EXEC SQL EXECUTE sql_stmt USING :n_var1, :n_var2;EXEC SQL COMMIT; 绑定变量的注意事项绑定变量的注意事项注意：注意：1、不要使用数据库级的变量绑定参数、不要使用数据库级的变量绑定参数cursor_sharing来强来强制绑定，无论其值为制绑定，无论其值为 force 还是还是similar2、有些带、有些带 0性能优于性能优于select count(*)from tab；尽量少嵌套

18、子查询，这种查询会消耗大量的尽量少嵌套子查询，这种查询会消耗大量的CPU资源；对于有比较多资源；对于有比较多or运算的查询，建议分成多个查询，用运算的查询，建议分成多个查询，用union all联结起来；多表查询联结起来；多表查询的查询语句中，选择最有效率的表名顺序。的查询语句中，选择最有效率的表名顺序。Oracle解析器对表解析从解析器对表解析从右到左，所以记录少的表放在右边。右到左，所以记录少的表放在右边。 尽量多用尽量多用commit语句提交事务，可以及时释放资源、解语句提交事务，可以及时释放资源、解锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的锁、释放日志空间、减少管理花费

19、；在频繁的、性能要求比较高的数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可以常驻内存：以常驻内存：alter tablecache； 在在Oracle中动态执行中动态执行SQL，尽量用，尽量用execute方式，不用方式，不用dbms_sql包。包。* SQL Tunning Tips *sql 语句的编写原则和优化语句的编写原则和优化 n随着数据库中数据的增加，系统的响随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很主要的问题之一。系统优化中一

20、个很重要的方面就是重要的方面就是SQLSQL语句的优化。对于语句的优化。对于大量数据，劣质大量数据，劣质SQLSQL语句和优质语句和优质SQLSQL语语句之间的速度差别可以达到上百倍，句之间的速度差别可以达到上百倍，对于一个系统不是简单地能实现其功对于一个系统不是简单地能实现其功能就可，而是要写出高质量的能就可，而是要写出高质量的SQLSQL语句，语句，提高系统的可用性。提高系统的可用性。n在多数情况下，在多数情况下，OracleOracle使用索引来更使用索引来更快地遍历表，优化器主要根据定义的快地遍历表，优化器主要根据定义的索引来提高性能。如果在索引来提高性能。如果在SQLSQL语句的语句

21、的wherewhere子句中写的子句中写的SQLSQL代码不合理，就代码不合理，就会造成优化器删去索引而使用全表扫会造成优化器删去索引而使用全表扫描，一般就这种描，一般就这种SQLSQL语句就是所谓的语句就是所谓的劣劣质质SQLSQL语句语句。sql 语句的编写原则和优化语句的编写原则和优化 n在编写在编写SQLSQL语句时我们应清楚优化器根语句时我们应清楚优化器根据何种原则来使用索引，这有助于写据何种原则来使用索引，这有助于写出高性能的出高性能的SQLSQL语句。语句。nSQLSQL语句的编写原则和语句的编写原则和SQLSQL语句的优化，语句的优化，请跟我一起学习以下几方面：请跟我一起学习以

22、下几方面：Tunning Tip的各个方面的各个方面1.不要让不要让Oracle做得太多；做得太多；2.给优化器更明确的命令；给优化器更明确的命令； 3.减少访问次数；减少访问次数；4.细节上的影响；细节上的影响；1.不要让不要让Oracle做得太多做得太多避免复杂的多表关联避免复杂的多表关联select from user_files uf, df_money_files dm, cw_charge_record ccwhere uf.user_no = dm.user_noand dm.user_no = cc.user_noand and not exists(select )?很难优化

23、，随着数据量的增加性能的风很难优化，随着数据量的增加性能的风险很大。险很大。避免使用避免使用 * 当你想在当你想在SELECT子句中列出所有的子句中列出所有的COLUMN时时,使用动态使用动态SQL列引用列引用 * 是一个方便的方法是一个方便的方法.不不幸的是幸的是,这是一个非常低这是一个非常低效的方法效的方法. 实际上实际上,ORACLE在解析的过在解析的过程中程中, 会将会将* 依次转依次转换成所有的列名换成所有的列名, 这个工作是通过查询这个工作是通过查询数据字典完成的数据字典完成的, 这意这意味着将耗费更多的时间；味着将耗费更多的时间；只提取你所要使用的列；只提取你所要使用的列；使用别

24、名能够加快解析速度；使用别名能够加快解析速度；避免使用耗费资源的操作避免使用耗费资源的操作带有带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的的SQL语句会启动语句会启动SQL引擎执行耗费资源的排序引擎执行耗费资源的排序(SORT)功能功能. DISTINCT需要一次排序操作需要一次排序操作, 而其他的至少需要执行两次而其他的至少需要执行两次排序排序.例如例如,一个一个UNION查询查询,其中每个查询都带有其中每个查询都带有GROUP BY子句子句, GROUP BY会触发嵌入排序会触发嵌入排序(NESTED SORT) ; 这样这样, 每个每个查询需要执行

25、一次排序查询需要执行一次排序, 然后在执行然后在执行UNION时时, 又一个唯一又一个唯一排序排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入操作被执行而且它只能在前面的嵌入排序结束后才能开始执行排序结束后才能开始执行. 嵌入的排序的深度会大大影响查嵌入的排序的深度会大大影响查询的效率询的效率.通常通常, 带有带有UNION, MINUS , INTERSECT的的SQL语句都可以语句都可以用其他方式重写用其他方式重写.例如例如:低效低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO =

26、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);用用EXISTS替换替换DISTINCT用用UNION-ALL 替换替换UNION ( if possible)当当SQL语句需要语句需要UNION两个查询结果集合时两个查询结果集合时,这两个结果集合会以这两个结果集合会以UNION-ALL的方式被合并的方式被合并, 然后在输出最终结果前进行排序然后在输出最终结果前进行排序.举例举例: 低效：低效： SELECT A

27、CCT_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_TRAN

28、SACTIONS WHERE TRAN_DATE = 31-DEC-952. 给优化器更明确的命令给优化器更明确的命令自动选择索引自动选择索引如果表中有两个以上（包括两个）索引，其中有一个唯一性如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性索引，而其他是非唯一性在这种情况下，在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯将使用唯一性索引而完全忽略非唯一性索引一性索引举例举例:SELECT ENAMEFROM EMPWHERE EMPNO = 2326 AND DEPTNO = 20 ;这里，只有这里，只有EMPNO上的索引是唯一性的，所以上的索引是唯一性的

29、，所以EMPNO索索引将用来检索记录引将用来检索记录TABLE ACCESS BY ROWID ON EMP INDEX UNIQUE SCAN ON EMP_NO_IDX至少要包含组合索引的第一列至少要包含组合索引的第一列如果索引是建立在多个列上如果索引是建立在多个列上, 只有在它的第一个列只有在它的第一个列(leading column)被被where子句引用时子句引用时,优化器才会选择使用该索引优化器才会选择使用该索引. SQL create table multiindexusage ( inda number , indb number , descr varchar2(10);Ta

30、ble created.SQL create index multindex on multiindexusage(inda,indb);Index created.SQL set autotrace traceonlySQL select * from multiindexusage where inda = 1;Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF MULTIINDEXUSAGE 2 1 INDEX (RANGE SCAN) OF MULTINDEX

31、(NON-UNIQUE)SQL select * from multiindexusage where indb = 1;Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF MULTIINDEXUSAGE 很明显很明显, 当仅引用索引的第二个列时当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引优化器使用了全表扫描而忽略了索引避免在索引列上使用函数避免在索引列上使用函数WHERE子句中，如果索引列是函数的一部分优化器将不子句中，如果索引列是函数的一部分优化器将不使用索引而使用全表扫

32、描使用索引而使用全表扫描举例举例:低效：低效：SELECT FROM DEPTWHERE SAL * 12 25000;高效高效:SELECT FROM DEPTWHERE SAL 25000/12;避免使用前置通配符避免使用前置通配符WHERE子句中子句中, 如果索引列所对应的值的第一个字符由通如果索引列所对应的值的第一个字符由通配符配符(WILDCARD)开始开始, 索引将不被采用索引将不被采用. SELECT USER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERE USER_NO LIKE %109204421; 在这种情况下，在这种情况下，ORACL

33、E将使用全表扫描将使用全表扫描.避免在索引列上使用避免在索引列上使用NOT通常，我们要避免在索引列上使用通常，我们要避免在索引列上使用NOT, NOT会产生在和在会产生在和在索引列上使用函数相同的影响索引列上使用函数相同的影响. 当当ORACLE”遇到遇到”NOT,他他就就会停止使用索引转而执行全表扫描会停止使用索引转而执行全表扫描.举例举例: 低效低效: (这里这里,不使用索引不使用索引) SELECT FROM DEPT WHERE DEPT_CODE NOT = 0; 高效高效: (这里这里,使用了索引使用了索引) SELECT FROM DEPT WHERE DEPT_CODE 0;避

34、免在索引列上使用避免在索引列上使用 IS NULL和和IS NOT NULL避免在索引中使用任何可以为空的列，避免在索引中使用任何可以为空的列，ORACLE将无法使用该将无法使用该索引索引 对于单列索引，如果列包含空值，索引中将不存在此记对于单列索引，如果列包含空值，索引中将不存在此记录录. 对于复合索引，如果每个列都为空，索引中同样不存在此对于复合索引，如果每个列都为空，索引中同样不存在此记录记录.如果至少有一个列不为空，则记录存在于索引中如果至少有一个列不为空，则记录存在于索引中如果唯一性索引建立在表的如果唯一性索引建立在表的A列和列和B列上列上, 并且表中存在一条记并且表中存在一条记录的

35、录的A,B值为值为(123,null) , ORACLE将不接受下一条具有相同将不接受下一条具有相同A,B值（值（123,null）的记录）的记录(插入插入). 然而如果所有的索引列都为然而如果所有的索引列都为空，空，ORACLE将认为整个键值为空而空不等于空将认为整个键值为空而空不等于空. 因此你可以因此你可以插入插入1000条具有相同键值的记录条具有相同键值的记录,当然它们都是空当然它们都是空!因为空值不存在于索引列中因为空值不存在于索引列中,所以所以WHERE子句中对索引列进行子句中对索引列进行空值比较将使空值比较将使ORACLE停用该索引停用该索引.任何在任何在where子句中使用子句

36、中使用is null或或is not null的语句优化器是的语句优化器是不允许使用索引的。不允许使用索引的。避免出现索引列自动转换避免出现索引列自动转换当比较不同数据类型的数据时当比较不同数据类型的数据时, ORACLE自动对列进行简单自动对列进行简单的类型转换的类型转换.假设假设EMP_TYPE是一个字符类型的索引列是一个字符类型的索引列.SELECT USER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERE USER_NO = 109204421这个语句被这个语句被ORACLE转换为转换为:SELECT USER_NO,USER_NAME,ADDRESS

37、FROM USER_FILESWHERE TO_NUMBER(USER_NO) = 109204421 因为内部发生的类型转换因为内部发生的类型转换, 这个索引将不会被用到这个索引将不会被用到! 在查询时尽量少用格式转换在查询时尽量少用格式转换n如用如用 WHERE a.order_no = b.order_no n不用不用 WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, .) - 1) = TO_NUMBER (substr(a.order_no, instr(b.order_no, .) - 1)3.减少访问次数减少访问次数减少访

38、问数据库的次数减少访问数据库的次数当执行每条当执行每条SQL语句时语句时, ORACLE在内在内部执行了许多工作部执行了许多工作: 解析解析SQL语句语句, 估算索引的利用率估算索引的利用率, 绑定绑定变量变量 , 读数据块等等读数据块等等. 由此可见由此可见, 减少访问数据库的次数减少访问数据库的次数 , 就能就能实际上减少实际上减少ORACLE的工作量的工作量.类比，工程实施类比，工程实施使用使用DECODE来减少处理时间来减少处理时间例如例如: SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0020 AND ENAME LIKESMIT

39、H%; SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0030 AND ENAME LIKESMITH%;你可以用你可以用DECODE函数高效地得到相同结果函数高效地得到相同结果SELECT COUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,X,NULL) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030

40、_SALFROM EMP WHERE ENAME LIKE SMITH%;减少对表的查询减少对表的查询在含有子查询的在含有子查询的SQL语句中语句中,要特别注意减少对表的查询要特别注意减少对表的查询.例如例如: 低效低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) ANDDB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) 高效高效 SELECT TAB_NAME FR

41、OM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604) 4. 细节上的影响细节上的影响WHERE子句中的连接顺序子句中的连接顺序ORACLE采用自下而上的顺序解析采用自下而上的顺序解析WHERE子句子句,根据这个原根据这个原理理, 当在当在WHERE子句中有多个表联接时，子句中有多个表联接时，WHERE子句中排子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在句应放在WHERE子句中

42、的最后。子句中的最后。n如：设从如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：子句中的最后：select * from emp e,dept d where d.deptno 10 and e.deptno =30 ; n如果如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。语句响应快得多。select * from emp e,dept d wh

43、ere e.deptno =30 and d.deptno 10 ;WHERE子句子句 函数、表达式使用函数、表达式使用n最好不要在最好不要在WHERE子句中使用函或表子句中使用函或表达式，如果要使用的话，最好统一使达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以用相同的表达式或函数，这样便于以后使用合理的索引。后使用合理的索引。Order by语句语句 nORDER BY语句决定了语句决定了Oracle如何将返回的查询如何将返回的查询结果排序。结果排序。Order by语句对要排序的列没有什么语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或特别的限制，也可以

44、将函数加入列中（象联接或者附加等）。者附加等）。任何在任何在Order by语句的非索引项或语句的非索引项或者有计算表达式都将降低查询速度。者有计算表达式都将降低查询速度。n仔细检查仔细检查order by语句以找出非索引项或者表达语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是式，它们会降低性能。解决这个问题的办法就是重写重写order by语句以使用索引，也可以为所使用语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。子句中使用表达式。联接列联接列 对于有联接的列，即使最后的联接值

45、为一个静态值，优化器对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。是不会使用索引的。 select * from employss where first_name|last_name =Beill Cliton; 系统优化器对基于系统优化器对基于last_name创建的索引没有使用。创建的索引没有使用。 当采用下面这种当采用下面这种SQL语句的编写，语句的编写，Oracle系统就可以采用基系统就可以采用基于于last_name创建的索引。创建的索引。 select * from employee where first_name =Beill and last_nam

46、e =Cliton; 带通配符（带通配符（%）的）的like语句语句 通配符（通配符（%）在搜寻词首出现，）在搜寻词首出现，Oracle系统不使用系统不使用last_name的索引。的索引。select * from employee where last_name like %cliton%; 在很多情况下可能无法避免这种情况，但是一定要心中有底在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索符串其他位置时，优化器就能利用索引。在下面

47、的查询中索引得到了使用：引得到了使用： select * from employee where last_name like c%; 用用Where子句替换子句替换HAVING子句子句避免使用避免使用HAVING子句子句, HAVING 只会在检索出所有记录之后才对结果只会在检索出所有记录之后才对结果集进行过滤集进行过滤. 这个处理需要排序这个处理需要排序,总计等操作总计等操作. 如果能通过如果能通过WHERE子句限子句限制记录的数目制记录的数目,那就能减少这方面的开销那就能减少这方面的开销.例如例如: 低效低效: SELECT REGION，AVG(LOG_SIZE) FROM LOCAT

48、ION GROUP BY REGION HAVING REGION REGION != SYDNEY AND REGION != PERTH 高效高效 SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != SYDNEY AND REGION != PERTH GROUP BY REGION 顺序顺序 WHERE GROUP HAVING用用NOT EXISTS 替代替代 NOT IN在子查询中在子查询中,NOT IN子句将执行一个内部的排序和合并子句将执行一个内部的排序和合并. 无论在哪种情况无论在哪种情况下下,NOT

49、 IN都是最低效的都是最低效的 (因为它对子查询中的表执行了一个全表遍历因为它对子查询中的表执行了一个全表遍历). 使用使用NOT EXISTS 子句可以有效地利用索引。尽可能使用子句可以有效地利用索引。尽可能使用NOT EXISTS来代替来代替NOT IN，尽管二者都使用了，尽管二者都使用了NOT（不能使用索引而降低速度），（不能使用索引而降低速度），NOT EXISTS要比要比NOT IN查询效率更高。查询效率更高。例如例如:语句语句1 SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp);

50、语句语句2 SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno); 2 2要比要比1 1的执行性能好很多。的执行性能好很多。因为因为1 1中对中对empemp进行了进行了full table scan,full table scan,这是很浪费时间的操作。而且这是很浪费时间的操作。而且1 1中中没有用到没有用到empemp的的indexindex， 因为没有因为没有wherewhere子句。而子句。而2 2中的语句对中的语句对empemp进行

51、的是进行的是缩小范围的查询。缩小范围的查询。用索引提高效率用索引提高效率索引是表的一个概念部分索引是表的一个概念部分,用来提高检索数据的效率，用来提高检索数据的效率，ORACLE使使用了一个复杂的自平衡用了一个复杂的自平衡B-tree结构结构. 通常通常,通过索引查询数据比全表通过索引查询数据比全表扫描要快扫描要快. 当当ORACLE找出执行查询和找出执行查询和Update语句的最佳路径时语句的最佳路径时, ORACLE优化器将使用索引优化器将使用索引. 同样在联结多个表时使用索引也可以同样在联结多个表时使用索引也可以提高效率提高效率. 另一个使用索引的好处是另一个使用索引的好处是,它提供了主

52、键它提供了主键(primary key)的唯一性验证。的唯一性验证。通常通常, 在大型表中使用索引特别有效在大型表中使用索引特别有效. 当然当然,你也会发现你也会发现, 在扫描小在扫描小表时表时,使用索引同样能提高效率使用索引同样能提高效率. 虽然使用索引能得到查询效率的提虽然使用索引能得到查询效率的提高高,但是我们也必须注意到它的代价但是我们也必须注意到它的代价. 索引需要空间来存储索引需要空间来存储,也需要也需要定期维护定期维护, 每当有记录在表中增减或索引列被修改时每当有记录在表中增减或索引列被修改时, 索引本身也索引本身也会被修改会被修改. 这意味着每条记录的这意味着每条记录的INSE

53、RT , DELETE , UPDATE将为将为此多付出此多付出4 , 5 次的磁盘次的磁盘I/O . 因为索引需要额外的存储空间和处理因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引。定期的重构索引是有必要的。是有必要的。避免在索引列上使用计算避免在索引列上使用计算WHERE子句中，如果索引列是函数的一部分优子句中，如果索引列是函数的一部分优化器将不化器将不使用索引而使用全表扫描使用索引而使用全表扫描 低效：低效： SELECT FROM DEPT WHERE SAL * 12 25000; 高效高效: SE

54、LECT FROM DEPT WHERE SAL 25000/12;用用= 替代替代 如果如果DEPTNO上有一个索引。上有一个索引。 高效高效: SELECT * FROM EMP WHERE DEPTNO =4 低效低效: SELECT * FROM EMP WHERE DEPTNO 3通过使用通过使用=、=等，避免使用等，避免使用NOT命令命令n例子：例子：select * from employee where salary 3000; n对这个查询，可以改写为不使用对这个查询，可以改写为不使用NOT：select * from employee where salary3000; 虽

55、然这两种查询的结果一样，但是第二种查询方案会比第虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许一种查询方案更快些。第二种查询允许Oracle对对salary列列使用索引，而第一种查询则不能使用索引。使用索引，而第一种查询则不能使用索引。如果有其它办法，不要使用子查如果有其它办法，不要使用子查询。询。外部联接外部联接+的用法的用法 n外部联接外部联接+按其在按其在=的左边或右边分左联接和右联接。若不带的左边或右边分左联接和右联接。若不带+运算符的表中的一个行不直接匹配于带运算符的表中的一个行不直接匹配于带+预算符的表中的任何行，预算符的表中的任何行，则前者的

56、行与后者中的一个空行相匹配并被返回。利用外部联接则前者的行与后者中的一个空行相匹配并被返回。利用外部联接+，可以替代效率十分低下的可以替代效率十分低下的 not in not in 运算，大大提高运行速度。例如，运算，大大提高运行速度。例如，下面这条命令执行起来很慢：下面这条命令执行起来很慢：select a.empno from emp a where a.empno not in(select empno from emp1 where job=SALE);n利用外部联接，改写命令如下利用外部联接，改写命令如下: : select a.empno from emp a ,emp1 b wh

57、ere a.empno=b.empno(+) and b.empno is null and b.job=SALE;这样运行速度明显提高这样运行速度明显提高. .尽量多使用尽量多使用COMMIT 事务是消耗资源的，大事务还容易引起事务是消耗资源的，大事务还容易引起死锁死锁 COMMIT所释放的资源所释放的资源: 回滚段上用于恢复数据的信息回滚段上用于恢复数据的信息. 被程序语句获得的锁被程序语句获得的锁 redo log buffer 中的空间中的空间 ORACLE为管理上述为管理上述3种资源中的内部种资源中的内部花费花费用用TRUNCATE替代替代DELETE当删除表中的记录时当删除表中的记

58、录时,在通常情况下在通常情况下, 回滚段回滚段(rollback segments ) 用来存放可以被恢复的信息用来存放可以被恢复的信息. 如果你没如果你没有有COMMIT事务事务,ORACLE会将数据恢复到删除之前的会将数据恢复到删除之前的状态状态(准准确地说是恢复到执行删除命令之前的状况确地说是恢复到执行删除命令之前的状况)而当运用而当运用TRUNCATE时时, 回滚段不再存放任何可回滚段不再存放任何可被恢复的被恢复的信息信息.当命令运行后当命令运行后,数据不能被恢复数据不能被恢复.因此很少的资因此很少的资源被调用源被调用,执行时间也会很短执行时间也会很短.计算记录条数计算记录条数和一般的

59、观点相反和一般的观点相反, count(*) 比比count(1)稍快稍快 , 当然如果可当然如果可以通过索引检索以通过索引检索,对索引列的计数仍旧是对索引列的计数仍旧是最快的最快的. 例如例如 COUNT(EMPNO)比如有的表比如有的表PHONE_NO字段是字段是CHAR型型,而且创建有索引，而且创建有索引，但在但在WHERE条件中忘记了加引号，就条件中忘记了加引号，就不会用到索引。不会用到索引。WHERE PHONE_NOHERE PHONE_NO符型字段的引号字符型字段的引号优化优化EXPORT和和IMPORT使用较大的使用较大的BU

60、FFER(比如比如10MB , 10,240,000)可以提高可以提高EXPORT和和IMPORT的速度的速度;ORACLE将尽可能地获取你所指定的内将尽可能地获取你所指定的内存大小存大小,即使在内存即使在内存不满足不满足,也不会报错也不会报错.这个值至少要和表这个值至少要和表中最大的列相当中最大的列相当,否则否则列值会被截断列值会被截断;* 优化优化 Tools *SQL 语句的执行步骤语句的执行步骤语法分析语法分析 ，分析语句的语法是否符合规范，衡量语句中各表达式的，分析语句的语法是否符合规范，衡量语句中各表达式的意义。意义。 语义分析语义分析 ，检查语句中涉及的所有数据库对象是否存在，且