如何解决UNION ALL的分页查询执行问题

这篇文章将为大家详细讲解有关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已经解决了。

关于UNION ALL的分页查询执行问题有哪些就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。