Oracle 数据库SQL调优

概述
数据库的调优包括多个层面，如：操作系统调优、数据合理有效的存
储、数据库服务器参数调整、应用层调优等 对应用开发层的SQL的调优，是最直接有效的手段，可解决80％以上 对应用开发层的SQL的调优，是最直接有效的手段，可解决80％以上 的数据库访问效率问题 本次培训主要讲述一些书写高效SQL语句常用规则 本次培训主要讲述一些书写高效SQL语句常用规则
规则：总是使用索引的第一个列 规则：
如果索引是建立在多个列上（复合索引）, 只有在它的第一个列 如果索引是建立在多个列上（复合索引）,
(leading column)被where子句引用时,优化器才会选择使用该索引. column)被where子句引用时,优化器才会选择使用该索引. create table multiindexusage ( inda number , indb number , descr varchar2(10)); create index multindex on multiindexusage(inda,indb); 通过索引扫描： select * from multiindexusage where inda = 1; 全表扫描： select * from multiindexusage where indb = 1;
规则：合理有效的使用索引提高查询效率 规则：
在多数情况下，通过索引提高查询效率是非常有效的办法 索引一定要建的合理，并且被正确的使用 选择合适的索引列规则： 选择在where子句中常用的查询列做索引字段 选择在where子句中常用的查询列做索引字段 选择常用来关联表的字段做索引字段 对普通的B TREE索引,应该选择具有选择性高（high selectivity）的 对普通的B-TREE索引,应该选择具有选择性高（high selectivity）的 字段做索引字段，若字段的不同取值很少，即选择性低，则适合建位 图索引 不要在经常被修改的字段上建索引。索引会降低 update ,insert ,delete等操作的效率。 ,delete等操作的效率。
规则：避免改变索引列的类型 规则：
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换. 当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
内部隐式的类型转变会降低执行效率，更重要的，如果转换列为索引 列，则由于内部转换(相当于对索引列进行了函数运算) 列，则由于内部转换(相当于对索引列进行了函数运算)的原因，该索 引将不被使用。例如： select * from user where user_id =123456 若user_id字段类型为字符串型，则oracle自动把上述语句修改为： user_id字段类型为字符串型，则oracle自动把上述语句修改为： select * from user where to_number(user_id) =123456 修改为： select * from user where user_id =to_char(123456) 由于类型转换也要耗费时间，最好避免，修改为： select * from user where user_id =‘123456’
规则：避免在索引列上使用计算或在非基于函数的 规则： 索引列上使用函数
如果一定要对使用函数的列启用索引, ORACLE新的功能: 基于函数的 如果一定要对使用函数的列启用索引, ORACLE新的功能:
索引(Function索引(Function-Based Index) 是一个较好的方案. 是一个较好的方案. CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建立基于函数的 /*建立基于函数的 索引* 索引*/ SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*将 /*将 使用索引* 使用索引*/ 基于函数的索引要求等式匹配 create index emp_ename_substr on eemp ( substr(ename,1,2) ); --高效 --高效 select * from emp where substr(ename,1,2)=’SM’;(INDEX RANGE SCAN ) --低效 --低效 select * from emp where subst查询效率 规则：
数据库表索引设计原则
表索引原则上在5个内； 单字段上索引原则上不能超过2个； 复合索引原则上一次包含字段不能超过3个； 分区表原则上全部使用本地索引(LOCAL) 注意：在用分析命令对分区索引进行分析时，每一个分区的数据 值的范围信息会放入Oracle的数据字典中。Oracle可以利用这个信息 来提取出那些只与SQL查询相关的数据分区。 例如，假设你已经定义了一个分区索引，并且某个SQL语句需要在一 个索引分区中进行一次索引扫描。Oracle会仅仅访问这个索引分区， 而且会在这个分区上调用一个此索引范围的快速全扫描。因为不需要 访问整个索引，所以提高了查询的速度。
规则：选择最有效率的表名顺序 规则：
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此 ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,
FROM子句中写在最后的表( FROM子句中写在最后的表(基础表 也叫驱动表 driving table)将被 table)将被 最先处理. 最先处理. 在FROM子句中包含多个表的情况下,应该选择记录条数最少的表作为 FROM子句中包含多个表的情况下, 基础表. ORACLE处理多个表时, 会运用排序及合并的方式连接它们. 基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们. 首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序, 首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然 后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表 后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表 中检索出的记录与第一个表中合适记录进行合并. 中检索出的记录与第一个表中合适记录进行合并. 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. table)作为基础表, 交叉表是指那个被其他表所引用的表.
规则：合理有效的使用索引提高查询效率 规则：
通过索引查询，得到查询结果分成两个步骤，首先扫描索引找到符合
条件的记录的rowid,然后再根据rowid从记录表中得到结果记录。 条件的记录的rowid,然后再根据rowid从记录表中得到结果记录。 当查询返回的记录数很多时，从记录表中读取的数据量也很大，不如 不通过索引，直接全表扫描效率更高。( 不通过索引，直接全表扫描效率更高。(一般建议在查询数据量10%以 下使用索引) )
规则：合理有效的使用索引提高查询效率 规则：
B*TREE索引 TREE索引
模拟为二叉树数据结构, 模拟为二叉树数据结构,有两个记录项 rowid (它是行的物理位置) (它是行的物理位置) 正被索引的列值 位图索引(bitmap) 位图索引(bitmap) 以矩阵模式为基础,对于每一行, 以矩阵模式为基础,对于每一行,将位图于它的匹配值相对应 位图索引在oracle的数据仓库应用中常用。 位图索引在oracle的数据仓库应用中常用。 相比与B_TREE索引，位图索引效率更高，占用空间更小。 相比与B_TREE索引，位图索引效率更高，占用空间更小。 对低选择性的字段，可以考虑建位图索引。
规则：避免在索引列上使用计算或在非基于函数的 规则： 索引列上使用函数
常见的未正确使用索引的例子 :
在下面的例子里, 在下面的例子里, ‘!=' 将不使用索引. 记住, 索引只能告诉你什 将不使用索引. 记住, 么存在于表中, 而不能告诉你什么不存在于表中. 么存在于表中, 而不能告诉你什么不存在于表中. 不使用索引: 不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0; 使用索引: 使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0;
规则：避免在索引列上使用NOT与<> NOT与 规则：避免在索引列上使用NOT
NOT会产生和在索引列上使用函数相同的影响. ORACLE”遇 NOT会产生和在索引列上使用函数相同的影响. 当ORACLE”遇 到”NOT,会停止使用索引转而执行全表扫描. 到”NOT,会停止使用索引转而执行全表扫描. 当在索引列上使用<>时，ORACLE也会会停止使用索引转而执行全表扫 当在索引列上使用<>时，ORACLE也会会停止使用索引转而执行全表扫 描.
规则：选择最有效率的表名顺序 规则：
注意：上述表明顺序规则并不是绝对的，不同的优化方式配置
（rule-based or cost-based），可能有不同的结果。此条规则可以 rulecost-based），可能有不同的结果。此条规则可以 这样理解：尝试调整from后各个表的顺序，往往可以优化sql的查询 这样理解：尝试调整from后各个表的顺序，往往可以优化sql的查询 效率，最合理的表名顺序应该根据实际的运行结果并结合执行计划分 析确定
规则：避免在索引列上使用计算或在非基于函数的 规则： 索引列上使用函数
下面的例子中, ‘||'是字符连接函数. 就象其他函数那样, 下面的例子中, ‘||'是字符连接函数. 就象其他函数那样, 停用了索 引. 不使用索引: 不使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME||ACCOUNT_TYPE='AMEXA'; 使用索引: 使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = ‘AMEX' AND ACCOUNT_TYPE=' A';