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

这篇文章将为大家详细讲解有关UNION ALL的分页查询执行问题有哪些，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

镇雄网站制作公司哪家好，找创新互联公司！从网页设计、网站建设、微信开发、APP开发、成都响应式网站建设公司等网站项目制作，到程序开发，运营维护。创新互联公司于2013年开始到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联公司。

今天又发现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的分页查询执行问题有哪些就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

本文题目：如何解决UNIONALL的分页查询执行问题
文章转载：http://chengdu.cdxwcx.cn/article/pcgpcj.html

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

首页

网站建设

网站制作案例

解决方案

网站设计报价

网站制作动态

关于我们

联系我们

成都网站建设设计将想法与焦点和您一起共享

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

其他资讯

android手机蓝光蓝光机安卓机

Ios12开发者测试证书苹果开发者测试版影响保修吗

css的层叠样式是指什么是css层叠样式表

jquery初始化表单 jq重置表单

css最底部样式 css 底部居中

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

成都网站建设设计 将想法与焦点和您一起共享

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

其他资讯

android手机蓝光 蓝光机 安卓机

Ios12开发者测试证书 苹果开发者测试版影响保修吗

css的层叠样式是指 什么是css层叠样式表

jquery初始化表单 jq重置表单

css最底部样式 css 底部居中

成都网站建设设计将想法与焦点和您一起共享

android手机蓝光蓝光机安卓机

Ios12开发者测试证书苹果开发者测试版影响保修吗

css的层叠样式是指什么是css层叠样式表