Home » SQL & PL/SQL » SQL & PL/SQL » ODCI aggregate question (merged)
ODCI aggregate question (merged) [message #383647] Thu, 29 January 2009 08:17 Go to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Hi,

I'm working on a custom aggregate function called first, which will return the first value of the grouped row, for a given column,

Code is as follows,

1. The New Object Type
CREATE OR REPLACE TYPE firstval AS OBJECT (
   intcounter    NUMBER,
   strfirstval   VARCHAR2 (51),
   STATIC FUNCTION odciaggregateinitialize (actx IN OUT firstval)
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateiterate (SELF IN OUT firstval, val IN VARCHAR2)
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateterminate (
      SELF          IN       firstval,
      returnvalue   OUT      VARCHAR2,
      flags         IN       NUMBER
   )
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregatemerge (SELF IN OUT firstval, ctx2 IN firstval)
      RETURN NUMBER
);
/


This is body of the Object Type
CREATE OR REPLACE TYPE BODY firstval
AS
   STATIC FUNCTION odciaggregateinitialize (actx IN OUT firstval)
      RETURN NUMBER
   IS
   BEGIN
      --   DBMS_OUTPUT.put_line ('Value located');
      actx := firstval (0, '');
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregateiterate (SELF IN OUT firstval, val IN VARCHAR2)
      RETURN NUMBER
   IS
   BEGIN
 --     DBMS_OUTPUT.put_line ('Iterate ' || TO_CHAR (val));
      IF intcounter = 0
      THEN
         strfirstval := val;
      END IF;
	  intCounter := intCounter + 1;

      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregateterminate (
      SELF          IN       firstval,
      returnvalue   OUT      VARCHAR2,
      flags         IN       NUMBER
   )
      RETURN NUMBER
   IS
   BEGIN
      returnvalue := SELF.strfirstval;
      RETURN odciconst.success;
   END;

   MEMBER FUNCTION odciaggregatemerge (SELF IN OUT firstval, ctx2 IN firstval)
      RETURN NUMBER
   IS
   BEGIN
--      DBMS_OUTPUT.put_line ('Code was present in merge');
      IF SELF.strfirstval = ''
      THEN
         SELF.strfirstval := ctx2.strfirstval;
      END IF;

      RETURN odciconst.success;
   END;
END;
/


This is the defined aggregate function "first"
CREATE OR REPLACE FUNCTION FIRST (x VARCHAR2)
   RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE
   USING firstval;


The Above code returns the expected results, but
The Issue is.
1. Performance is extremely bad, as the column that I'm trying to merge have repeated values, so aggregate function "max" also works fine, but max is way too fast. what could be the reason for this slow speed?? Am I going wrong somewhere. on the contrary, I thought that this should be working faster than Max.
2. Is there a way to break OCDI..Iterate loop.? (That could improve the performance)

Thanks in anticipation.
Re: ODCI aggregate question (merged) [message #383659 is a reply to message #383647] Thu, 29 January 2009 09:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It doesn't seem that slow to me.
It ran through a 100,000 row table in about 3 seconds.

It is roughly an order of magnitude slower than MAX, but that's going to be more to do with MAX being better optimised than anything else.
Re: ODCI aggregate question (merged) [message #383682 is a reply to message #383647] Thu, 29 January 2009 12:22 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Thanks for the reply JRowbottom,,,,

I guess there might be something more in that box of mine that is eating up time,

what about the second point,, is there a way to break from the loop ODCIAggregateIterate just after one iteration??
That could really speed things up,,, any Ideas??
Re: ODCI aggregate question (merged) [message #383816 is a reply to message #383682] Fri, 30 January 2009 06:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't think there's a way to break out - that's the method that gets called for each row that the query handles.

In your case you could get a marginal improvement in performance by moving the line intCounter := intCounter + 1 to inside the IF/END IF block, but that's all I can think of.
Re: ODCI aggregate question (merged) [message #383934 is a reply to message #383647] Sat, 31 January 2009 13:27 Go to previous message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Thanks JRowbottom for the reply, I would check out more on the issue,, and would get back in case I find anything more on the subject...


Keep going!!!

Previous Topic: data modeling
Next Topic: Like operator
Goto Forum:
  


Current Time: Sat Dec 03 05:49:44 CST 2016

Total time taken to generate the page: 0.09696 seconds