Oracle之SQL优化

1、东大阿尔派 王赟王赟2007年年4月月 Cost Based Optimizer(简称简称CBO)基于统计信息(成本)的优化 Rule Based Optimizer(简称简称RBO)基于数据字典(规则)的优化，在以后的版本中将不支持(10g及以后) 设置缺省的优化器设置缺省的优化器,可以通过对可以通过对init.ora文件文件中中OPTIMIZER_MODE参数的各种声明参数的各种声明 根据数据字典查询有无可用的索引，如果根据数据字典查询有无可用的索引，如果有则使用，否则不使用有则使用，否则不使用 不同的访问方法有预定好的优先级，选择不同的访问方法有预定好的优先级，选择优先级高的执行方法优先

2、级高的执行方法 需要收集统计信息需要收集统计信息 表有多少行，占用多少数据块表有多少行，占用多少数据块 列有多少个列有多少个Null值、不同值值、不同值 列的最大值和最小值，及值的分布情况列的最大值和最小值，及值的分布情况 索引的层次、结点数、叶结点数，及行的索引的层次、结点数、叶结点数，及行的分布状况分布状况(Cluster) 根据一定算法算出一个成本值，选择成本根据一定算法算出一个成本值，选择成本值最低的执行方法，不一定使用索引。值最低的执行方法，不一定使用索引。访问Table的方式ORACLE 采用两种访问表中记录的方式采用两种访问表中记录的方式:a. 全表扫描全表扫描 全表扫描就是顺序

3、地访问表中每条记录全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入采用一次读入多个数据块多个数据块(database block)的方式优化全表扫描的方式优化全表扫描. b. 通过通过ROWID访问表访问表 你可以采用基于你可以采用基于ROWID的访问方式情况的访问方式情况,提高访问表的效率提高访问表的效率, , ROWID包含了表中记录的物理位置信息包含了表中记录的物理位置信息.ORACLE采用索引采用索引(INDEX)实现了数据和存放数据的物理位置实现了数据和存放数据的物理位置(ROWID)之间的联系之间的联系. 通常索引提通常索引提供了快速访问供了快速访问ROWID的方法的

4、方法,因此那些基于索引列的查询就可以得到因此那些基于索引列的查询就可以得到性能上的提高性能上的提高 索引扫描不一定是最快的索引扫描不一定是最快的(如小表如小表)CBO中经常会选择全表扫描中经常会选择全表扫描(如小表如小表)当当Selective (SELECT MIN(B.ROWID) FROM SPJK_ORDER_PARA B WHERE A.ORDER_ID = B.ORDER_ID AND A.PARA_ID = B.PARA_ID); 在含有子查询的在含有子查询的SQL语句中语句中,要特别注意减要特别注意减少对表的查询少对表的查询.低效低效 SELECT * FROM INTER_S

5、ERVICE_ORDER A WHERE A.SO_NBR IN (SELECT B.SO_NBR FROM TF_LOG B WHERE B.IF_ALLOW = G) AND A.PROD_ID IN (SELECT B.COMM_SERV_SPEC_ID FROM TF_LOG B WHERE B.IF_ALLOW = G)高效高效SELECT * FROM INTER_SERVICE_ORDER A WHERE (A.SO_NBR, A.PROD_ID) IN (SELECT B.SO_NBR, B.COMM_SERV_SPEC_ID FROM TF_LOG B WHERE B.IF_

6、ALLOW = G) 索引是表的一个概念部分索引是表的一个概念部分,用来提高检索数据的用来提高检索数据的效率效率.实际上实际上,ORACLE使用了一个复杂的自平衡使用了一个复杂的自平衡B-tree结构结构. 通常通常,通过索引查询数据比全表扫描要通过索引查询数据比全表扫描要快快. 当当ORACLE找出执行查询和找出执行查询和Update语句的最语句的最佳路径时佳路径时, ORACLE优化器将使用索引优化器将使用索引. 同样在联同样在联结多个表时使用索引也可以提高效率结多个表时使用索引也可以提高效率. 另一个使用另一个使用索引的好处是索引的好处是,它提供了主键它提供了主键(primary key

7、)的唯一的唯一性验证性验证. 除了那些除了那些LONG或或LONG RAW数据类型数据类型, 你可你可以索引几乎所有的列以索引几乎所有的列. 通常通常, 在大型表中使用索引在大型表中使用索引特别有效特别有效. 虽然使用索引能得到查询效率的提高虽然使用索引能得到查询效率的提高,但是我们也但是我们也必须注意到它的代价必须注意到它的代价. 索引需要空间来存储索引需要空间来存储,也需也需要定期维护要定期维护, 每当有记录在表中增减或索引列被每当有记录在表中增减或索引列被修改时修改时, 索引本身也会被修改索引本身也会被修改. 这意味着每条记录这意味着每条记录的的INSERT , DELETE , UPD

8、ATE将为此多付出将为此多付出4 , 5 次的磁盘次的磁盘I/O . 因为索引需要额外的存储空间和因为索引需要额外的存储空间和处理处理,那些不必要的索引反而会使查询反应时间变那些不必要的索引反而会使查询反应时间变慢慢 定期的重构索引是有必要的定期的重构索引是有必要的. ALTER INDEX REBUILD 索引能提高速度的关键就是索引所占的空索引能提高速度的关键就是索引所占的空间要比表小得多间要比表小得多 索引的列不要太多，要选择一些索引的列不要太多，要选择一些selective比比较低的列建较低的列建B-tree索引，选择索引，选择selective高的高的列建列建bitmap索引索引(在

9、更新比较多的表不不要在更新比较多的表不不要建建bitmap索引索引) 将将selective较低的列放在前面较低的列放在前面 在更新不多的表上建索引时，可以考在更新不多的表上建索引时，可以考虑用虑用compress选择，以节约索引的空选择，以节约索引的空间间另外创建分区表另外创建分区表一般记录数在一般记录数在50万以上的可以考虑创万以上的可以考虑创建分区表建分区表基础表的选择 当当SQL语句的执行路径可以使用分布在多个表上语句的执行路径可以使用分布在多个表上的多个索引时的多个索引时, ORACLE会同时使用多个索引并会同时使用多个索引并在运行时对它们的记录进行合并在运行时对它们的记录进行合并,

