Home » SQL & PL/SQL » SQL & PL/SQL » Purpose of ODCIAggregateMerge
| Purpose of ODCIAggregateMerge [message #613214] |
Thu, 01 May 2014 02:30  |
 |
user1231
Messages: 4
Registered: May 2014
|
Junior Member |
|
|
Hi,
I've been looking into user-defined aggregate functions, but I can't seem to understand the purpose of ODCIAggregateMerge() function. It is said to be merging two aggregation contexts, however, I don't understand where the other aggregation context comes from. I have created an example of standard deviation aggregate function that takes as a parameter a column of numbers and returns the deviation. When I tested it out, it works properly. Can anybody provide me with some reasonable, simplified explanation of is that function used for/what the aggregate context is?
Thanks
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Purpose of ODCIAggregateMerge [message #613223 is a reply to message #613219] |
Thu, 01 May 2014 04:47  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
OK, here's an example, quite long to build a clear test case.
Here the aggregate function just does a SUM but it logs each procedure call in a TLOG table.
-- Clean
drop procedure plog;
drop sequence s;
drop function f;
drop table t purge;
drop table tlog purge;
drop trigger trg;
drop type typ;
-- Logging environment: a sequence, a table, an insert trigger and a procedure
create sequence s;
create table tlog (id integer, dt timestamp, value varchar2(100));
create or replace trigger trg before insert on tlog
for each row
declare
seq integer;
begin
select s.nextval into seq from dual;
:new.id := seq;
:new.dt := systimestamp;
end;
/
create or replace procedure plog (p_value in varchar2) is
pragma autonomous_transaction;
begin
insert into tlog (value) values (p_value);
commit;
end;
/
-- Aggregation type
create or replace type typ as object
(
pid number,
val number,
static function ODCIAggregateInitialize (sctx IN OUT typ) return number,
member function
ODCIAggregateIterate (self IN OUT typ, value IN number)
return number,
member function ODCIAggregateMerge (self IN OUT typ, ctx2 IN typ) return number,
member function
ODCIAggregateTerminate (self IN typ, returnValue OUT number, flags IN number)
return number
);
/
create or replace type body typ as
static function ODCIAggregateInitialize (sctx IN OUT typ) return number
is
begin
plog ('Initializing process: '||sys_context('userenv','sid'));
sctx := typ(sys_context('userenv','sid'),0);
return ODCIConst.Success;
end;
member function
ODCIAggregateIterate (self IN OUT typ, value IN number)
return number
is
begin
plog ('Process '||self.pid||' handling '||value||' previous total: '||self.val);
self.val := self.val + value;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge (self IN OUT typ, ctx2 IN typ) return number
is
begin
plog ('Merging '||self.pid||' ('||self.val||') and '||ctx2.pid||' ('||ctx2.val||')');
self.val := self.val + ctx2.val;
return ODCIConst.Success;
end;
member function
ODCIAggregateTerminate (self IN typ, returnValue OUT number, flags IN number)
return number
is
begin
plog ('Process '||self.pid||' terminates - total: '||self.val);
returnValue := val;
return ODCIConst.Success;
end;
end;
/
-- Aggregate function
create or replace function f (input number)
return number
parallel_enable aggregate using typ;
/
SQL> -- Test
SQL> create table t as select level nb, cast(' ' as char(512)) val from dual connect by level <= 20;
Table created.
SQL> -- Run in serial
SQL> select 'Total: '||f(nb) res from t;
RES
-----------------------------------------------
Total: 210
1 row selected.
SQL> select value from tlog order by id;
VALUE
----------------------------------------------------------------------------------------------------
Initializing process: 144
Process 144 handling 1 previous total: 0
Process 144 handling 2 previous total: 1
Process 144 handling 3 previous total: 3
Process 144 handling 4 previous total: 6
Process 144 handling 5 previous total: 10
Process 144 handling 6 previous total: 15
Process 144 handling 7 previous total: 21
Process 144 handling 8 previous total: 28
Process 144 handling 9 previous total: 36
Process 144 handling 10 previous total: 45
Process 144 handling 11 previous total: 55
Process 144 handling 12 previous total: 66
Process 144 handling 13 previous total: 78
Process 144 handling 14 previous total: 91
Process 144 handling 15 previous total: 105
Process 144 handling 16 previous total: 120
Process 144 handling 17 previous total: 136
Process 144 handling 18 previous total: 153
Process 144 handling 19 previous total: 171
Process 144 handling 20 previous total: 190
Process 144 terminates - total: 210
SQL> -- Run in parallel
SQL> truncate table tlog;
Table truncated.
SQL> select /*+ parallel(t 2) */ 'Total: '||f(nb) res from t;
RES
-----------------------------------------------
Total: 210
1 row selected.
SQL> select value from tlog order by id;
VALUE
--------------------------------------------------------------------
Initializing process: 148
Process 148 handling 1 previous total: 0
Process 148 handling 2 previous total: 1
Process 148 handling 3 previous total: 3
Process 148 handling 4 previous total: 6
Process 148 handling 5 previous total: 10
Process 148 handling 6 previous total: 15
Process 148 handling 7 previous total: 21
Process 148 handling 8 previous total: 28
Process 148 handling 9 previous total: 36
Process 148 handling 10 previous total: 45
Process 148 handling 11 previous total: 55
Process 148 handling 12 previous total: 66
Process 148 handling 13 previous total: 78
Initializing process: 144
Merging 144 (0) and 148 (91)
Initializing process: 143
Process 143 handling 14 previous total: 0
Process 143 handling 15 previous total: 14
Process 143 handling 16 previous total: 29
Process 143 handling 17 previous total: 45
Process 143 handling 18 previous total: 62
Process 143 handling 19 previous total: 80
Process 143 handling 20 previous total: 99
Merging 144 (91) and 143 (119)
Process 144 terminates - total: 210
|
|
|
|
|
|
| Re: Purpose of ODCIAggregateMerge [message #616014 is a reply to message #613360] |
Wed, 11 June 2014 10:22 |
 |
arielhhs
Messages: 2
Registered: June 2014
Location: SC
|
Junior Member |
|
|
Just created an account to say thanks so much for this explanation Michel Cadot, it has been killing me not knowing what the heck this Merge thing meant! I thought the same thing as the OP, that it was used in these situations. "SELECT MyAggregate(col1), MyAggregate(col2) FROM MyTable" or something.
I have another question relating to these user defined aggregates..
I have made my own user defined aggregates which I only use as an analytic in a very specific context. Rather than give a dirty great block of code, I will just explain what the analytic does (or is supposed to do) and maybe you could even point out a way that this could have been achieved using built in analytics.
MyTable
----------------------------------------------------------------------------------------------------
ID | Value1 | UpperLimit | LowerLimit | MyAnalytic(Value1) | MyAnalytic2(Value1)| MyAnalytic3(Value1)
| | | | As FutureRowID | As FutureRowValue1 | As AboveOrBelow
----------------------------------------------------------------------------------------------------
1 | 102 | 4 | 5 | 5 | 108 | A
2 | 104 | 4 | 4 | 4 | 99 | B
3 | 101 | 4 | 3 | 5 | 108 | A
4 | 99 | 2 | 1 | 5 | 108 | A
5 | 108 | 5 | 4 | 10 | 104 | B
6 | 111 | 6 | 7 | 10 | 104 | B
7 | 110 | 1 | 8 | 9 | 102 | B
8 | 105 | 4 | 5 | - | - | -
9 | 102 | 2 | 5 | 10 | 104 | A
10 | 104 | 4 | 5 | - | - | -
So I hope you can see what I want here, but basically, for each row, i want to look forward down the table, only at rows which have:
FutureRow.ID > currentRow.ID
And
(
FutureRow.Value1 >= CurrentRow.Value1 + CurrentRow.UpperLimit
OR
FutureRow.Value1 <= CurrentRow.Value1 - CurrentRow.LowerLimit
)
And of these "FutureRows" that meet this criteria I want to grab the FIRST (I.E. the one with the lowest ID from those rows) ID, Value1 and say "was it an Above or Below row that met the criteria".
Now that is super easy with a correlated scalar subquery in the select list or maybe some kind of self join, however, i intend to apply this to very large amounts of data and performance is a factor. Currently, my dodgy user defined analytics performs this, except because my windowing clause would be:
"Rows between current row and unbounded following"
I believe it is looking all the way down the table, even once it hits a row that meets this criteria. How do i tell my analytic to stop looking down the table once it has found the row that matches the criteria? Or can this be done with built in analytics? (no sub-querying or self joins please).
If you would like code, just ask i will post it propmtly.
|
|
|
|
|
|
| Re: Purpose of ODCIAggregateMerge [message #616046 is a reply to message #616019] |
Thu, 12 June 2014 00:11 |
|
arielhhs
Messages: 2
Registered: June 2014
Location: SC
|
Junior Member |
|
|
Ok i don't know why i didn't just think to add the trace to it like you did  .. I will do that now. However my user defined analytic turned out to have severe performance issues.
As for whether or not this can be done with built in analytics, I will try to explain what i want to achieve and maybe you can help me:
So the input table is simply MyTable WITHOUT the columns that have the analytics. Sorry for the lack of clarity. Here is the example again which you can directly copy and paste, no DDL required (please note, i made errors in my first example so ignore it and use this):
With MyTable As (
Select 1 As ID, 102 As VALUE1, 4 As UPPERLIMIT, 5 As LOWERLIMIT From DUAL
Union All
Select 2, 104, 4, 4 From DUAL
Union All
Select 3, 101, 4, 3 From DUAL
Union All
Select 4, 99, 2, 1 From DUAL
Union All
Select 5, 108, 5, 4 From DUAL
Union All
Select 6, 111, 6, 7 From DUAL
Union All
Select 7, 110, 1, 8 From DUAL
Union All
Select 8, 105, 4, 5 From DUAL
Union All
Select 9, 102, 2, 5 From DUAL
Union All
Select 10, 104, 4, 5 From DUAL
)
Select MT1.ID,
MT1.VALUE1,
MT1.UPPERLIMIT,
MT1.LOWERLIMIT,
Min(MT2.ID) As FutureRowID, -- This is logically equivalent to MyAnalytic(VALUE1)
Min(MT2.VALUE1) Keep (Dense_Rank First Order By MT2.ID) As FutureRowValue1, -- MyAnalytic2(VALUE1)
Case
When Min(MT2.VALUE1) Keep (Dense_Rank First Order By MT2.ID) >= MT1.VALUE1 + MT1.UPPERLIMIT
Then 'A'
When Min(MT2.VALUE1) Keep (Dense_Rank First Order By MT2.ID) <= MT1.VALUE1 - MT1.LOWERLIMIT
Then 'B'
Else Null
End As AboveOrBelow -- MyAnalytic3(VALUE1)
From MyTable MT1 Left Outer Join MyTable MT2
On ( MT2.ID > MT1.ID
And (MT2.VALUE1 >= MT1.VALUE1 + MT1.UPPERLIMIT Or MT2.VALUE1 <= MT1.VALUE1 - MT1.LOWERLIMIT)
)
Group By
MT1.ID,
MT1.VALUE1,
MT1.UPPERLIMIT,
MT1.LOWERLIMIT;
Normally I would use a Where Clause join with a (+) to perform the outer join but due to the OR condition, this seems to be the only way around that.
If that isn't immediately clear, here is another method which achieves identical results but using scalar correlated-subqueries
With MyTable As (
Select 1 As ID, 102 As VALUE1, 4 As UPPERLIMIT, 5 As LOWERLIMIT From DUAL
Union All
Select 2, 104, 4, 4 From DUAL
Union All
Select 3, 101, 4, 3 From DUAL
Union All
Select 4, 99, 2, 1 From DUAL
Union All
Select 5, 108, 5, 4 From DUAL
Union All
Select 6, 111, 6, 7 From DUAL
Union All
Select 7, 110, 1, 8 From DUAL
Union All
Select 8, 105, 4, 5 From DUAL
Union All
Select 9, 102, 2, 5 From DUAL
Union All
Select 10, 104, 4, 5 From DUAL
)
Select MT1.*,
( Select Min(MT2.ID)
From MyTable MT2
Where MT2.ID > MT1.ID
And ( MT2.VALUE1 >= MT1.VALUE1 + MT1.UPPERLIMIT
Or MT2.VALUE1 <= MT1.VALUE1 - MT1.LOWERLIMIT) ) As FutureRowID, -- MyAnalytic(VALUE1)
( Select Min(MT2.VALUE1) Keep (Dense_Rank First Order By MT2.ID)
From MyTable MT2
Where MT2.ID > MT1.ID
And ( MT2.VALUE1 >= MT1.VALUE1 + MT1.UPPERLIMIT
Or MT2.VALUE1 <= MT1.VALUE1 - MT1.LOWERLIMIT) ) As FutureRowValue1, -- MyAnalytic2(VALUE1)
( Select Case
When Min(MT2.VALUE1) Keep (Dense_Rank First Order By MT2.ID) >= MT1.VALUE1 + MT1.UPPERLIMIT
Then 'A'
When Min(MT2.VALUE1) Keep (Dense_Rank First Order By MT2.ID) <= MT1.VALUE1 - MT1.LOWERLIMIT
Then 'B'
Else Null
End
From MyTable MT2
Where MT2.ID > MT1.ID
And ( MT2.VALUE1 >= MT1.VALUE1 + MT1.UPPERLIMIT
Or MT2.VALUE1 <= MT1.VALUE1 - MT1.LOWERLIMIT) ) As AboveOrBelow -- MyAnalytic3(VALUE1)
From MyTable MT1;
So for each row, we want to see which is the first "future row" (future being defined as having a greater ID than the current row) that has:
FutureRow.Value1 >= CurrentRow.Value1 + CurrentRow.UpperLimit
OR
FutureRow.Value1 <= CurrentRow.Value1 - CurrentRow.LowerLimit
So is there a way to perform this logic using built in analytics to avoid a self join / this subquerying nonsense?
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 10:47:45 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
