ODCIAggregateMerge without parallel_enabled

In SQL, when an aggregate function does not have the parallel_enabled setting and ODCIAggregateMerge returns ODCIConst.Error, the function is not intended to run in parallel. While merge may occur during serial evaluation, especially with super-aggregates like rollup or cube, in typical scenarios like simple group by operations, merge is unlikely to be called without parallel_enabled. It is generally safe to assume that without parallel_enabled, merge will not run. However, it's essential to test and validate this assumption based on specific use cases as exceptions may exist.

The ODCIAggregateMerge function is part of the Oracle Data Cartridge Interface (ODCI) and is used to merge two aggregation contexts into a single object instance. This can occur during either serial or parallel evaluation of a user-defined aggregate1.

The parallel_enabled option, when set, indicates that the function can run from a parallel execution server of a parallel query operation2. If parallel_enabled is not set, the function is not designed to be executed in parallel.

However, even in serial evaluation, ODCIAggregateMerge can be invoked in certain scenarios. As you mentioned, super-aggregates like rollup and cube are examples where merge may occur even in serial evaluation3. Another scenario could be when the user-defined aggregate is a window function45.

In your case, if you have not declared your function as parallel_enabled and you return ODCIconst.Error in ODCIAggregateMerge, it should prevent the function from running in parallel. However, it’s important to note that this might not prevent ODCIAggregateMerge from being invoked in all scenarios, especially in complex queries involving super-aggregates or window functions.

It’s generally safe to assume that without parallel_enabled set, ODCIAggregateMerge will not be invoked in a simple group by operation. However, without knowing the full details of your database schema, queries, and data distribution, it’s hard to guarantee this for all possible scenarios. It’s recommended to thoroughly test your aggregate function with various types of queries and data sets to ensure it behaves as expected. If you encounter a scenario where ODCIAggregateMerge is invoked without parallel_enabled, it would be interesting to investigate further. 

Thanks

You need to understand that the optimizer may choose to use different execution strategies based on a variety of factors including but not limited to data distribution, available indexes, and the presence of features like rollup and cube in the query. While it's true that super-aggregate operations like rollup and cube can trigger a merge operation due to their nature of aggregating over multiple levels of aggregation, there might be other less obvious scenarios where Oracle decides to use a merge.

However, if you have not declared your function as parallel_enabled and are returning ODCIConst.Error in the ODCIAggregateMerge method as a precaution, you are essentially signaling to Oracle that your custom aggregate cannot be merged in a parallel execution context. If Oracle respects this setting—which it should, according to its documentation and design—then you shouldn't see ODCIAggregateMerge being invoked in serial execution contexts under normal circumstances.