10、 检索出仅对全部检索出仅对全部索引有效的记录索引有效的记录. 在在ORACLE选择执行路径时选择执行路径时,唯一性索引的等唯一性索引的等级高于非唯一性索引级高于非唯一性索引. 然而这个规则只有当然而这个规则只有当WHERE子句中索引列和常量比较才有效子句中索引列和常量比较才有效.如果索如果索引列和其他表的索引类相比较引列和其他表的索引类相比较. 这种子句在优化器这种子句在优化器中的等级是非常低的中的等级是非常低的. 如果不同表中两个想同等级的索引将被引如果不同表中两个想同等级的索引将被引用用, FROM子句中表的顺序将决定哪个会被子句中表的顺序将决定哪个会被率先使用率先使用. FROM子句中最

11、后的表的索引将子句中最后的表的索引将有最高的优先级有最高的优先级. 如果相同表中两个想同等级的索引将被如果相同表中两个想同等级的索引将被引用引用, WHERE子句中最先被引用的索引将子句中最先被引用的索引将有最高的优先级有最高的优先级. 当当ORACLE无法判断索引的等级高低差别无法判断索引的等级高低差别,优化器优化器将只使用一个索引将只使用一个索引,它就是在它就是在WHERE子句中被列子句中被列在最前面的在最前面的. 举例举例: DEPTNO上有一个非唯一性索引上有一个非唯一性索引,EMP_CAT也有也有一个非唯一性索引一个非唯一性索引. SELECT ENAME FROM EMP WHER

12、E DEPTNO 20 AND EMP_CAT A; 这里这里, ORACLE只用到了只用到了DEPT_NO索引索引. 执行路径如下执行路径如下: TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON DEPT_IDX WHERE子句中，如果索引列是函数的一子句中，如果索引列是函数的一部分优化器将不使用索引而使用全表扫部分优化器将不使用索引而使用全表扫描描 SELECT * FROM SO_ACC_NBR WHERE NXX_NBR|LINE_NBR=87881434 如果表中有两个以上（包括两个）索引，其中有一个唯如果表中有两个以上（包括两个）索引

13、，其中有一个唯一性索引，而其他是非唯一性一性索引，而其他是非唯一性 在这种情况下，在这种情况下，ORACLE将使用唯一性索引而完全忽将使用唯一性索引而完全忽略非唯一性索引略非唯一性索引. 避免在索引中使用任何可以为空的列，避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引将无法使用该索引 对于单列索对于单列索引，如果列包含空值，索引中将不存在此引，如果列包含空值，索引中将不存在此记录记录. 对于复合索引，如果每个列都为空，对于复合索引，如果每个列都为空，索引中同样不存在此记录索引中同样不存在此记录.如果至少有一如果至少有一个列不为空，则记录存在于索引中个列不为空，则记录存在于索引中

14、 . 如果唯一性索引建立在表的如果唯一性索引建立在表的A列和列和B列上列上, 并且表并且表中存在一条记录的中存在一条记录的A,B值为值为(123,null) , ORACLE将不接受下一条具有相同将不接受下一条具有相同A,B值（值（123,null）的记）的记录录(插入插入). 然而如果所有的索引列都为空，然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空将认为整个键值为空而空不等于空. 因因此你可以插入此你可以插入1000条具有相同键值的记录条具有相同键值的记录,当然它当然它们都是空们都是空. 因为空值不存在于索引列中因为空值不存在于索引列中,所以所以WHERE子句中子句

15、中对索引列进行空值比较将使对索引列进行空值比较将使ORACLE停用该索引停用该索引. 如果索引是建立在多个列上如果索引是建立在多个列上, 只有在它的第只有在它的第一个列一个列(leading column)被被where子句引用时子句引用时,优化器才会选择使用该索引优化器才会选择使用该索引. 对于表的访问对于表的访问,可以使用两种可以使用两种Hints:FULL 和和 ROWID FULL hint 告诉告诉ORACLE使用全表扫描的使用全表扫描的方式访问指定表方式访问指定表. select /*+full(so_nbr)*/* from work_item_so where local_ne

16、t_id =290 select /*+index(so_nbr)*/ * from work_item_so where sts =C 在不使用在不使用hint的情况下的情况下, 以上的查询应该也会使用以上的查询应该也会使用索引索引,然而然而,如果该索引的重复值过多而你的优化如果该索引的重复值过多而你的优化器是器是CBO, 优化器就可能忽略索引优化器就可能忽略索引. 在这种情况下在这种情况下, 你可以用你可以用INDEX hint强制强制ORACLE使用该索引使用该索引 . 当比较不同数据类型的数据时当比较不同数据类型的数据时, ORACLE自动对自动对列进行简单的类型转换列进行简单的类型转换 . select * from tf_resource where create_date 20061201 select * from tf_resource where to_number(create_date ) 20061201select * from tf_resource where spjk_id=43210select * from tf_reso