ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 基于UNION ALL的分页查询执行计划问题

基于UNION ALL的分页查询执行计划问题

原创 Linux操作系统 作者:yangtingkun 时间:2007-07-03 00:00:00 0 删除 编辑

今天又发现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/4227/viewspace-69335/,如需转载,请注明出处,否则将追究法律责任。

下一篇: EXP-3错误
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10486688