sql - 没有分组依据的 Oracle 结果

Question

我在 Oracle Exadata 上运行以下查询。

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

select sum (t.sum_edw_trx_cnt) 
  from ( 
         select max(x.edw_trx_cnt)sum_edw_trx_cnt, 
                x.prctr_cell_nbr
           from p_prctr_smpl_pf_sp3 x
          where  mdld_prctr_flg = 'Y'
       )t;

我期待 oracle 返回一个错误，因为 - 正如你所看到的，没有 group by内部查询“t”中的子句，我希望这个查询失败。

有数百万条记录，每个记录 prctr_cell_nbr我想要最大计数，然后外部查询应该总结每个 prctr_cell 的最大计数.
这是一个简单的查询。
但是，查询运行并返回 112 的输出这是来自内部查询的最大计数。

我对这种行为感到困惑，因为这不是查询返回的正确结果。我不认为这是一种已知的行为，最近有人看到过吗？

谢谢

Answer 1

您看到的是优化器应用“选择列表修剪”的效果。在这种情况下，它被认为是一个错误 - 如果内嵌 View 包含聚合函数、主查询中未引用的列，并且没有 group by子句，优化器决定简单地删除那些未引用的列(SLP - 选择列表修剪):

环境:Windows x64；甲骨文 12.1.0.1.0

-- test-table 
create table t1 as
  select level as col1
       , level as col2
    from dual
  connect by level <= 7;

-- gather statistic on t1 table. 
exec dbms_stats.gather_table_stats('', 'T1');

现在让我们在启用 10053 跟踪的情况下执行那个有问题的查询，看看在幕后会发生什么:

alter session set tracefile_identifier='no_group_by';

alter session set events '10053 trace name context forever';

select /*+ qb_name(outer) */ col1
  from (
         select /*+ qb_name(inner) */ max(col1) as col1
              , col2
           from t1
       );


      COL1
----------
         7 

alter session set events '10053 trace name context off';

没有预期 ORA-00937错误。一切都很顺利。现在跟踪文件:

OPTIMIZER INFORMATION

******************************************
----- Current SQL Statement for this session (sql_id=d14y7zuxvvfbw) -----
select /*+ qb_name(outer)  */ col1
  from (
         select /*+ qb_name(inner)*/max(col1) as col1
              , col2
           from t1
       )
*******************************************
.....

Query transformations (QT)
**************************
....
SVM: SVM bypassed: Single grp set fct (aggr) without group by.

/* That's where we lose our COL2 */

SLP: Removed select list item COL2 from query block INNER
query block OUTER (#0) unchanged

....

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("OUTER") */ "from$_subquery$_001"."COL1" "COL1" 
  FROM  (SELECT /*+ QB_NAME ("INNER") */ MAX("T1"."COL1") "COL1" 
           FROM "HR"."T1" "T1") "from$_subquery$_001"

作为解决方法 _query_rewrite_vop_cleanup参数可以设置为 false .但是如果在生产环境中需要设置这个参数，当然要咨询oracle支持。

alter session set "_query_rewrite_vop_cleanup"=false;
session altered

select /*+ qb_name(outer)  */ col1
  from (
         select /*+ qb_name(inner) */ max(col1) as col1
              , col2
           from t1
       );

结果:

Error report -
SQL Error: ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"

当我们添加 group by子句，查询按预期工作。

select /*+ qb_name(outer) */ col1
  from (
         select /*+ qb_name(inner) */ max(col1) as col1
              , col2
           from t1
          group by col2
       );

结果:

COL1
----------
         1 
         6 
         2 
         4 
         5 
         3 
         7 

如果你可以访问 MOS，请查看 1589317.1 , 16989676.8 (错误 16989676 - 应在 12.1.0.2 版本中修复)， Bug 8945586笔记。