基於UNION ALL的分頁查詢執行計劃問題（二）

今天又發現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的查詢不走索引的情況；

索引查詢的選擇度比較高，能夠確保過濾掉絕大部分的資料。