Hi Tom
I'm using the following environment (which I believe is relevant because a similar query broke after migrating from 11 to 12c):
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
The following query
select trunc(SEC), SUM(SEC_COUNT)
from (
select CREATED as SEC, COUNT(* ) as SEC_COUNT
from ALL_OBJECTS group by CREATED
)
group by trunc(SEC)
order by 1 asc
;
returns this result
TRUNC(SECOND) SUM(SECOND_COUNT)
8-Feb-2012 9
8-Feb-2012 1
8-Feb-2012 55
8-Feb-2012 64
8-Feb-2012 58
...The grouping column shows many duplicate values where I expect only one (grouping per per day).
The real query also deals with max counts per second which disallows rewriting with a single aggregation granularity.
Thanks for pointing me to the misconception I must have on this.
Regards,
Stefan
I see the same:
select trunc( sec ) dt,
sum( sec_count )
from (
select created as sec,
count(*) as sec_count
from all_objects
group by created
)
group by trunc( sec )
order by 1;
DT SUM(SEC_COUNT)
07-JUL-2014 00:00:00 70
07-JUL-2014 00:00:00 30
07-JUL-2014 00:00:00 80
07-JUL-2014 00:00:00 87
07-JUL-2014 00:00:00 64
07-JUL-2014 00:00:00 49
07-JUL-2014 00:00:00 50
07-JUL-2014 00:00:00 74
07-JUL-2014 00:00:00 95
07-JUL-2014 00:00:00 56
07-JUL-2014 00:00:00 40
07-JUL-2014 00:00:00 77
07-JUL-2014 00:00:00 58
...This looks like it's related to Bug 26588069 (currently only reported for 12.2). Contact support if you need a patch.
In the meantime, the following workaround suggested in MOS note 26588069.8 does the trick:
ALTER SESSION SET "_optimizer_aggr_groupby_elim" = FALSE;
select trunc( sec ) dt,
sum( sec_count )
from (
select created as sec,
count(*) as sec_count
from all_objects
group by created
)
group by trunc( sec )
order by 1;
DT SUM(SEC_COUNT)
07-JUL-2014 00:00:00 89359
08-AUG-2016 00:00:00 4
09-AUG-2016 00:00:00 55
10-AUG-2016 00:00:00 2
11-AUG-2016 00:00:00 4
12-AUG-2016 00:00:00 2
15-AUG-2016 00:00:00 3
17-AUG-2016 00:00:00 1
18-AUG-2016 00:00:00 2
...