接下来要攻克的这条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> 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"<SYSDATE@!)
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> 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"<SYSDATE@!)
4 - access("E"."EVENT_TYPE">=0 AND "E"."EVENT_TYPE" IS NOT NULL)
Note: cpu costing is off
19 rows selected
