| |
| 3 | VIEW | | 70304 | 11M| | 1898 (2)| 00:00:23 |
|* 4 | SORT ORDER BY STOPKEY | | 70304 | 6659K| 17M| 1898 (2)| 00:00:23 |
| 5 | VIEW | TEST_VIEW | 70304 | 6659K| | 329 (5)| 00:00:04 |
| 6 | UNION-ALL PARTITION | | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| A | 1650 | 156K| | 238 (3)| 00:00:03 |
|* 8 | INDEX FULL SCAN | IDX_A | 1650 | | | 199 (4)| 00:00:03 |
| 9 | TABLE ACCESS BY INDEX ROWID| B | 1650 | 156K| | 238 (3)| 00:00:03 |
|* 10 | INDEX FULL SCAN | IDX_B | 1650 | | | 199 (4)| 00:00:03 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=0)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
8 - filter("CREATED">TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"LAST_DDL_TIME" 10 - filter("CREATED">TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"LAST_DDL_TIME"
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
566 consistent gets
9 physical reads
0 redo size
2621 bytes sent via SQL*Net to client
411 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed
逻辑读566，那么这样改写是不是最优化的呢？显然不是，因为索引IDX_A,IDX_B 都是走的index full scan,会扫描整个索引block，原始的SQL这个索引里面有3863K 条数据，性能肯定是很低的。 所以进一步的 改写SQL 如下： www.2cto.com
select * from
(
select t.*,rownum rn from
(
select * from
(select * from
(
select /*+ index_desc(a) */ *
from a
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
) where rownum<=20
union all
select * from
(
select /*+ index_desc(b) */ *
from b
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
) where rownum<=20
) order by created desc
) t where rownum<=20
) where rn>=0
执行计划和逻辑读如下：
SQL> select * from
2 (
3 select t.*,rownum rn from
4 (
5 select * from
6 (select * from
7 (
8 select /*+ index_desc(a) */ *
9 from a
10 where created > to_date('2010-01-01', 'yyyy-mm-dd') or
11 LAST_DDL_TIME < to_date('2007-0101', 'yyyy-mm-dd')
12 order by created desc
13 ) where rownum<=20
14 union all
15 select * from
16 (
17 select /*+ index_desc(b) */ *
18 from b
19 where created > to_date('2010-01-01', 'yyyy-mm-dd') or
20 LAST_DDL_TIME < to_date('2007-0101', 'yyyy-mm-dd')
21 order by created desc
22 ) where rownum<=20
23 ) order by created desc
24 ) t where rownum<=20
25 ) where rn>=0;
已选择20行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 3460309830
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------- |