Oracle GROUP BY返回错误的行数

ASKTOM 2019-02-06

310

问题描述

嗨

有点奇怪-可能是错误。

DROP TABLE xxibpc_test;
CREATE TABLE xxibpc_test AS
WITH data AS
 (SELECT LEVEL l_no
  FROM   dual
  CONNECT BY LEVEL <= 10000)
SELECT 1 instance_number
      ,1 + l_no session_id
      ,10 + l_no session_serial#
      ,CAST (systimestamp - ((1 / 24 / 60) * l_no) AS TIMESTAMP(3)) sample_time
FROM   data;


SELECT trunc(sample_time,'HH24') sample_hr
      ,COUNT(*) cnt
FROM xxibpc_test
GROUP BY trunc(sample_time,'HH24')
ORDER BY 1;




SELECT trunc(sample_date,'HH24') sample_hr
      ,trunc(AVG(sessions)) avg_sessions
      ,MAX(sessions) max_sessions
FROM   (SELECT trunc(sample_time,'Mi') sample_date
              ,COUNT(DISTINCT to_char(instance_number) || to_char(session_id) || to_char(session_serial#)) sessions
        FROM   xxibpc_test
        WHERE  1=1
           --AND rownum < 1e28                 -- Bug Fix
        GROUP  BY trunc(sample_time,'Mi'))
GROUP  BY trunc(sample_date,'HH24')
ORDER  BY 1;
复制

如果运行第一个SELECT，则会得到167行，这是正确的。

但是，如果你运行第二个选择，你会得到10,000行，这是不正确的。

如果取消注释rownum谓词，则会获得正确的167行。

我正在针对dba_hist_active_sess_history使用此版本，以获取每小时一分钟的平均会话数和最大会话数。

我错过了什么吗？我可以让它工作的唯一方法是添加rownum <1e28谓词，我认为这是停止CBO重写查询。

感谢和问候

伊恩

专家解答

是的-这是一只虫子。但是它已在12.2中修复，并且有一个适用于12.1.0.2的补丁程序。

同时，您可以通过将 _ optimizer_aggr_groupby_elim设置为false来解决它:

alter session set "_optimizer_aggr_groupby_elim"=true;

select count(*) from (
SELECT trunc(sample_date,'HH24') sample_hr
      ,trunc(AVG(sessions)) avg_sessions
      ,MAX(sessions) max_sessions
FROM   (SELECT trunc(sample_time,'Mi') sample_date
              ,COUNT(DISTINCT to_char(instance_number) || to_char(session_id) || to_char(session_serial#)) sessions
        FROM   xxibpc_test
        WHERE  1=1
        GROUP  BY trunc(sample_time,'Mi'))
GROUP  BY trunc(sample_date,'HH24')
);

COUNT(*)   
     10000 

alter session set "_optimizer_aggr_groupby_elim"=false;

select count(*) from (
SELECT trunc(sample_date,'HH24') sample_hr
      ,trunc(AVG(sessions)) avg_sessions
      ,MAX(sessions) max_sessions
FROM   (SELECT trunc(sample_time,'Mi') sample_date
              ,COUNT(DISTINCT to_char(instance_number) || to_char(session_id) || to_char(session_serial#)) sessions
        FROM   xxibpc_test
        WHERE  1=1
        GROUP  BY trunc(sample_time,'Mi'))
GROUP  BY trunc(sample_date,'HH24')
);

COUNT(*)   
       167 复制

有关更多详细信息，请参见MOS注释21826068.8。