今天又发现9204上的一个问题。不过这个问题并不会造成数据的错误，但是会严重的影响查询的性能。

成都创新互联坚持“要么做到，要么别承诺”的工作理念，服务领域包括：成都网站设计、网站制作、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的辰溪网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！

基于UNION ALL的分页查询执行计划问题：http://yangtingkun.itpub.net/post/468/303221

这篇文章继续讨论这个问题，并给出一个相对简单的解决方法。

首先发现的第一个问题是，这个问题是由UNION ALL引起的，而和视图没有关系。

SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT /*+ FIRST_ROWS */ * FROM V_T
8 WHERE CREATE_DATE = SYSDATE - 2
9 ) A
10 WHERE ROWNUM <= 10
11 )
12 WHERE RN > 1
13 ;

未选定行

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=11 Card=10 Bytes=520)
1 0 VIEW (Cost=11 Card=10 Bytes=520)
2 1 COUNT (STOPKEY)
3 2 VIEW OF 'V_T' (Cost=11 Card=14885 Bytes=580515)
4 3 UNION-ALL
5 4 TABLE ACCESS (FULL) OF 'T1' (Cost=9 Card=13727 Bytes=480445)
6 4 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=1158 Bytes=35898)

SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT /*+ FIRST_ROWS */ * FROM
8 (
9 SELECT * FROM T1
10 UNION ALL
11 SELECT * FROM T2
12 )
13 WHERE CREATE_DATE = SYSDATE - 2
14 ) A
15 WHERE ROWNUM <= 10
16 )
17 WHERE RN > 1
18 ;

未选定行

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=11 Card=10 Bytes=520)
1 0 VIEW (Cost=11 Card=10 Bytes=520)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=11 Card=14885 Bytes=580515)
4 3 UNION-ALL
5 4 TABLE ACCESS (FULL) OF 'T1' (Cost=9 Card=13727 Bytes=480445)
6 4 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=1158 Bytes=35898)

在Metalink上搜索了一下，没有找到这个问题的描述，不过似乎和下面这个问题有几分类似：Note:2281909.8。

Description

Suboptimal plan possible from INLINE non-correlated UNION ALL subquery. When this problem occurs the execution plan indicates that the subquery has been unnested to a view, and a join predicate was pushed into the view.

这个bug中的问题是由于Oracle错误的将连接列的查询条件推入到UNION ALL子查询中，导致性能下降。而当前的问题是Oracle没有把限制条件推入到UNION ALL子查询中去。

对于这个问题的解决，就是避免在ROWNUM出现后，在外层再嵌套一层查询。

当然升级到10g也是一种选择，不过代价比较大。

对于分页操作由于无法避免三层嵌套查询，可以利用MINUS来解决这个问题：

SQL> SELECT ROWNUM, A.*
2 FROM
3 (
4 SELECT /*+ FIRST_ROWS */ * FROM V_T
5 WHERE CREATE_DATE = SYSDATE - 2
6 ORDER BY NAME
7 ) A
8 WHERE ROWNUM <= 20
9 MINUS
10 SELECT ROWNUM, A.*
11 FROM
12 (
13 SELECT /*+ FIRST_ROWS */ * FROM V_T
14 WHERE CREATE_DATE = SYSDATE - 2
15 ORDER BY NAME
16 ) A
17 WHERE ROWNUM <= 10
18 ;

未选定行

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=18 Card=20 Bytes=1170)
1 0 MINUS
2 1 SORT (UNIQUE) (Cost=9 Card=20 Bytes=780)
3 2 COUNT (STOPKEY)
4 3 VIEW (Cost=7 Card=21 Bytes=819)
5 4 SORT (ORDER BY STOPKEY) (Cost=7 Card=21 Bytes=777)
6 5 VIEW OF 'V_T' (Cost=4 Card=21 Bytes=777)
7 6 UNION-ALL (PARTITION)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=4 Card=35 Bytes=1225)
9 8 INDEX (RANGE SCAN) OF 'IND_T1_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=35)
10 7 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=3 Card=2 Bytes=62)
11 10 INDEX (RANGE SCAN) OF 'IND_T2_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=2)
12 1 SORT (UNIQUE) (Cost=9 Card=10 Bytes=390)
13 12 COUNT (STOPKEY)
14 13 VIEW (Cost=7 Card=21 Bytes=819)
15 14 SORT (ORDER BY STOPKEY) (Cost=7 Card=21 Bytes=777)
16 15 VIEW OF 'V_T' (Cost=4 Card=21 Bytes=777)
17 16 UNION-ALL (PARTITION)
18 17 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=4 Card=35 Bytes=1225)
19 18 INDEX (RANGE SCAN) OF 'IND_T1_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=35)
20 17 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=3 Card=2 Bytes=62)
21 20 INDEX (RANGE SCAN) OF 'IND_T2_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=2)

采用这种方式，可以在利用索引的基础上完成翻页的功能，不过这种方法对于结果集靠后的记录可能会导致查询时间成倍增加。

使用这个方法一般只在下面两个条件都成立：

已经碰到了基于UNION ALL的查询不走索引的情况；

索引查询的选择度比较高，能够确保过滤掉绝大部分的数据。

分享文章：基于UNIONALL的分页查询执行计划问题（二）
新闻来源：http://scyanting.com/article/gpijhs.html