ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 缺少GROUP BY表达式可以顺利执行的问题(二)

缺少GROUP BY表达式可以顺利执行的问题(二)

原创 Linux操作系统 作者:yangtingkun 时间:2008-01-16 23:53:08 0 删除 编辑

Oracle9204上执行一个明显语法错误的SQL,却可以得到查询结果。

网友gclizh指出,使用MERGE提示可以在10g中避免错误的发生而得到执行结果。根据这个结果进一步分析问题。

缺少GROUP BY表达式可以顺利执行的问题:http://yangtingkun.itpub.net/post/468/451079

 

 

使用提示MERGE,可以在10g中重现这个问题。说明这个问题的引入是由于Oracle将内层子查询进行MERGE操作,把GROUP BY操作放在了最后。

10G中使用MERGE提示可以重现这个问题:

SQL> CONN YANGTK/YANGTK@YTK102
已连接。
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 AS SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> CREATE TABLE T2 AS SELECT * FROM DBA_USERS;

表已创建。

SQL> SELECT USERNAME, OBJECT_TYPE, CN
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
  3  WHERE USERNAME = OWNER
  4  AND USERNAME LIKE 'SYS%';
FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
             *
2 行出现错误:
ORA-00979:
不是 GROUP BY 表达式


SQL> SET AUTOT ON EXP
SQL> SELECT /*+ MERGE(T) */ USERNAME, OBJECT_TYPE, CN
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE) T, T2
  3  WHERE USERNAME = OWNER
  4  AND USERNAME LIKE 'SYS%';

USERNAME                       OBJECT_TYPE                 CN
------------------------------ ------------------- ----------
SYS                            PACKAGE BODY               497
SYSTEM                         VIEW                        12
SYS                            LIBRARY                    111
SYS                            RULE SET                    11
SYSTEM                         INDEX PARTITION             32
.
.
.
SYS                            SCHEDULE                     1
SYS                            JOB                          4
SYSMAN                         TYPE                       212
SYSMAN                         PROCEDURE                    2

已选择66行。


执行计划
----------------------------------------------------------
Plan hash value: 51733071

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  6813 |   379K|   167   (4)| 00:00:03 |
|   1 |  HASH GROUP BY      |      |  6813 |   379K|   167   (4)| 00:00:03 |
|*  2 |   HASH JOIN         |      |  6813 |   379K|   165   (3)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL| T2   |     3 |    87 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T1   | 22710 |   620K|   162   (3)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("USERNAME"="OWNER")
   3 - filter("USERNAME" LIKE 'SYS%')
   4 - filter("OWNER" LIKE 'SYS%')

Note
-----
   - dynamic sampling used for this statement

从执行计划上看,确实这是造成问题的真正原因。而且在9i中,如果使用NO_MERGE的提示也确实可以避免这个问题:

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 -
Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> CREATE TABLE T2 AS SELECT * FROM DBA_USERS;

表已创建。

SQL> SET AUTOT ON EXP
SQL> SELECT USERNAME, OBJECT_TYPE, CN
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
  3  WHERE USERNAME = OWNER
  4  AND USERNAME LIKE 'SYS%';

USERNAME                       OBJECT_TYPE                CN
------------------------------ ------------------ ----------
SYS                            LOB                        31
SYS                            TYPE                      644
SYS                            VIEW                     2090
SYS                            INDEX                     315
SYS                            QUEUE                       4
SYS                            TABLE                     347
.
.
.
SYSTEM                         PACKAGE BODY                1
SYSTEM                         INDEX PARTITION            24
SYSTEM                         TABLE PARTITION            27

已选择40行。


执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (GROUP BY)
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'T2'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'T1'

 

SQL> SELECT /*+ NO_MERGE(T) */ USERNAME, OBJECT_TYPE, CN
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE) T, T2
  3  WHERE USERNAME = OWNER
  4  AND USERNAME LIKE 'SYS%';
FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE) T, T2
             *
2 行出现错误:
ORA-00979:
不是 GROUP BY 表达式

对于Oracle来说,先执行关联操作,然后对关联的结果执行GROUP BY操作是可以得到正确的答案的,但是对于这种写法显然是有问题的。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-149071/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 表异常增大的bug
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10354541