User-Defined Aggregates and LOBs

User-defined aggregates (UDAGs) provide a mechanism for application/cartridge developers, and end-users to implement and deploy new aggregate functions over scalar datatypes (including LOBs) as well as object and opaque types.

The following are two examples of applications for user-defined aggregates (UDAGs):

• Spatial Cartridge. In Oracle Spatial cartridge, several functions return geometries as the result of a spatial query. These functions can only be aggregated using user-defined aggregate functions. In situations, such as web delivery, it could be efficient to aggregate the results of a query and send one aggregate geometry instead of sending several individual geometries.

  For example, a query to find the state boundary by unionizing the county boundaries in each state can be executed as follows:

  SELECT SDO_AGGR_UNION(county.geometry)
  FROM COUNTIES
  GROUP BY county.state;
  
  

• Trusted Oracle. In Trusted Oracle, the row label is modelled as an opaque type LBAC_LABEL. To define aggregate functions over rowlabels such as greatest lower bound (GLB) and least upper bound (LUB), queries such as the following, can then be executed efficiently.

  SELECT group_column, GLB(rowlabel) 
  FROM x 
  GROUP BY group_column.
  
  

User Defined Aggregate functions (UDAG) refer to aggregate functions with user specified aggregation semantics. You can create a new aggregate function and provide the aggregation logic via a set of routines. Once created, user defined aggregate functions can be used in SQL DML statements like built-in aggregates.

Complex data is typically stored in the database using object types, opaque types or LOBs. User-defined aggregates are useful in specifying aggregation over these domains of data.

UDAGs can also be used to create new aggregate functions over traditional scalar data types for financial or scientific applications. Since, it is not possible to provide native support for all forms of aggregates, this functionality provides you with a flexible way to add new aggregate functions.

An aggregate function takes a set of values as input and returns a single value. The sets of values for aggregation are typically identified using a GROUP BY clause. For example:

SELECT AVG(T.Sales)
  FROM AnnualSales T
  GROUP BY T.State

UDAGs allow you to register new aggregate functions by providing specific (new) implementations for the above primitive operations.

UDAGs: DDL Support

User-defined aggregate functions have the following DDL support:

• Creating and dropping aggregate functions over scalar data types (including LOBs) and user-defined data types (object types and opaque types).

• Specifying the aggregation logic in form of user supplied routines.

• Specification of implementation routines in any combination of the following languages: PL/SQL, C/C++ or Java.

UDAGs: DML and Query Support

User-defined aggregate functions have the following DML and query support:

• SQL statements which invoke the UDAG as part of an expression in the SELECT list and/or as part of the predicate in the HAVING clause

• Both serial and parallel evaluations of the UDAG

• Allow specification of DISTINCT and ALL (default) keywords on the input parameter to the UDAG

• UDAGs with group by extensions - CUBE, ROLLUP and grouping sets

• Materialized views with UDAGs with memory-less refresh option

• Windowing functions with UDAGs

  See Also: Oracle9i Data Cartridge Developer's Guide

  Note: In PL/SQL, UDAGs cannot be used in procedural statements, but can be used in embedded SQL.