ODCIAggregateMerge without parallel_enabled

Question

These are quotes from Oracle docs:

[Optional] Merge by combining the two aggregation contexts and return a single context. This operation combines the results of aggregation over subsets in order to obtain the aggregate over the entire set. This extra step can be required during either serial or parallel evaluation of an aggregate. If needed, it is performed before step 4:

,

The ODCIAggregateMerge() interface is invoked to compute super aggregate values in such rollup operations.

We have an aggregate function, that we do NOT want to ever run in parallel.
The reason is that the merging of contexts would be resource consuming and would force us to use different data structures than we are using now, effectively offseting any performance benefits from parallel execution.

Thus, we did not declare our function as parallel_enabled, and instead return ODCIconst.Error in ODCIAggregateMerge 'just in case'.

However, the first quote docs claim, that merge may occur even in serial evaluation.
Super-aggregates (rollup, cube) are obvious examples, but are there any others?

I've been totally unable to reproduce it with simple group by, merge is never called without parallel_enabled and it seems that always only one context is created within the group.

Is it safe to assume that without the parallel_enabled set, merge will never be run?
Have you ever seen a counterexample to that rule?

Cletacleti

Just to be clear, you are looking for examples besides super-aggregates (e.g., GROUP BY GROUPING SETS(...)? The documentation for ODCIAggregateMerge in Oracle 19c added this line: "If the user-defined aggregate *is a window function*, and it is not possible to make an implementation of ODCIAggregateMerge(), ODCIConst.Errorshould be returned. This error is translated as an Oracle user error." I infer from that that there is at least one analytic function case that will invoke a merge, but I can't say what it would be. – Solubilize

Yes, i do know why merge is invoked in super-aggregates ( for example with t as (select 1 x from dual union all select 2 from dual) select t.x, count(1) from t group by rollup(t.x) Here for null x aggregation contexts can be simply merged instead of invoking function for the second time. What I'm looking for is cases beside super-aggregates. – Cletacleti

Also my function is a regular aggregate - i do not know why oracle specified window function there. We use Oracle 11g – Cletacleti

Your function is not just a regular aggregate. User-defined aggregates demonstrably have analytic/window function support. – Acerose

This popped up at the top of my SO "unanswered questions" view for some reason, even though it's old. One other thought -- in a large hash join, where the build table is too large to fit in PGA memory, the joined rows are output in several passes. Perhaps, if aggregates are involved, Oracle might compute the aggregate from each pass and then merge them? Just a theory -- not going to try it out now. – Solubilize

@MatthewMcPeak Well the query has been running on production every day for 3 years and it does involve many hash joins and it hasn't yet crashed. But who knows what great idea the query optimizer will have tommorow. – Cletacleti

@Cletacleti It was just a thought. Also, even if it were true, it wouldn't be a problem for every hash join, just hash joins that needed more memory than was allowed. Thanks for the update though. It's still an interesting question. – Solubilize

Answer 1

I think you can get that in case of some transformations like, for example, group-by placement or OR-Expansion, where group by is transformed into several group-by nested queries with "union all", or grouping-sets, or group pruning in case of complex joins.

Legnica