基於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的查詢不走索引的情況;
索引查詢的選擇度比較高,能夠確保過濾掉絕大部分的資料。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29067253/viewspace-2141083/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 基於UNION ALL的分頁查詢執行計劃問題
- 分頁查詢的排序問題(二)排序
- 查詢集合操作union與union all
- 分頁查詢的排序問題排序
- 批次分頁查詢問題?
- explain 查詢執行計劃AI
- MYSQL學習筆記24: 多表查詢(聯合查詢,Union, Union All)MySql筆記
- Java中關於二分查詢的問題Java
- mysql 基於規則的執行計劃(二)MySql
- 關於分頁查詢結果的快取問題快取
- 請教批次分頁查詢的問題
- 執行計劃-6:推入子查詢
- hyperf 使用模型寫 union 子查詢並做分頁模型
- 使用物化檢視查詢重寫 優化對於 UNION ALL檢視的CONNECT BY查詢優化
- 二分查詢基礎專題——二分模板
- Oracle訪問索引的執行計劃(二)Oracle索引
- MySQL中in(獨立子查詢)的執行計劃MySql
- 關於批次分頁查詢
- mysql 基於規則的執行計劃MySql
- 使用AspNetPager進行分頁,查詢條件丟失問題
- mysql求交集:UNION ALL合併查詢,inner join內連線查詢,IN/EXISTS子查詢MySql
- oracle 查詢計劃中的基數cardinality概念(二)Oracle
- Oracle分頁查詢語句(二)Oracle
- 透過查詢檢視sql執行計劃SQL
- 透過union all把sql查詢的結果拼接起來SQL
- mysql的執行計劃快取問題MySql快取
- 關於二分查詢及其上下界問題的一些思考
- 在MySQL中使用explain查詢SQL的執行計劃MySqlAI
- oracle查詢語句執行計劃中的表消除Oracle
- 批次查詢的翻頁問題
- 分頁及查詢引數傳遞問題分享
- MySQL中使用or、in與union all在查詢命令下的效率對比MySql
- 關於分頁查詢的優化思路優化
- 關於 groupBy 分組查詢的分頁處理
- 基於MSSQLSQL資料庫大批次資料的分塊分頁查詢SQL資料庫
- union all和union的區別
- union和union all的區別
- Union與Union All的區別