O,
EVENT_ID
FROM EMS_EVENT_VIEW
WHERE (first_occurrence_time >
to_date('2012-02-22 00:00:00',
'yyyy-mm-dd hh24:mi:ss') and
first_occurrence_time <
to_date('2012-02-29 09:42:35',
'yyyy-mm-dd hh24:mi:ss'))
))
WHERE ROWNO >= 0
AND ROWNO <= 20) A
WHERE EMS_EVENT_VIEW.EVENT_ID = A.EVENT_ID;
这个SQL其实就是一个分页SQL，利用row_number over 做分页，EMS_EVENT_VIEW是一个视图。这个SQL确实写得很坑爹，它要扫描EMS_EVENT_VIEW两次，其实我们可以改写它，让它扫描一次，而不是自己和自己利用event_id 做自连接。
EMS_EVENT_VIEW的定义就不贴出来了，涉及保密 。它的大概意思就是select * from a union all select * from b; 无where 过滤条件。
因为这个SQL是朋友给我的，我无法连接到他的DB，所以我只有自己做测试了，测试代码如下：
create table a as select * from dba_objects;
create table b as select * from dba_objects;
create view test_view as select * from a
union all select * from b;
create index idx_a on a(created ,last_ddl_time);
create index idx_b on b(created ,last_ddl_time);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'B',
estimate_percent => 100,
method_opt => 'for all columns size auto',
no_invalidate => FALSE,
degree => 4,
cascade => TRUE);
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'B',
estimate_percent => 100,
method_opt => 'for all columns size auto',
no_invalidate => FALSE,
degree => 4,
cascade => TRUE);
END;
/
要优化的SQL可以改写成如下代码，只访问一次视图：
select * from
(
select t.*,rownum rn from
(select /*+ INDEX(TEST_VIEW.A idx_a) INDEX(TEST_VIEW.b idx_b) */ *
from test_view
where created > to_date('2010-01-01', 'yyyy-mm-dd') or
LAST_DDL_TIME < to_date('2007-0101', 'yyyy-mm-dd')
order by created desc
) t where rownum<=20
) where rn>=0;
现在来看它的执行计划
SQL> select * from
2 (
3 select t.*,rownum rn from
4 (select /*+ INDEX(TEST_VIEW.A idx_a) INDEX(TEST_VIEW.b idx_b) */ *
5 from test_view
6 where created > to_date('2010-01-01', 'yyyy-mm-dd') or
7 LAST_DDL_TIME < to_date('2007-0101', 'yyyy-mm-dd')
8 order by created desc
9 ) t where rownum<=20
10 ) where rn>=0;
已选择20行。
已用时间: 00: 00: 00.10
执行计划
----------------------------------------------------------
Plan hash value: 1808710389
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 3800 | | 1898 (2)| 00:00:23 |
|* 1 | VIEW | | 20 | 3800 | | 1898 (2)| 00:00:23 |
|* 2 | COUNT STOPKEY | | | | | |