缺少GROUP BY表示式可以順利執行的問題（二）

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操作是可以得到正確的答案的，但是對於這種寫法顯然是有問題的。