Introduction

Even though Oracle has a wide variety of aggregates, not everything is implemented. For example, a basic product is lacking from the list. Oracle Data Cartridge helps to extend the functionality in the database and allows to create for example custom aggregates using PL/SQL. This article shows few simple examples of custom aggregates.

The First Aggregate, Product

Schema for Aggregates (Optional)

Before creating the aggregates, I chose to create a new schema. This is not mandatory but I felt that it would simplify administration if the types and functions that reside in a separate schema:

When running the user creation, change the default and temporary tablespaces suitable to your environment.

PL/SQL Implementation

Creating an aggregate requires to create a type including the header and the body. The header includes the variables needed to store intermediate values in the context and the functions which Oracle will call at runtime. The mandatory functions are:

ODCIAggregateInitialize: This function is called to create a new context for a set of values. The purpose of this static method is to return a new, initialized instance for the type.

ODCIAggregateIterate: Each value is passed to this method which is responsible for making the necessary calculations, etc.

ODCIAggregateMerge: This method is used when two contexts are merged into a single one. Merging happens if the original set of values are iterated using several different contexts.

ODCIAggregateTerminate: When all values have been handled and contexts are merged, this function returns the result.

Merging is best explained with a small (simplified) diagram:

The query processor may divide a set of values to a smaller subsets. When the aggregation is done, each group has its own instance of the type to handle the subset. Each instance is first initialized and the iteration is done for each value in the set. After iterations have been done, the context is merged to another context. Finally, when all the contexts have been merged, the aggregation is terminated and the result is returned to the consumer. For this reason, the aggregate must be designed to support operating on partial sets at iteration time.

The type definition for calculating the product looks like the following:

In the ODCIAggregateInitialize, a new instance of ProductType is created. The mProduct variable is initialized with a null and the mElements with 0.

All the methods return either ODCIConst.Success or ODCIConst.Error depending on whether the operation has succeeded. The ODCIAggregateIterate method multiplies each new value with the existing product and increases the number of elements by 1. ODCIAggregateMerge multiplies products from both contexts if they have had values, otherwise it chooses the product from the context having values. And the ODCIAggregateTerminate function simply sets the return value.

The last step is to create the aggregate itself with CREATE FUNCTION statement:

The function is defined with PARALLEL_ENABLE to let the optimizer know that calculation can be done in parallel. The AGGREGATE USING clause defines the type which implements the aggregate.

Let’s test this:

-- Test run 1
SELECT CustomAggregates.Product(a.Value) AS Result
FROM ( SELECT4AS Value FROM DUAL UNIONALLSELECT2AS Value FROM DUAL UNIONALLSELECT5AS Value FROM DUAL) a;

The result is:

RESULT
----------
40

Well, that was somewhat expectable. What happens if there’s a null in the values.

-- Test run 2, null included
SELECT CustomAggregates.Product(a.Value) AS Result
FROM ( SELECT4AS Value FROM DUAL UNIONALLSELECT2AS Value FROM DUAL UNIONALLSELECTNULLAS Value FROM DUAL UNIONALLSELECT5AS Value FROM DUAL) a;

The result is the same:

RESULT
----------
40

As the ANSI standard defines that nulls are ignored in aggregates such as SUM, MIN etc., they are not sent to the ODCIAggregateIterate at all. This is why nulls were not handled differently in the function. Note that this behaviour is different in Oracle 11g where nulls are actually passed to custom aggregates.

Let's have a final test with an empty set:

-- Test run 3, empty set
SELECT CustomAggregates.Product(a.Value) AS Result
FROM ( SELECT1AS Value FROM DUAL WHERE 1=0) a;

The result is null:

RESULT
----------

Few More Aggregates, Geometric Mean and Harmonic Mean

These aggregates are very similar as the product. Geometric mean is defined:

Now, since this calculation cannot be done in whole in iteration (in this form), we calculate the product in iteration and merge steps and the final result is calculated in the terminate function. So the difference is in the ODCIAggregateTerminate function:

Analytic Functions

Custom aggregates can be used with analytic clauses. For example, if we divide the previous data to two different categories and we want to have the geometric mean for each category, the query looks like:

Basically, the iterate function goes char by char and searches for delimiters. If a word is found, it’s checked against the existing word list and if it’s not present, it will be added. The result of this aggregate is a list of words delimited by semicolon (;).

To test this, let’s take a few simple character strings:

-- Test run 1, case sensitive
SELECT CustomAggregates.Words(a.Value) AS Result
FROM ( SELECT'This is the first string'AS Value FROM DUAL UNIONALLSELECT'And this is the second string'AS Value FROM DUAL) a;

The result is:

RESULT
--------------------------------------------------------------------------------
This;is;the;first;string;And;this;second

So the result is case sensitive since the word This is listed twice. If we want to get the list case insensitively, execute:

-- Test run 2, case insensitive
SELECT CustomAggregates.Words(LOWER(a.Value)) AS Result
FROM ( SELECT'This is the first string'AS Value FROM DUAL UNIONALLSELECT'And this is the second string'AS Value FROM DUAL) a;

And the result is:

RESULT
--------------------------------------------------------------------------------
this;is;the;first;string;and;second

And finally with a little more complex input:

-- Test run 3, Sentences
SELECT CustomAggregates.Words(LOWER(a.Value)) AS Result
FROM ( SELECT'This is the first sentence. And the second: Is this'AS Value
FROM DUAL UNIONALLSELECT'"quote" from somewhere; And the second sentence again!'AS Value
FROM DUAL UNIONALLSELECT'Cursing #!#%# not allowed :)'AS Value
FROM DUAL) a;

Now the result is:

RESULT
--------------------------------------------------------------------------------
this;is;the;first;sentence;and;second;quote;from;somewhere;again;cursing;not;allowed

Final Words

That’s it for now and I hope you found something new. I’d be grateful if you would have the extra time for comments and votes. Thank you.

Comments and Discussions

Custom aggregates is a very nice idea, thanks for the article. Unfortunately, I am having an issue with compiling the code in Oracle 11g. The error that I get is: 'Required tables DBMSHP_FUNCTION_INFO,DBMSHP_PARENT_CHILD_INFO,DBMSHP_RUNS missing'. I have tried to grant permissions as well as set up the Hierarchical Profiler with dbmshptab.sql , however this has not resolved the situation. I am not much of a dba . Any advice?