GROUP BY – wrong results in

Posted by

I encountered this during some testing – the optimizer can be a little overzealous when it encounters nested group-by’s

SQL> create table T ( ts number not null, c char(10));
Table created.
SQL> insert into T
  2  select 100+dbms_random.value(1,50),'x'
  3  from ( select 1 from dual  connect by level < 1000 ),
  4       ( select 1 from dual  connect by level  commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('','T') ;
PL/SQL procedure successfully completed.

So I’ve got ~1million rows, with column TS containing decimal numbers ranging between 100 to 150. Now first I’m going to reduce those numbers to integers, via an inline view, and then truncate those numbers to the nearest 100 (the outer statement). Because all the numbers are between 100 and 150, rounding down to the nearest 100, should return just a single row. Lets see what happens…

SQL> select trunc(ts/100),
  2         sum(cnt) per_min,
  3         avg(cnt) avg_cnt_per_min,
  4         max(cnt) max_cnt
  5  from
  6     ( select cast(ts as int) ts,
  7               count(*) cnt
  8        from   t
  9        group by cast(ts as int)
10      )
11  group by trunc(ts/100)
12  order by 1
13  /
50 rows selected.

The optimizer saw the two group-by’s, and mistakenly decided one could be eliminated. We can work around the problem either by using MATERIALIZE

SQL> with
  2   inline_view as
  3    ( select /*+ materialize */ cast(ts as int) ts,
  4               count(*) cnt
  5        from   t
  6        group by cast(ts as int)
  7      )
  8  select trunc(ts/100),
  9         sum(cnt) per_min,
10         avg(cnt) avg_cnt_per_min,
11         max(cnt) max_cnt
12  from  inline_view
13  group by trunc(ts/100)
14  order by 1
15  /
------------- ---------- --------------- ----------
            1     998001        19960.02      20613

or by setting the initialization parameter “_optimizer_aggr_groupby_elim” to false.

Hope this helps anyone hitting this.


Thanks to Sayan Malakshinov for pointing to some more information on this here:

Addenda #2:

I’ve just applied the patch to bug 19567916 which claims to fix the issue. The patch was applied on top of bp4, and does not appear to have solved it (at least in my case).


Leave a Reply to Sayan Malakshinov Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.