Oracle 数据库SQL调优PPT课件
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
培训中心版权所有，未经许可不得向外传播，违者必究
规则：合理有效的使用索引提高查询效率
在多数情况下，通过索引提高查询效率是非常有效的办法 索引一定要建的合理，并且被正确的使用
选择合适的索引列规则：
➢ 选择在where子句中常用的查询列做索引字段 ➢ 选择常用来关联表的字段做索引字段 ➢ 对普通的B-TREE索引,应该选择具有选择性高（high selectivity）的字段做索
当对索引列上使用计算时，应把计算操作右移。 若需要在索引列上应用函数，考虑创建基于函数的索引。
培训中心版权所有，未经许可不得向外传播，违者必究
规则：避免在索引列上使用计算或在非基于函数的索引列上 使用函数 常见的未正确使用索引的例子 :
在下面的例子里, ‘!=' 将不使用索引. 记住, 索引只能告诉 你什么存在于表中, 而不能告诉你什么不存在于表中. 不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0; 使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0;
培训中心版权所有，未经许可不得向外传播，违者必究
规则：避免在索引列上使用计算或在非基于函数的索引列上 使用函数
如果一定要对使用函数的列启用索引, ORACLE新的功能: 基于函数的索引(Function-Based Index) 也许是一个较好 的方案. CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建 立基于函数的索引*/ SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*将使用索引*/
描表(索引扫描或全表扫描)以及使用到的索引名称. 通过分析执行计划，可以发现并避免： ➢ 不必要的全表扫描 ➢ 选择性不高的范围搜索 ➢ 错误的连接次序 ➢ 过迟的条件过滤操作
培训中心版权所有，未经许可不得向外传播，违者必究
规则：用EXPLAIN PLAN 分析SQL语句
按照从里到外,从上到下的次序解读分析的结果. 分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读. 如果两个操作处于同一层中,带有最小操作号的将被首先执行. NESTED LOOP是少数不按照上述规则处理的操作, 正确的执行路径
本次培训讲述一些书写高效SQL语句常用规则
培训中心版权所有，未经许可不得向外传播，违者必究
规则：用EXPLAIN PLAN 分析SQL语句
显示oracle的优化器为sql语句选择的执行计划. 可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫
是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最 先处理. 目前许多第三方的工具如TOAD，PL/SQL都提供了极其方便的 EXPLAIN PLAN工具
培训中心版权所有，未经许可不得向外传播，违者必究
规则：用EXPLAIN PLAN 分析SQL语句
举例：SELECT * FROM dept, emp WHERE emp.deptno = dept.deptno
Execution Plan ： 0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 'EMP‘ 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
培训中心版权所有，未经许可不得向外传播，违者必究
规则：避免在索引列上使用计算或在非基于函数的索引列上
使用函数
WHERE子句中，如果索引列是函数的一部分．优化器将 不使用索引而使用全表扫描． 例如: SELECT … FROM tbl WHERE col+10 > 20 --全表扫描， 低效 应改为： SELECT … FROM tbl WHERE col > 10 --使用col列上的 索引，高效
将不被使用。
培训中心版权所有，未经许可不得向外传播，违者必究
规则：合理有效的使用索引提高查询效率
通过索引查询，得到查询结果分成两个步骤，首先扫描索 引找到复合条件的记录的rowid,然后再根据rowid从记录表 中得到结果记录。
当查询返回的记录数很多时，从记录表中读取的数据量也 很大，不如不通过索引，直接全表扫描效率更高。
Oracle 数据库SQL调优
员工培训中心 X X X X年X月X日
培训中心版权所有，未经许可不得向外传播，违者必究
概述
数据库的调优包括多个层面，如：操作系统调优、数据合 理有效的存储、数据库服务器参数调整、应用层调优等
对应用开发层的SQL的调优，是最直接有效的手段，可解 决80％以上的数据库访问效率问题
培训中心版权所有，未经许可不得向外传播，违者必究
规则：避免在索引列上使用计算或在非基于函数的索引列上 使用函数
下面的例子中, ‘||'是字符连接函数. 就象其他函数那样, 停用 了索引. 不使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME||ACCOUNT_TYPE='AMEXA'; 使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_TYPE=' A';
引字段，若字段的不同取值很少，即选择性低，则适合建位映射索引 ➢ 不要在经常被修改的字段上建索引。索引会降低update ,insert ,delete等操作
的效率。
培训中心版权所有，未经许可不得向外传播，违者必究
规则：合理有效的使用索引提高查询效率
对低选择性的字段，可以考虑建位映射索引。 位映射索引在oracle的数据仓库应用中常用。 相比与B_TREE索引，位映射索引效率更高，占用空间更小。 对作为where子句中的函数参数的字段，应该建函数索引，普通索引
规则：合理有效的使用索引提高查询效率
在多数情况下，通过索引提高查询效率是非常有效的办法 索引一定要建的合理，并且被正确的使用
选择合适的索引列规则：
➢ 选择在where子句中常用的查询列做索引字段 ➢ 选择常用来关联表的字段做索引字段 ➢ 对普通的B-TREE索引,应该选择具有选择性高（high selectivity）的字段做索
当对索引列上使用计算时，应把计算操作右移。 若需要在索引列上应用函数，考虑创建基于函数的索引。
培训中心版权所有，未经许可不得向外传播，违者必究
规则：避免在索引列上使用计算或在非基于函数的索引列上 使用函数 常见的未正确使用索引的例子 :
在下面的例子里, ‘!=' 将不使用索引. 记住, 索引只能告诉 你什么存在于表中, 而不能告诉你什么不存在于表中. 不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0; 使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0;
培训中心版权所有，未经许可不得向外传播，违者必究
规则：避免在索引列上使用计算或在非基于函数的索引列上 使用函数
如果一定要对使用函数的列启用索引, ORACLE新的功能: 基于函数的索引(Function-Based Index) 也许是一个较好 的方案. CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建 立基于函数的索引*/ SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*将使用索引*/
描表(索引扫描或全表扫描)以及使用到的索引名称. 通过分析执行计划，可以发现并避免： ➢ 不必要的全表扫描 ➢ 选择性不高的范围搜索 ➢ 错误的连接次序 ➢ 过迟的条件过滤操作
培训中心版权所有，未经许可不得向外传播，违者必究
规则：用EXPLAIN PLAN 分析SQL语句
按照从里到外,从上到下的次序解读分析的结果. 分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读. 如果两个操作处于同一层中,带有最小操作号的将被首先执行. NESTED LOOP是少数不按照上述规则处理的操作, 正确的执行路径
本次培训讲述一些书写高效SQL语句常用规则
培训中心版权所有，未经许可不得向外传播，违者必究
规则：用EXPLAIN PLAN 分析SQL语句
显示oracle的优化器为sql语句选择的执行计划. 可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫
是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最 先处理. 目前许多第三方的工具如TOAD，PL/SQL都提供了极其方便的 EXPLAIN PLAN工具
培训中心版权所有，未经许可不得向外传播，违者必究
规则：用EXPLAIN PLAN 分析SQL语句
举例：SELECT * FROM dept, emp WHERE emp.deptno = dept.deptno
Execution Plan ： 0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 'EMP‘ 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
培训中心版权所有，未经许可不得向外传播，违者必究
规则：避免在索引列上使用计算或在非基于函数的索引列上
使用函数
WHERE子句中，如果索引列是函数的一部分．优化器将 不使用索引而使用全表扫描． 例如: SELECT … FROM tbl WHERE col+10 > 20 --全表扫描， 低效 应改为： SELECT … FROM tbl WHERE col > 10 --使用col列上的 索引，高效
将不被使用。
培训中心版权所有，未经许可不得向外传播，违者必究
规则：合理有效的使用索引提高查询效率
通过索引查询，得到查询结果分成两个步骤，首先扫描索 引找到复合条件的记录的rowid,然后再根据rowid从记录表 中得到结果记录。
当查询返回的记录数很多时，从记录表中读取的数据量也 很大，不如不通过索引，直接全表扫描效率更高。
Oracle 数据库SQL调优
员工培训中心 X X X X年X月X日
培训中心版权所有，未经许可不得向外传播，违者必究
概述
数据库的调优包括多个层面，如：操作系统调优、数据合 理有效的存储、数据库服务器参数调整、应用层调优等
对应用开发层的SQL的调优，是最直接有效的手段，可解 决80％以上的数据库访问效率问题
培训中心版权所有，未经许可不得向外传播，违者必究
规则：避免在索引列上使用计算或在非基于函数的索引列上 使用函数
下面的例子中, ‘||'是字符连接函数. 就象其他函数那样, 停用 了索引. 不使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME||ACCOUNT_TYPE='AMEXA'; 使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_TYPE=' A';
引字段，若字段的不同取值很少，即选择性低，则适合建位映射索引 ➢ 不要在经常被修改的字段上建索引。索引会降低update ,insert ,delete等操作
的效率。
培训中心版权所有，未经许可不得向外传播，违者必究
规则：合理有效的使用索引提高查询效率
对低选择性的字段，可以考虑建位映射索引。 位映射索引在oracle的数据仓库应用中常用。 相比与B_TREE索引，位映射索引效率更高，占用空间更小。 对作为where子句中的函数参数的字段，应该建函数索引，普通索引