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

基于UNION ALL的分页查询执行计划问题：

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

首先发现的第一个问题是，这个问题是由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的查询不走索引的情况；

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