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要聪明一些。