基於UNION ALL的分頁查詢執行計劃問題
今天又發現9204上的一個問題。不過這個問題並不會造成資料的錯誤，但是會嚴重的影響查詢的效能。
還是透過一個簡單的例子來展現問題：
SQL> CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2(30), CREATE_DATE DATE);
表已建立。
SQL> CREATE INDEX IND_T1_CREATE_DATE ON T1(CREATE_DATE);
索引已建立。
SQL> CREATE TABLE T2 (ID NUMBER, NAME VARCHAR2(30), CREATE_DATE DATE);
表已建立。
SQL> CREATE INDEX IND_T2_CREATE_DATE ON T2(CREATE_DATE);
索引已建立。
SQL> CREATE VIEW V_T AS SELECT ID, NAME, CREATE_DATE FROM T1 UNION ALL SELECT ID, NAME, CREATE_DATE
FROM T2;
檢視已建立。
SQL> INSERT INTO T1 SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS WHERE OWNER = 'SYS';
已建立13727行。
SQL> INSERT INTO T2 SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS WHERE OWNER = 'NDMAIN';
已建立1158行。
SQL> COMMIT;
提交完成。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1');
PL/SQL 過程已成功完成。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2');
PL/SQL 過程已成功完成。
下面看一個簡單的基於V_T檢視的查詢：
SQL> SELECT /*+ FIRST_ROWS */ * FROM V_T WHERE CREATE_DATE = SYSDATE - 2;
未選定行
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=4 Card=21 Bytes=777)
1 0 VIEW OF 'V_T' (Cost=4 Card=21 Bytes=777)
2 1 UNION-ALL (PARTITION)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=4 Card=35 Bytes=1225)
4 3 INDEX (RANGE SCAN) OF 'IND_T1_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=35)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=3 Card=2 Bytes=62)
6 5 INDEX (RANGE SCAN) OF 'IND_T2_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=2)
這個執行計劃沒有問題，但是如果加上分頁，那麼執行計劃將變成：
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)
雖然仍然包含了FIRST_ROWS提示，Oracle這裡並沒有選擇索引。下面嘗試使用INDEX提示強制查詢使用索引：
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT /*+ INDEX(V_T.T1) INDEX(V_T.T2) */ * 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=CHOOSE (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)
提示仍然無效，嘗試對CREATE_DATE新增NOT NULL約束：
SQL> ALTER TABLE T1 MODIFY CREATE_DATE NOT NULL;
表已更改。
SQL> ALTER TABLE T2 MODIFY CREATE_DATE NOT NULL;
表已更改。
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT /*+ INDEX(V_T.T1) INDEX(V_T.T2) */ * 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=CHOOSE (Cost=1652 Card=10 Bytes=520)
1 0 VIEW (Cost=1652 Card=10 Bytes=520)
2 1 COUNT (STOPKEY)
3 2 VIEW OF 'V_T' (Cost=1652 Card=14885 Bytes=580515)
4 3 UNION-ALL
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=826 Card=13727 Bytes=480445)
6 5 INDEX (FULL SCAN) OF 'IND_T1_CREATE_DATE' (NON-UNIQUE) (Cost=26 Card=13727)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=826 Card=1158 Bytes=35898)
8 7 INDEX (FULL SCAN) OF 'IND_T2_CREATE_DATE' (NON-UNIQUE) (Cost=26 Card=1158)
這次雖然使用了索引，但是並非是開始的索引範圍掃描，而變成了索引全掃描。其實從新增NOT NULL約束後執行計劃的變化也可以看出，Oracle這裡的考慮以及和前面發生了很大的變化。
如果將這個查詢的最外層去掉，那麼查詢又會恢復正常了：
SQL> SELECT ROWNUM, A.*
2 FROM
3 (
4 SELECT /*+ FIRST_ROWS */ * FROM V_T
5 WHERE CREATE_DATE = SYSDATE - 2
6 ) A
7 WHERE ROWNUM <= 10
8 ;
未選定行
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=4 Card=10 Bytes=370)
1 0 COUNT (STOPKEY)
2 1 VIEW OF 'V_T' (Cost=4 Card=21 Bytes=777)
3 2 UNION-ALL (PARTITION)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=4 Card=35 Bytes=1225)
5 4 INDEX (RANGE SCAN) OF 'IND_T1_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=35)
6 3 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=3 Card=2 Bytes=62)
7 6 INDEX (RANGE SCAN) OF 'IND_T2_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=2)
如果保留三層查詢結構，去掉ROWNUM：
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM, A.*
5 FROM
6 (
7 SELECT /*+ FIRST_ROWS */ * FROM V_T
8 WHERE CREATE_DATE = SYSDATE - 2
9 ) A
10 )
11 ;
未選定行
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=11 Card=14885 Bytes=774020)
1 0 VIEW (Cost=11 Card=14885 Bytes=774020)
2 1 COUNT
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 A.*
5 FROM
6 (
7 SELECT /*+ FIRST_ROWS */ * FROM V_T
8 WHERE CREATE_DATE = SYSDATE - 2
9 ) A
10 )
11 ;
未選定行
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=4 Card=21 Bytes=777)
1 0 VIEW OF 'V_T' (Cost=4 Card=21 Bytes=777)
2 1 UNION-ALL (PARTITION)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=4 Card=35 Bytes=1225)
4 3 INDEX (RANGE SCAN) OF 'IND_T1_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=35)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=3 Card=2 Bytes=62)
6 5 INDEX (RANGE SCAN) OF 'IND_T2_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=2)
對比上面兩個查詢不能發現，當查詢包含了ROWNUM後，且在這個查詢外部再巢狀一層，就會導致上面的問題的出現，即查詢條件無法從檢視外推到UNION ALL檢視的基表查詢中。
看來9i在執行計劃上的bug還真是不少，最近已經接連碰到幾個了。
下面簡單測試一下10g的情況：
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2(30), CREATE_DATE DATE);
表已建立。
SQL> CREATE INDEX IND_T1_CREATE_DATE ON T1(CREATE_DATE);
索引已建立。
SQL> CREATE TABLE T2 (ID NUMBER, NAME VARCHAR2(30), CREATE_DATE DATE);
表已建立。
SQL> CREATE INDEX IND_T2_CREATE_DATE ON T2(CREATE_DATE);
索引已建立。
SQL> CREATE VIEW V_T AS SELECT ID, NAME, CREATE_DATE FROM T1 UNION ALL SELECT ID, NAME, CREATE_DATE
FROM T2;
檢視已建立。
SQL> INSERT INTO T1 SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS WHERE OWNER = 'SYS';
已建立22988行。
SQL> INSERT INTO T2 SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS WHERE OWNER = 'NDMAIN';
已建立0行。
SQL> COMMIT;
提交完成。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1');
PL/SQL 過程已成功完成。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2');
PL/SQL 過程已成功完成。
SQL> SET AUTOT ON EXP
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 ;
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 2589469176
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 520 | 1 (0)|
|* 1 | VIEW | | 10 | 520 | 1 (0)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | V_T | 10 | 380 | 1 (0)|
| 4 | UNION-ALL PARTITION | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 34 | 1224 | 1 (0)|
|* 6 | INDEX RANGE SCAN | IND_T1_CREATE_DATE | 34 | | 1 (0)|
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 39 | 1 (0)|
|* 8 | INDEX RANGE SCAN | IND_T2_CREATE_DATE | 1 | | 1 (0)|
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">1)
2 - filter(ROWNUM<=10)
6 - access("CREATE_DATE"=SYSDATE@!-2)
8 - access("CREATE_DATE"=SYSDATE@!-2)
看來這個問題在10g已經解決了。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29067253/viewspace-2141082/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 基於UNION ALL的分頁查詢執行計劃問題（二）
- 查詢集合操作union與union all
- 分頁查詢的排序問題排序
- 批次分頁查詢問題?
- MYSQL學習筆記24: 多表查詢(聯合查詢,Union, Union All)MySql筆記
- explain 查詢執行計劃AI
- 分頁查詢的排序問題（二）排序
- 關於分頁查詢結果的快取問題快取
- 請教批次分頁查詢的問題
- 執行計劃-6：推入子查詢
- 使用物化檢視查詢重寫 優化對於 UNION ALL檢視的CONNECT BY查詢優化
- hyperf 使用模型寫 union 子查詢並做分頁模型
- 關於批次分頁查詢
- MySQL中in（獨立子查詢）的執行計劃MySql
- mysql 基於規則的執行計劃MySql
- mysql求交集：UNION ALL合併查詢，inner join內連線查詢，IN/EXISTS子查詢MySql
- 使用AspNetPager進行分頁，查詢條件丟失問題
- 透過union all把sql查詢的結果拼接起來SQL
- 透過查詢檢視sql執行計劃SQL
- mysql的執行計劃快取問題MySql快取
- Java中關於二分查詢的問題Java
- 在MySQL中使用explain查詢SQL的執行計劃MySqlAI
- oracle查詢語句執行計劃中的表消除Oracle
- 批次查詢的翻頁問題
- 分頁及查詢引數傳遞問題分享
- mysql 基於規則的執行計劃(二)MySql
- MySQL中使用or、in與union all在查詢命令下的效率對比MySql
- 關於分頁查詢的優化思路優化
- 基於MSSQLSQL資料庫大批次資料的分塊分頁查詢SQL資料庫
- 關於 groupBy 分組查詢的分頁處理
- union all和union的區別
- union和union all的區別
- Union與Union All的區別
- union的兩個子查詢是否並行並行
- 執行計劃的偏差導致的效能問題
- MySQL的分頁查詢MySql
- Oracle的分頁查詢Oracle
- Lucene的分頁查詢