OracleSQL语句优化技术分析.doc

Oracle SQL语句优化技术分析操作符优化 IN 操作符 用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。 但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别： ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。 推荐方案：在业务密集的SQL当中尽量不采用IN操作符。 NOT IN操作符 此操作是强列推荐不使用的，因为它不能应用表的索引。 推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替 操作符（不等于） 不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 推荐方案：用其它相同功能的操作运算代替，如 a0 改为 a0 or a0 a 改为 a IS NULL 或IS NOT NULL操作（判断字段是否为空） 判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。 推荐方案： 用其它相同功能的操作运算代替，如 a is not null 改为 a0 或a等。 不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。 建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）。 及 2与A=3的效果就有很大的区别了，因为A2时ORACLE会先找出为2的记录索引再进行比较，而A=3时ORACLE则直接找到=3的记录索引。 LIKE操作符 LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE %5400% 这种查询不会引用索引，而LIKE X5400%则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE %5400% 这个条件会产生全表扫描，如果改成YY_BH LIKE X5400% OR YY_BH LIKE B5400% 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。Oracle SQL的优化(1)1、在Oracle中有两种操作可以直接访问Table (1)Table Access Full -为了优化全表扫描的性能，Oracle在每次数据库读取中都会读取多个数据块；-只要查询中没有where子句，Oracle就会采用全表扫描。 (2)Table Access by RowID(基于RowID的访问) -RowID记录了数据行的物理存储位置； -Oracle使用索引将数据值与RowID相关联，从而与数据物理位置相关联。2、提示：在select关键字之后使用/*+. */3、使用Index: (1)Index unique scanselect * from bookshelf where title=WTL假设在title列上有唯一索引，则这个语句的执行方式为： a)首先通过insex unique scan操作访问title列索引； b)从索引返回与title值WTL相匹配的RowID值，然后利用此RowID值通过Table Access by RowID操作来查询BookShelf表。 (2)Index range scan 如果基于一个值的范围查询或者利用一个非唯一索引进行查询，则可以使用index range scan操作对索引进行查询。由于index range scan操作需要从索引中读取多个值，所以它的效率要比index unique scan低。假设emp表的ename上有一个非唯一索引idx_emp_ename，如果在查询的where子句中给出ename的限定条件，则可能会执行idx_emp_ename索引的index range scan操作，应为ename上的这和索引是一个非唯一性索引，所以数据库不能在该索引上执行index unique scan操作，即便是ename等于查询中的单个值也不行，即.where ename=GLEDESON 和 .where ename like G%都是执行index range scan 操作的。 注意：如果在like条件中的开头使用了通配符则查询将不会使用索引(如:like %M%)来解决这个查询。4、使用索引的注意事项: (1)如果设置了一个索引列等(=)一某个值,则将使用index range scan操作 |-唯一索引(unique index) *索引-| |-非唯一索引(non-unique index) (2)要使用一个索引不一定要给出明确的值,index range scan操作可为值的范围扫描一个索引，具 体的操作符有、like,注意：不要在like算式的开头使用通配符！ |-索引的全表扫描 *全表扫描-| |-表的全表扫描 (3)如果在where条件中使用了函数，则不会使用索引，除非索引是基于函数的！ (4)在查询条件中使用了is null、is not null则不会使用索引。(null值是不存在索引中的) *在大多数情况下，执行全表扫描将会比执行用索引返回的所有值执行索引扫描更为有效！ (5)如果在查询条件中使用了!=操作将不会使用索引，也不会。 (6)使用not in、in操作符也不会使用索引，在Oracle中，几乎所有的not in、in操作都可以用not exists、exists来代替。.where exists(select x from .where .) *使用exists子句，不管从子查询中抽取什么数据，它只会查看where子句。这样优化器就不会遍历整个表而仅根据索引就可以完成工作(这里假设where子句中的列上使用了index). 通过使用exists，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这样就节省了时间。Oracle在执行in查询时，首先执行子查询，并将获得的结果列表存放到一个加了索引的临时表中，在执行子查询之前，先将主查询挂起，待子查询执行完毕存放到临时表中以后再执行主查询，这就是使用exists比使用in通常查询速度快的原因。 (7)如果设置了复合索引的首列等于某个值，则使用该索引。 *在复合索引中，Oracle9i之前，只有在限定条件中使用了复合索引的首列，才会使用该索引，但Oracle9i之后，索引的跳跃扫描特性允许优化器潜在地使用连接索引，即使复合索引的首列没有出现在where子句中也是如此！ (8)如果选择了一个索引列的max或min函数，则优化器会使用此索引来快速地找到列的最大最小值。 (9)索引的选择性：假设一个表中有100行，其中的一列(这列上有索引)中不重复的纪录有80个，也就是说有20行纪录有重复，则这个索引的选择性为80/100=0.80，即这列上的索引的选择性是80%，选择性越高，列中每个不同的值返回的行的数目就越少。如果使用的优化模式是CBO，并且索引是经过分析的，则优化程序就会考虑索引的选择性来判断使用索引是否会降低执行查询的成本。Oracle SQL的优化(2)组合多个索引扫描的输出可以使用多个索引(或相同索引的多次扫描)来完成单个查询。多个索引的And-Equal操作如果在一个查询中为多个索引指定了限定条件，则优化器在解决这个查询时或许能够使用多个索引。例如： 在bookshelf表的title列和CategoryName列上有两个索引：select * from bookshelf where titleM and CategoryNameB此查询的where子句包含了两个独立的限定条件，每个限定条件对应一个不同的索引，第一个限定条件对于应主键索引，第二个限定条件对应于bookshelf$category索引，在解决此查询时，优化程序可能同时使用这两个索引，也可能会进行全表扫描，如果使用两个索引，每个索引都将通过Index Range scan操作来扫描，从主键索引扫描返回的RowID将与从bookshelf$category索引返回的RowID进行比较。从两个索引返回的那些RowID将在后面的Table Access by RowID操作中使用：主键索引的Index Range Scan-| bookshelf的|-And-Equal-Table Access bybookshelf$category索引的-| RowID Index Range ScanAnd-Equal操作比较两个索引扫描的结果，一般来说，单个多列索引的访问(在查询的where子句的限定条件中使用了复合索引的首列)将比多个单列索引的And-Equal完成的更好!And-Equal操作相当于：select rowid from bookshelf where titleMinsersectselect rowid from bookshelf where categorynameB它把两个结果集共有部分的RowID拿出来去做bookshelf的Table Access by RowID。Oracle SQL的优化(3)使用Index提示：1、Index提示是最常用的与索引有关的提示，使用Index提示时，虽然可以列出特定的索引，但不一定要提及索引名。例如： select /*+index(bookshelf)*/ title from bookshelf where categoryname=HARRISON因为在title上有索引，此查询将使用索引而无需提示，但是，如果索引是非选择性的或者表比较小且使用了CBO则优化器可能会忽略索引，使用Table Access Full。如果你知道索引对于给定的值来说是选择性的，则可以使用index提示强制使用一个基于索引的数据访问路径,而不是全表扫描! 注意：如果不在index提示中列出特定的索引，且表中有多个可供使用的索引，则优化器将会评估可用的索引并选择其扫描成本最低的索引。优化器还可以选择扫描几个索引并利用And-Equal操作来合并他们。索引的其它优化问题：(1)、一般来说，优化程序扫描单一的复合索引的速度要比扫描多个独立的单个索引的速度要快，扫描所返回的 行数越多越能体现复合索引的性能。(2)、可以使用index提示强迫优化器使用跳跃扫描功能。Oracle SQL的优化(4)处理数据集的操作： 一旦数据从表或索引返回，就可以对其进行处理了，可以对记录分组，分类，计数，锁定或者将查询的结果与其它查询的结果进行合并(union、minus、intersect)。 大多数处理记录集的操作在整个操作完成前不会向用户返回数据! 索引扫描操作和表访问操作在找到记录后立刻将它返回给用户! 在集合操作中，用户必须等待操作处理完成所有的行，才将结果集返回给用户!1、行的分类 有三个Oracle的内部操作在不必对行进行分组的情况下就可以对它们进行分类操作了： (1)、Sort Order by 操作 (2)、Distinct操作(Sort Unique操作) (3)、Sort Join操作,它总是作为merge join 操作的一部分是用，它从不独自使用 2、行的分组 Oracle有两个内部操作在将类似的记录组织在一起的过程中对行进行分类： (1)、Sort aggregate操作 (2)、Sort group by操作它们与分组函数结合在一起使用，例如：select max(salary) from emp 为了完成这个查询，优化器将执行两个独立的操作： 1)、Table access full操作从表中选择salary值； 2)、利用Sort aggregate操作对行进行分析，该操作将向用户返回最大的salary值，这个查询在读出所有记录且完成Sort aggregate操作之前，将不返回最大的salary值。使用group by子句的查询将使用Sort group by操作。 例如：select deptno,count(salary) from emp group by deptno; 为了完成这个查询Oracle将执行一个Table access full操作，此查询没有限定条件，故不会使用索引，因为使用了group by子句，所以，Table access full操作返回的行将由Sort group by操作来处理，一旦所有的行都分成组，且每个组的计数都已计算出来，则将结果返回给用户。注意：如果应用程序不要求在给出查询的输出前对所有的行进行分类，则可以考虑使用fist_rows提示，该提示会告诉优化器尽量不使用进行集合操作的执行路径!Oracle SQL的优化(5)-关于rownum的操作在查询中有时使用到伪列rownum对使用伪列rownum的查询，优化器要么使用count操作，要么使用count stopkey操作来对rownum计数器进行增量(注意：这里的count操作和count stopkey操作与count函数没有任何关系)，如果对rownum伪列应用一个限定条件，如：where rownum10;则使用 count stopkey操作，如果不为Rownum伪列指定限定条件，则是使用count操作。例1：不在Rownum伪列上使用限定条件select id,rownum from employee;(在id列上有一个主键索引) 为了完成这个查询，优化器执行一个全索引扫描(主键索引)，后跟一个count操作生成每个行的rownum值，count操作不需要等待得到整个记录集，随着从employee表中返回记录，rownum计数器进行增量，从而确定每个记录的rownum.例2：在rownum伪列上使用一个限定select id,rownum from employee where rownum10这样的操作，只能使用rownum add_months(d,n)日期d加n个月last_day(d)包含d的月份的最后一天的日期month_between(d,e)日期d与e之间的月份数，e先于dnew_time(d,a,b