如何解決UNION ALL的分頁查詢執(zhí)行問題

這篇文章將為大家詳細(xì)講解有關(guān)UNION ALL的分頁查詢執(zhí)行問題有哪些，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

今天又發(fā)現(xiàn)9204上的一個(gè)問題。不過這個(gè)問題并不會造成數(shù)據(jù)的錯(cuò)誤，但是會嚴(yán)重的影響查詢的性能。

還是通過一個(gè)簡單的例子來展現(xiàn)問題：

SQL> CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2(30), CREATE_DATE DATE);

表已創(chuàng)建。

SQL> CREATE INDEX IND_T1_CREATE_DATE ON T1(CREATE_DATE);

索引已創(chuàng)建。

SQL> CREATE TABLE T2 (ID NUMBER, NAME VARCHAR2(30), CREATE_DATE DATE);

表已創(chuàng)建。

SQL> CREATE INDEX IND_T2_CREATE_DATE ON T2(CREATE_DATE);

索引已創(chuàng)建。

SQL> CREATE VIEW V_T AS SELECT ID, NAME, CREATE_DATE FROM T1 UNION ALL SELECT ID, NAME, CREATE_DATE
FROM T2;

視圖已創(chuàng)建。

SQL> INSERT INTO T1 SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS WHERE OWNER = 'SYS';

已創(chuàng)建13727行。

SQL> INSERT INTO T2 SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS WHERE OWNER = 'NDMAIN';

已創(chuàng)建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 過程已成功完成。

下面看一個(gè)簡單的基于V_T視圖的查詢：

SQL> SELECT /*+ FIRST_ROWS */ * FROM V_T WHERE CREATE_DATE = SYSDATE - 2;

未選定行

執(zhí)行計(jì)劃
----------------------------------------------------------
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)

這個(gè)執(zhí)行計(jì)劃沒有問題，但是如果加上分頁，那么執(zhí)行計(jì)劃將變成：

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 ;

未選定行

執(zhí)行計(jì)劃
----------------------------------------------------------
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提示強(qiáng)制查詢使用索引：

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 ;

未選定行

執(zhí)行計(jì)劃
----------------------------------------------------------
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 ;

未選定行

執(zhí)行計(jì)劃
----------------------------------------------------------
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)

這次雖然使用了索引，但是并非是開始的索引范圍掃描，而變成了索引全掃描。其實(shí)從添加NOT NULL約束后執(zhí)行計(jì)劃的變化也可以看出，Oracle這里的考慮以及和前面發(fā)生了很大的變化。

如果將這個(gè)查詢的最外層去掉，那么查詢又會恢復(fù)正常了：

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 ;

未選定行

執(zhí)行計(jì)劃
----------------------------------------------------------
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)

如果保留三層查詢結(jié)構(gòu)，去掉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 ;

未選定行

執(zhí)行計(jì)劃
----------------------------------------------------------
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 ;

未選定行

執(zhí)行計(jì)劃
----------------------------------------------------------
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)

對比上面兩個(gè)查詢不能發(fā)現(xiàn)，當(dāng)查詢包含了ROWNUM后，且在這個(gè)查詢外部再嵌套一層，就會導(dǎo)致上面的問題的出現(xiàn)，即查詢條件無法從視圖外推到UNION ALL視圖的基表查詢中。

看來9i在執(zhí)行計(jì)劃上的bug還真是不少，最近已經(jīng)接連碰到幾個(gè)了。

下面簡單測試一下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);

表已創(chuàng)建。

SQL> CREATE INDEX IND_T1_CREATE_DATE ON T1(CREATE_DATE);

索引已創(chuàng)建。

SQL> CREATE TABLE T2 (ID NUMBER, NAME VARCHAR2(30), CREATE_DATE DATE);

表已創(chuàng)建。

SQL> CREATE INDEX IND_T2_CREATE_DATE ON T2(CREATE_DATE);

索引已創(chuàng)建。

SQL> CREATE VIEW V_T AS SELECT ID, NAME, CREATE_DATE FROM T1 UNION ALL SELECT ID, NAME, CREATE_DATE
FROM T2;

視圖已創(chuàng)建。

SQL> INSERT INTO T1 SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS WHERE OWNER = 'SYS';

已創(chuàng)建22988行。

SQL> INSERT INTO T2 SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS WHERE OWNER = 'NDMAIN';

已創(chuàng)建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 ;

未選定行

執(zhí)行計(jì)劃
----------------------------------------------------------
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)

看來這個(gè)問題在10g已經(jīng)解決了。

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