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> 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 / [snip] 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> 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 / TRUNC(TS/100) PER_MIN AVG_CNT_PER_MIN MAX_CNT ------------- ---------- --------------- ---------- 1 998001 19960.02 20613 SQL>
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: https://jonathanlewis.wordpress.com/2014/09/04/group-by-bug/
I’ve just applied the patch to bug 19567916 which claims to fix the issue. The patch was applied on top of 22.214.171.124 bp4, and does not appear to have solved it (at least in my case).
looks like Bug 19567916
You can avoid it with no_elim_groupby hint:
Have you raised an SR? We’ll take a look at the bug regardless but an SR will help you track it.
Since it looks like 19567916 isn’t resolving the issue, I’ve opened a new bug for this one #20537092