11g之前和11g SQL执行结果不同

zmyzmy726

首先创建环境：
create table t (x number(10), y number(10));
insert into t values (1,110);
insert into t values (2,120);
insert into t values (2,80);
insert into t values (3,150);
insert into t values (3,30);
insert into t values (3,60);
commit;

查询表：
SQL> select * from t;
         X          Y
---------- ----------
         1        110
         2        120
         2         80
         3        150
         3         30
         3         60

执行如下语句：
SELECT x, SUM (y) AS total_y
FROM t
GROUP BY x
HAVING SUM (y) > (SELECT SUM (y)/3 FROM t)
ORDER BY total_y
/

在oracle11g之前的版本，会得到2行结果，11g的时候则no rows select
查看执行计划的不同：
oracle11g之前
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     6 |   156 |     4  (50)| 00:00:01 |
|   1 |  SORT ORDER BY       |      |     6 |   156 |     4  (50)| 00:00:01 |
|*  2 |   FILTER             |      |       |       |            |          |
|   3 |    HASH GROUP BY     |      |     6 |   156 |     4  (50)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T    |     6 |   156 |     2   (0)| 00:00:01 |
|   5 |    SORT AGGREGATE    |      |     1 |    13 |            |          |
|   6 |     TABLE ACCESS FULL| T    |     6 |    78 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SUM("Y")> (SELECT SUM("Y")/3 FROM "T" "T"))

11g：
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     6 |   234 |     4  (50)| 00:00:01 |
|   1 |  SORT ORDER BY        |          |     6 |   234 |     4  (50)| 00:00:01 |
|*  2 |   VIEW                | VW_WIF_1 |     6 |   234 |     3  (34)| 00:00:01 |
|   3 |    WINDOW BUFFER      |          |     6 |   156 |     3  (34)| 00:00:01 |
|   4 |     HASH GROUP BY     |          |     6 |   156 |     3  (34)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| T        |     6 |   156 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ITEM_1">"ITEM_2"*3)

经验证，11g的时候如果把SELECT SUM (y)/3 FROM t修改成(SELECT  SUM (y)/3 FROM t)/3,或者SELECT TO_NUMBER(SUM(y)/3) FROM t或者改成>=或者<等等情况都会使11g的执行计划也变成2次扫描表，做正确的filter，但是如果把/3写在里面就会导致执行计划使用WINDOW BUFFER,metalink上也没查到资料，请大神指导

26856649

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as sys@192.168.253.248:1521/orcl AS SYSDBA

SQL>
SQL> SELECT x, SUM (y) AS total_y
  2  FROM t
  3  GROUP BY x
  4  HAVING SUM (y) > (SELECT SUM (y)/3 FROM t)
  5  ORDER BY total_y;

          X    TOTAL_Y
----------- ----------
          2        200
          3        240

SQL>

zmyzmy726

可以发下你的执行计划么，我这边测试多个11g的库都用了window buffer导致没有结果，一个朋友的11g库也是如此。

26856649

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 25 16:29:42 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn scott/tiger
Connected.
SELECT t1.x, SUM(t1.y) AS total_y
  FROM t t1
GROUP BY t1.x
HAVING SUM(t1.y) > (SELECT SUM(t2.y) / 3 FROM t t2)
  5  /

no rows selected

SQL>

zmyzmy726

测试11g的版本为11.2.0.2以及11.2.0.3

26856649

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID        aprr3bq7895ad, child number 0
-------------------------------------
SELECT x, SUM (y) AS total_y FROM t GROUP BY x HAVING SUM (y) > (SELECT
SUM (y)/3 FROM t) ORDER BY total_y

Plan hash value: 1651950043

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Starts | E-Rows | A-Rows |        A-Time         | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |        1 |           |          0 |00:00:00.01 |         3 |           |           |              |
|   1 |  SORT ORDER BY              |          |        1 |         6 |          0 |00:00:00.01 |         3 |  1024 |  1024 |              |
|*  2 |   VIEW                      | VW_WIF_1 |        1 |         6 |          0 |00:00:00.01 |         3 |           |           |              |
|   3 |    WINDOW BUFFER      |          |        1 |         6 |          3 |00:00:00.01 |         3 |  2048 |  2048 | 2048  (0)|
|   4 |     HASH GROUP BY     |          |        1 |         6 |          3 |00:00:00.01 |         3 |   948K|   948K|  714K (0)|
|   5 |      TABLE ACCESS FULL| T         |        1 |         6 |          6 |00:00:00.01 |         3 |           |           |              |
-----------------------------------------------------------------------------------------------------------------------

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

   2 - filter("ITEM_1">"ITEM_2"*3)

Note
-----
   - dynamic sampling used for this statement (level=2)


27 rows selected.

[ 本帖最后由 26856649 于 2012-6-25 16:37 编辑 ]

zmyzmy726

发愁了，求大神解答~

Maclean Liu(刘相兵

可以用 _remove_aggr_subquery 参数 还原 到 10g 中的表现


SQL>
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> set linesize 200 pagesize 2000
SQL> SELECT x, SUM (y) AS total_y
  2  FROM t
  3  GROUP BY x
  4  HAVING SUM (y) > (SELECT SUM (y)/3 FROM t)
  5  ORDER BY total_y
  6  /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1651950043

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     6 |   234 |     4  (50)| 00:00:01 |
|   1 |  SORT ORDER BY        |          |     6 |   234 |     4  (50)| 00:00:01 |
|*  2 |   VIEW                | VW_WIF_1 |     6 |   234 |     3  (34)| 00:00:01 |
|   3 |    WINDOW BUFFER      |          |     6 |   156 |     3  (34)| 00:00:01 |
|   4 |     HASH GROUP BY     |          |     6 |   156 |     3  (34)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| T        |     6 |   156 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   2 - filter("ITEM_1">"ITEM_2"*3)

Note
-----
   - dynamic sampling used for this statement (level=2)










SQL>  alter session set "_remove_aggr_subquery"=false;

Session altered.

SQL> SELECT x, SUM (y) AS total_y
  2  FROM t
  3  GROUP BY x
  4  HAVING SUM (y) > (SELECT SUM (y)/3 FROM t)
  5  ORDER BY total_y
  6  /

         X    TOTAL_Y
---------- ----------
         2        200
         3        240


Execution Plan
----------------------------------------------------------
Plan hash value: 4167292448

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     6 |   156 |     4  (50)| 00:00:01 |
|   1 |  SORT ORDER BY       |      |     6 |   156 |     4  (50)| 00:00:01 |
|*  2 |   FILTER             |      |       |       |            |          |
|   3 |    HASH GROUP BY     |      |     6 |   156 |     4  (50)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T    |     6 |   156 |     2   (0)| 00:00:01 |
|   5 |    SORT AGGREGATE    |      |     1 |    13 |            |          |
|   6 |     TABLE ACCESS FULL| T    |     6 |    78 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - filter(SUM("Y")> (SELECT SUM("Y")/3 FROM "T" "T"))

Note
-----
   - dynamic sampling used for this statement (level=2)

Maclean Liu(刘相兵

_remove_aggr_subquery   enables removal of subsumed aggregated subquery

unora

Bug 9189996 - Wrong Results with uncorrelated subquery removal [ID 9189996.8]

fsm

好奇，大神是如何知道使用_remove_aggr_subquery隐含参数的。

psufnxk2000

无所不能的大神啊！！！

無限追云

好贴，学习了。

同问，大神是如何知道并使用这个参数的？