如何解決UNION ALL的分頁查詢執行問題

發布時間：2021-11-05 16:47:38 來源：億速云閱讀：304 作者：柒染欄目：建站服務器

這篇文章將為大家詳細講解有關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;

表已更改。

未選定行

執行計劃
----------------------------------------------------------
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 ;

未選定行

對比上面兩個查詢不能發現，當查詢包含了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已經解決了。

關于UNION ALL的分頁查詢執行問題有哪些就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

向AI問一下細節

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

如何解決UNION ALL的分頁查詢執行問題

猜你喜歡

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

如何解決UNION ALL的分頁查詢執行問題

猜你喜歡

最新資訊

相關推薦

相關標簽