Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1112047
  • 博文数量: 350
  • 博客积分: 10
  • 博客等级: 民兵
  • 技术积分: 5668
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-23 17:53
文章分类

全部博文(350)

文章存档

2013年(350)

分类: Oracle

2013-04-10 13:34:46

B>. 低相异值的列也可以创建索引

接下来要攻克的这条语句CPU时间和BUFFER GETS和与之前两条比稍嫌逊色,但影响力不可小觑,毕竟执行次数高了一倍,而且其各项参数也只是相比前面的两条稍低,对比其它SQL语句在资源占用方面仍然有数倍甚至数十倍的差距,因此也是我们必须攻克的关口。

    CPU Elapsd

    Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value

    --------------- ------------ -------------- ------ -------- --------- ----------

    216,979,036 3,602 60,238.5 15.4 1878.72 1850.94 52310006

    select e.*, rownum from T_WIKI_EVENT e where (rownum <= :1) and

    NEXT_SEND_TIME < SYSDATE and EVENT_TYPE>=0 order by NEXT_SEND_TIME

该表由于频繁更新,物理读也是非常恐怖的(相比其它语句):

    CPU Elapsd

    Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value

    --------------- ------------ -------------- ------ -------- --------- ----------

    104,960,046 3,602 29,139.4 96.9 1878.72 1850.94 52310006

    select e.*, rownum from T_WIKI_EVENT e where (rownum <= :1) and

    NEXT_SEND_TIME < SYSDATE and EVENT_TYPE>=0 order by NEXT_SEND_TIME


SQL语句看起来还是比较简单的,首先看看执行计划怎么样:

    SQL> explain plan for

    2 select e.*, rownum from T_WIKI_EVENT e where (rownum <= :1) and NEXT_SEND_TIME < SYSDATE and EVENT_TYPE>=0 order by NEXT_SEND_TIME;


    Explained


    SQL> select * from table(dbms_xplan.display);


    PLAN_TABLE_OUTPUT

    --------------------------------------------------------------------------------

    ----------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost |

    ----------------------------------------------------------------------

    | 0 | SELECT STATEMENT | | | | |

    | 1 | SORT ORDER BY | | | | |

    |* 2 | COUNT STOPKEY | | | | |

    |* 3 | TABLE ACCESS FULL | T_WIKI_EVENT | | | |

    ----------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

    2 - filter(ROWNUM<=TO_NUMBER(:Z))

    3 - filter("E"."EVENT_TYPE">=0 AND "E"."NEXT_SEND_TIME"

    Note: rule based optimization


    17 rows selected

全表扫,怪不得要占用这么多资源啊,看起来如果想少读的话,必须从索引上考虑了,从过滤列上来看,我们的索引将在event_type和next_send_time两列上打主意,先来分析一下表吧:

    SQL> select count(0) from t_wiki_event where next_send_time>sysdate;


    COUNT(0)

    ----------

    1561


    SQL> select event_type,count(0) from t_wiki_event group by event_type;


    EVENT_TYPE COUNT(0)

    ---------- ----------

    -1 589566

    1 6

基于这样一个结果,很多人都会下意识的认为event_type这种状态列,属于低相异值,不建议在其中创建索引。应该说,这种说法并没有问题,但是具体情况要具体分析,对于性能调优来说从来没有什么铁则。

考虑到该表大多数查询的过滤条件都是event_type>0,而表中符合这一条件的记录非常之少,因此我感觉将索引建在event_type列上将会有更好的查询性能,而且event_type列非常之小,创建索引的话相比date类型的next_send_time也能节省更多的空间。

创建索引如下:

    SQL> create index ind_t_wiki_event_event_type on t_wiki_event (event_type);


    Index created

再次查看执行计划:

    SQL> explain plan for

    2 select e.*, rownum from T_WIKI_EVENT e where (rownum <= :1) and NEXT_SEND_TIME < SYSDATE and EVENT_TYPE>=0 order by NEXT_SEND_TIME;


    Explained


    SQL> select * from table(dbms_xplan.display);


    PLAN_TABLE_OUTPUT

    --------------------------------------------------------------------------------

    --------------------------------------------------------------------------------

    | Id | Operation | Name | Rows | B

    --------------------------------------------------------------------------------

    | 0 | SELECT STATEMENT | | 1 |

    | 1 | SORT ORDER BY | | 1 |

    |* 2 | COUNT STOPKEY | | |

    |* 3 | TABLE ACCESS BY INDEX ROWID| T_WIKI_EVENT | 1 |

    |* 4 | INDEX RANGE SCAN | IND_T_WIKI_EVENT_EVENT_TYPE | 1 |

    --------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

    2 - filter(ROWNUM<=TO_NUMBER(:Z))

    3 - filter("E"."NEXT_SEND_TIME"

    4 - access("E"."EVENT_TYPE">=0 AND "E"."EVENT_TYPE" IS NOT NULL)

    Note: cpu costing is off


    19 rows selected

这样的执行计划好看了很多,cost极底(虽然cost不代表一切),我们根据当前的具体情况分析,这样走索引方式取数据的效率也是非常高的。


阅读(1180) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~