PL/SQL优化

附1 PL/SQL优化

1.        Oracle索引概述

索引是表的一个概念部分，用来提高检索数据的效率。实际上，Oracle使用了一个复杂的自平衡B-tree结构。一般情况下，通过索引查询数据比全表扫描要快。当Oracle找出执行查询和Update语句的最佳路径后，Oracle优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。另一个使用索引的好处是，它提供了主键（primary key）的唯一性验证。除了那些LONG或LONG RAW数据类型，可以索引几乎所有的列。通常，在大型表中使用索引特别有效。在扫描小表时，使用索引同样能提高效率。虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的代价。索引需要空间来存储，也需要定期维护，当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT 、DELETE 、UPDATE将为此多付出4 、5 次的磁盘I/O 。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的(alter index <index_name> rebuild <tablespace>)。

Oracle对索引有两种访问模式：1）索引唯一扫描（ INDEX UNIQUE SCAN），索引将通过唯一扫描的方式被访问，获得相对应的ROWID，通过ROWID访问表的方式执行下一步检索。如果被检索返回的列包括在索引列中，ORACLE将不执行后续的处理而是通过ROWID访问表。 2）索引范围查询（INDEX RANGE SCAN），适用于两种情况，a) 基于一个范围的检索；b) 基于非唯一性索引的检索；由于索引范围查询返回一组值，它的效率就要比索引唯一扫描低一些。

2.        索引的优先等级

当SQL语句的执行路径分布在多个表上的多个索引时，Oracle会同时使用多个索引并对它们的记录进行合并，检索出仅符合全部条件的记录。在Oracle在选择执行路径时，唯一性索引等级高于非唯一性索引，只有当WHERE子句中索引列和常量比较才有效。如果索引列和其他表的索引列比较，这种子句在优化器中的等级是非常低的。如果不同表中两个想同等级的索引将被引用，from子句中表的顺序将决定哪个会被先使用。from子句中最后的表的索引将有最高优先级。如果相同表中两个相同等级的索引将被引用，where子句中最先被引用的索引将有最高的优先级。当Oracle无法判断索引的等级高低差别时，将只使用一个索引，就是在where子句中被列在最前面的那个。

3.        Oracle处理顺序

oracle的解析器按照从右到左的顺序处理from子句中的表名，因此from子句中写在最后的表将被最先处理。在from子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当oracle处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表（from子句中最后的那个表）并对记录进行派序，然后扫描第二个表（from子句中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。基础表（Driving Table）是指被最先访问的表（通常以全表扫描的方式被访问）。根据优化器的不同， Sql语句中基础表的选择是不一样的。如果你使用的是cbo，优化器会检查sql语句中的每个表的物理大小，索引的状态，然后选用花费最低的执行路径。如果你用rbo，并且所有的连接条件都有索引对应，在这种情况下，基础表就是from子句中列在最后的那个表。

4.        那些情况下会使索引失效

1)        如果索引列所对应的值的第一个字符由通配符开始，索引将不被采用。例如：where index_column like ‘%007’。

2)        Where子句中，如果索引列是函数的一部分。优化器将使用全表扫描。例如：where function_name(index_column) = value

3)        Where子句中，如果对索引列使用计算。优化器将使用全表扫描。例如：where index_column + 200 > 500

4)        避免在索引中使用任何可以为空的列，Oracle将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。对于复合索引，如果每个列都为空，索引中同样不存在此记录。

5)        当比较不同数据类型的数据时，Oracle自动对列进行简单的类型转换，应当避免索引列的类型。如果存在改变索引列的类型，Oracle将无法使用该索引。例如：where to_number(index_column) = 12345

6)        Where子句中，如果索引列比较使用is null、is not null、!=、<>，Oracle将无法使用该索引。因为索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。例如：index_column is null 、index_column<>0

7)        Where子句中，如果索引列使用字符连接函数(||)，Oracle将无法使用该索引。例如：where index_column||’xxx’ = value

8)        如果在索引中使用Not，Oracle将无法使用该索引。例如：where index_column not = 0

9)        如果一定要对使用函数的列启用索引，可使用基于函数的索引（Function-Based Index）。例如：

create index fidx_emp_upper on emp (upper(ename))； --建立函数索引
　   select * from emp where upper(ename) = ‘scott’；--将使用函数索引

5.        使用Exists

Exists使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。

1)        用exists替换distinct

2)        用exists替代in

3)        用not exists替代not in

6.        关于Or的使用

1)        用UNION替换OR，通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意，以上规则只针对多个索引列有效。如果有column没有被索引，查询效率可能会因为你没有选择OR而降低。

2)        用IN来替换OR。

7.        关于group by

提高group by 语句的效率，可以通过将不需要的记录在group by 之前过滤掉。例如：Group by dptno having dptno>20 用 where dptno>20 group by dptno 替代

8.        order by使用索引

Order by中所有的列必须包含在相同的索引中并保持在索引中的排列顺序，Order by中所有的列必须定义为非空，where子句使用的索引和Order by子句中所使用的索引不能并列。

9.        那些是耗费资源的操作

带有distinct 、union 、union all、minus、intersect、order by的sql语句会启动Sql引擎执行耗费资源的排序（sort）功能。distinct需要一次排序操作，而其他的至少需要执行两次排序。一般来说，带有union、union all、minus、intersect的Sql语句都可以使用其他方式重写，在考虑程序可读性情况下，通过调整SORT_AREA_SIZE的大小，使用union、 minus 、intersect也是可以考虑的。