问

没有group by的Oracle结果

发布于 2022-12-07 15:44

oracle

database

数据挖掘

testing

select

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

Oracle Database 11g企业版11.2.0.4.0版 - 64位生产,具有分区,Real Application Clusters,自动存储管理,OLAP,数据挖掘和Real Application Testing选项

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从内部查询返回其输出为最大计数.

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

谢谢

1 个回答

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

环境:Windows x64; Oracle 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
       );

结果:

如果你有机会到MOS,看看1589317.1,16989676.8(错误16989676 -应固定在12.1.0.2版本),错误8945586个笔记.

2022-12-11 02:13 回答

撰写答案

今天，你开发时遇到什么问题呢？

立即提问

热门标签