Oracle 简单的SQL调优

今天刚看到一篇简单的SQL调优，自己也试了一下优化。原文如下：

        http://valen.blog.ccidnet.com/blog-htm-do-showone-uid-51502-type-blog-itemid-291732.html

    也做了一个简单的测试并做延伸：

      Oracle数据库中，有个表：COST_LEDG_H（数据大约300多万）

     执行语句：

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--> select   max (INIT_UNIT_COST), max (INIT_UNIT_COST)  from  COST_LEDG_H;

 

    执行计划如下：

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--> |  Id   |  Operation              |  Name                |  Rows   |  Bytes  |  Cost ( % CPU) |  Time      |
-- ------------------------------------------------------------------------------ -- ----------
|     0   |   SELECT  STATEMENT       |                      |       1   |       4   |    1442    ( 5 ) |   00 : 00 : 18   |
|     1   |   SORT AGGREGATE        |                      |       1   |       4   | |            |
|     2   |     INDEX  FAST  FULL  SCAN |  COST_LEDG_H_INDEX1  |   3106K |     11M |    1442    ( 5 ) |   00 : 00 : 18   |

  这里发现索引是：INDEX FAST FULL SCAN ， 后面大约有6千多个IO次数，

  通过下列语句查看索引的IO次数：

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->   select  leaf_blocks 叶块数, blevel 级别, distinct_keys 不同值, clustering_factor 集的势   from   user_indexes   where  index_name = upper ( ' COST_LEDG_H_INDEX1 ' );

  输出结果如下： 

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--> 叶块数                    级别                     不同值                   集的势                    
-- -------------------- ---------------------- ---------------------- --------------
6253                     2                        5418                     1107960     

   这时发现该索引的全部页IO次数也是6千多次。这样以来可以确定索引是全部扫描，从根部一直到叶全部扫描了一遍。效率很低：

  原因：

     我们看看为何Oracle数据库为何要全部扫描索引，我们先来看看索引查询方式，索引只有两种查询：

       1，通过索引方式（向前或后向后查询）     

       2，非索引查找（全部索引搜索）

    这里我们看到这里Oracle采用了第2种查询方式，效率比较低，因为这里是一个最大，和一个最小值，数据库如果从第1种方式查也是必需从头到尾的查询其实也就是第二种方式。这里只是一次索引查询的结果。如果是两次索引查询，就可以大大提高查询性能。

  如改写成如下语句：

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--> select   min (INIT_UNIT_COST)  from  COST_LEDG_H
union  all   select   max (INIT_UNIT_COST)   from  COST_LEDG_H;

或

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--> select  ( select    min (INIT_UNIT_COST)  from  COST_LEDG_H)
,( select   max (INIT_UNIT_COST)   from  COST_LEDG_H) from  dual

 

 扩展：

   这次我在SQL server 2005中查询类似语句，速度很快，看了一下执行计划，SQL server 2005 智能的将一次查询索引分成了两次索引查找。这点SQL server 2005 要比Oracle 10gR2要聪明一些。