ORACLE 9i資料庫優化案例(3) --- 低相異值的列也可以建立索引

junsansi發表於2010-12-07
Oracle資料庫優化索引

B>. 低相異值的列也可以建立索引

接下來要攻克的這條SQL語句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不代表一切)，我們根據當前的具體情況分析，這樣走索引方式取資料的效率也是非常高的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7607759/viewspace-681169/，如需轉載，請註明出處，否則將追究法律責任。

相關文章