Ordered iteration in an user-defined aggregate function?

Question

I just implemented the ODCIAggregate Interface to create a custom aggregation function. It works quite well and fast, but I would like it to do a little something more. I have a statement going like this:

SELECT SomeId, myAggregationFunction(Item) FROM
(
  SELECT 
    Foo.SomeId, 
    SomeType(Foo.SomeValue, Foo.SomeOtherValue) AS Item
  FROM
    Foo
  ORDER BY Foo.SomeOrderingValue
)
GROUP BY SomeId;

My problem is that items aren't passed to the ODCIAggregateIterate function of my implementation in the same order that my inner (ordered) SELECT returns them.

I've Googled around and didn't find any Oracle-provided way to do so. Has any of you experimented a similar problem based on that requirement?

Thanks!

Answer 1

Have you considered using COLLECT instead of data cartridge?

At least for string aggregation, the COLLECT method is simpler and much faster. It does make your SQL a little weirder though.

Below is an example using just simple string concatenation.

--Create a type
create or replace type sometype as object
(
    someValue varchar2(100),
    someOtherValue varchar2(100)
);

--Create a nested table of the type.
--This is where the performance improvement comes from - Oracle can aggregate
--the types in SQL using COLLECT, and then can process all the values at once.
--This significantly reduces the context switches between SQL and PL/SQL, which
--are usually more expensive than the actual work.
create or replace type sometypes as table of sometype;

--Process all the data (it's already been sorted before it gets here)
create or replace function myAggregationFunction(p_sometypes in sometypes)
return varchar2 is
    v_result varchar2(4000);
begin
    --Loop through the nested table, just concatenate everything for testing.
    --Assumes a dense nested table
    for i in 1 .. p_sometypes.count loop
        v_result := v_result || ',' ||
            p_sometypes(i).someValue || '+' || p_sometypes(i).someOtherValue;
    end loop;

    --Remove the first delimeter, return value
    return substr(v_result, 2);
end;
/

--SQL
select someId
    ,myAggregationFunction
    (
        cast
        (
            --Here's where the aggregation and ordering happen
            collect(sometype(SomeValue, SomeOtherValue)
                order by SomeOrderingValue)
            as someTypes
        )
    ) result
from
(
    --Test data: note the unordered SoemOrderingValue.
    select 1 someId, 3 SomeOrderingValue, '3' SomeValue, '3' SomeOtherValue
    from dual union all
    select 1 someId, 1 SomeOrderingValue, '1' SomeValue, '1' SomeOtherValue
    from dual union all
    select 1 someId, 2 SomeOrderingValue, '2' SomeValue, '2' SomeOtherValue
    from dual
) foo
group by someId;

--Here are the results, aggregated and ordered.
SOMEID  RESULT
------  ------
1       1+1,2+2,3+3