Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Hash join - hashed table in memory

Sekar_BLUE4EVERJan 9 2018 — edited Jan 11 2018

Hi I have a complex query for which I am trying to figure out the optimal tuning order . I am proceeding with the following assumption

start with the table and condition which has the least number  of rows and keep the large tables for join at the end.

So I have decided an order for my first four tables as follows .

select /*+gather_plan_statistics leading( A D C E) */ A.bsid,C.trqty AS S_QTY,
C.CR AS EX_CM from tbl1 A INNER JOIN tbl2 D 
ON D.EID = A.EID AND D.INID = A.bsid AND D.NUMBEME = 'QUCD'
 INNER JOIN tbl3 C ON C.SID = A.bsid AND C.EID = A.EID 
INNER JOIN tbl4 E ON  E.EID = A.EID AND E.ACNO = (CASE WHEN C.SECMOVTYPE = 'DELI' THEN C.SACC ELSE C.CTRSECACC END) AND 
     E.ACERID = (CASE WHEN C.STYPE = 'DELI' THEN C.ENTTRD ELSE C.ENTCTID END)

Plan

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |
  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                     |      1 |        |    483K|00:00:01.35 |     520K|  25480 |       |
|   1 |  NESTED LOOPS           |                     |      1 |     18 |    483K|00:00:01.35 |     520K|  25480 |       |
|*  2 |   HASH JOIN             |                     |      1 |    241K|    617K|00:00:00.53 |     216K|  25449 |  1035K|  1035K| 1651K (0)|
|   3 |    NESTED LOOPS         |                     |      1 |   5843 |   6166 |00:00:00.02 |    5844 |    108 |       |
|   4 |     INDEX FAST FULL SCAN| IDX_tbl1_02         |      1 |   9393 |   9432 |00:00:00.01 |      95 |     87 |       |
|*  5 |     INDEX UNIQUE SCAN   | IDX_tbl2_01         |   9432 |      1 |   6166 |00:00:00.01 |    5749 |     21 |       |
|   6 |    TABLE ACCESS FULL    | tbl3                |      1 |    120K|    121K|00:00:00.33 |     210K|  25341 |       |
|*  7 |   INDEX UNIQUE SCAN     | IDX_tbl4            |    617K|      1 |    483K|00:00:00.73 |     304K|     31 |       |
--------------------------------------------------------------------------------------------------------------------------

Then I tried adding a fifth table G 

select /*+gather_plan_statistics leading( A D C E) */ A.bsid,C.trqty AS S_QTY,
C.CR AS EX_CM from tbl1 A INNER JOIN tbl2 D
 ON D.EID = A.EID AND D.INID = A.bsid AND D.NUMBEME = 'QUCD'
 INNER JOIN tbl3 C ON C.SID = A.bsid AND C.EID = A.EID 
INNER JOIN tbl4 E ON  E.EID = A.EID AND E.ACNO = (CASE WHEN C.SECMOVTYPE = 'DELI' THEN C.SACC ELSE C.CTRSECACC END) AND
 E.ACERID = (CASE WHEN C.STYPE = 'DELI' THEN C.ENTTRD ELSE C.ENTCTID END)
 INNER JOIN  tbl5 G ON G.LANG_ID = 1 and G.NAME = 'd_TrdStatus' AND G.VALUE = C.CURUS and G.CODE_ID = 2000000110

Line number 07 is the fifth table G which has been newly added.

Plan

---------------------------
| Id  | Operation                    | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
---------------------------
|   0 | SELECT STATEMENT             |                        |      1 |        |    483K|00:00:01.35 |     474K|  25471 |
|*  1 |  HASH JOIN                   |                        |      1 |     12 |    483K|00:00:01.35 |     474K|  25471 |    24M|  5228K|   39M (0)|
|   2 |   NESTED LOOPS               |                        |      1 |     18 |    483K|00:00:01.13 |     474K|  25471 |
|*  3 |    HASH JOIN                 |                        |      1 |    241K|    617K|00:00:00.43 |     211K|  25448 |  1035K|  1035K| 1632K (0)|
|   4 |     NESTED LOOPS             |                        |      1 |   5843 |   6166 |00:00:00.02 |    5844 |    107 |
|   5 |      INDEX FAST FULL SCAN    | IDX_tbl1_02            |      1 |   9393 |   9432 |00:00:00.01 |      95 |     87 |
|*  6 |      INDEX UNIQUE SCAN       | IDX_tbl2_01            |   9432 |      1 |   6166 |00:00:00.01 |    5749 |     20 |
|   7 |     TABLE ACCESS FULL        | tbl3                   |      1 |    120K|    121K|00:00:00.26 |     205K|  25341 |
|*  8 |    INDEX UNIQUE SCAN         | IDX_tbl4               |    617K|      1 |    483K|00:00:00.60 |     263K|     23 |
|*  9 |   TABLE ACCESS BY INDEX ROWID| tbl5                   |      1 |      1 |     94 |00:00:00.01 |      56 |      0 |
|* 10 |    INDEX RANGE SCAN          | IDX_tbl5_01            |      1 |    111 |     94 |00:00:00.01 |      15 |      0 |
--------------------------------------------------------------------------------------------------------------------------

AS you can see the plan is initially the same but a new hash join is added to join the table tbl5 with the output of four tables . The table tbl5 returns only 94 rows and so I believe hashing the table tbl5 in memory would be beneficial for the hash join .

According to oracle documentation

Because the orders table is small relative to the order_items table, which is 6 times larger, the database hashes orders. In a hash join, the data set for the build table always appears first in the list of operations (Step 2). In Step 3, the database performs a full scan of the larger order_items later, probing the hash table for each row.

https://docs.oracle.com/database/121/TGSQL/tgsql_join.htm#TGSQL245

I also tried writing the query as a subquery  in the below form

select /*+ leading (G adce) */ adce.bsid,adce.col2,adce.col3 from tbl5 G,(query joinin the tables adce) adce where G.LANG_ID = 1 and G.NAME = 'd_TrdStatus' AND G.VALUE = C.CURUS and G.CODE_ID = 2000000110

but after rewriting in the format the  varies completely and it starts joining tbl5 and tbl1 at the start.

Why is the join order not picking tbl5 to hash in memory ? How can I rewrite the query to make sute it joins tbl5 with the result of other 4 tables.

Thanks
This post has been answered by Jonathan Lewis on Jan 9 2018
Jump to Answer

Comments

486393
Is calling user defined PL/SQL functions allowed or not?
598210
wateenmooiedag the problem will be the performance most probably, but if it can not be handled within already SQL suppllied functionality then yes of course :)

By the way I will be using an 10.2 instance.
561093 
SQL> create or replace function sum_prod(p_val in number) return number is
  2   l_val Varchar2(500) := p_val;
  3   l_sum number := 0;
  4   l_n1  number;
  5   l_n2  number;
  6   l_product number := 1;
  7   l_first number;
  8  begin
  9    for i in 1..length(p_val)
 10    loop
 11      l_first := substr(l_val, 1, 1);
 12      l_sum := l_sum + l_first;
 13      l_product := l_product * l_first;
 14      l_val := substr(l_val, 2);
 15    end loop;
 16    return(l_sum + l_product);
 17  end;
 18  /

Function created.

SQL> select rno from ( select rownum rno
  2                      from dual
  3                    connect by level <= 100)
  4  where rno - reverse(to_char(rno)) = sum_prod(rno);

  RNO
-----
   63

SQL> select rno from ( select rownum rno
  2                      from dual
  3                    connect by level <= 1000)
  4  where rno - reverse(to_char(rno)) = sum_prod(rno);

  RNO
-----
   63
  726

SQL> select rno from ( select rownum rno
  2                      from dual
  3                    connect by level <= 10000)
  4  where rno - reverse(to_char(rno)) = sum_prod(rno);

  RNO
-----
   63
  726
 8937

I think SQL experts can write it more elegantly.

598210
Citrus thank you for your time, but let me rephrase my concerns below, if I use 1000000000000 with this method even on a strong machine it takes half a day to finish.

- Which is the most efficient way of producing for example numbers between 1 and 1000000000000, CONNECT BY from DUAL?
- Is there an alternative way to reverse a number other than undocumented REVERSE function to_number(reverse(to_char(anumber)) this way there will be three function calls for each number,
- What is the best way to break a number into pieces to sum or make product in SQL.
Nicolas Gasparotto

Hi,

The brute force won't help too much in your case.
You may want to exclude at least the number where the last digit is higher than the first.
And still, with such big number, I'm not sure SQL and PL/SQL will be very efficient.
Lastly, even if that takes half ad day to finish, I'm sure it's not a scheduled job, after running it once, it's finish.

Anyway, here below my first try on my laptop :

SQL> create or replace type nb_obj as object (n1 number, n2 number, n3 number, n4 number);
  2  /

Type created.

Elapsed: 00:00:00.00
SQL>
SQL> create or replace type nb_list as table of nb_obj;
  2  /

Type created.

Elapsed: 00:00:00.04
SQL>
SQL> create or replace function f_maths (p_max number) return nb_list pipelined is
  2      x number:=0;
  3      y number;
  4      t number;
  5      z number;
  6
  7  begin
  8      loop
  9          --X is a positive number
 10          x := x+1;
 11          if    substr(x,-1,1) > substr(x,1,1)
 12          then  x := x+11-substr(x,-1,1);
 13          end if;
 14          exit when x > p_max;
 15
 16          y := null;
 17          t := 0;
 18          z := 1;
 19          for j in 1..length(x) loop
 20              --Y is the reverse of X
 21              y:=y||to_char(substr(x,-j,1));
 22              --T is the sum of each number which X has
 23              t:=t+substr(x,-j,1);
 24              --Z is the product of each number which X has
 25              z:=z*substr(x,-j,1);
 26              exit when y > substr(x,1,j);
 27          end loop;
 28          --X - Y = T + Z
 29          if x - y = t + z then
 30              pipe row (nb_obj(x,y,t,z));
 31          end if;
 32      end loop;
 33  end;
 34  /

Function created.

Elapsed: 00:00:00.04
SQL> show err
No errors.
SQL> select *
  2  from   table(f_maths(10000));

        N1         N2         N3         N4
---------- ---------- ---------- ----------
        63         36          9         18
       726        627         15         84
      8937       7398         27       1512

Elapsed: 00:00:00.06
SQL>
SQL> select *
  2  from   table(f_maths(100000));

        N1         N2         N3         N4
---------- ---------- ---------- ----------
        63         36          9         18
       726        627         15         84
      8937       7398         27       1512

Elapsed: 00:00:00.60
SQL>
SQL> select *
  2  from   table(f_maths(1000000));

        N1         N2         N3         N4
---------- ---------- ---------- ----------
        63         36          9         18
       726        627         15         84
      8937       7398         27       1512

Elapsed: 00:00:07.01
SQL>
SQL> select *
  2  from   table(f_maths(10000000));

        N1         N2         N3         N4
---------- ---------- ---------- ----------
        63         36          9         18
       726        627         15         84
      8937       7398         27       1512

Elapsed: 00:01:21.81
SQL>
SQL> select *
  2  from   table(f_maths(100000000));

        N1         N2         N3         N4
---------- ---------- ---------- ----------
        63         36          9         18
       726        627         15         84
      8937       7398         27       1512

Elapsed: 00:15:36.81

Nicolas.

598210
N. Gasparotto thank you for your responce.

Related to just using SQL functions I opened another thread here -
668861

But I guess since there are several function calls those solutions do not help both in performance and easy undertanding compared to a user defined pl/sql like yours. This was an interesting experience :)
561093

I replaced "SUM_PROD" function with wateenmooiedag's query:

2577576

SQL> select rno from ( select rownum rno
  2                          from dual
  3                        connect by level <= 1000)
  4      where rno - reverse(to_char(rno)) =
  5                           (select to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select '
  6                                   ||regexp_replace(rno,'(.)','\1+') || '0'||'
  7                                      s from dual')),'/ROWSET/ROW/S'))
  8                          +        to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select '
  9                                   ||regexp_replace(rno,'(.)','\1*') || '1'||'
 10                                   p from dual')),'/ROWSET/ROW/P'))
 11                              from dual);

       RNO
----------
        63
       726

SQL>
Nicolas Gasparotto
YEah, XML and REGEXP are slow down. I'm pretty sure you can find some more rules to add in your function to exclude more and more numbers.

Nicolas.
Nicolas Gasparotto

Just a new try, ~3 minutes less for hundred million...

SQL> drop type nb_list;

Type dropped.

Elapsed: 00:00:00.11
SQL> create or replace type nb_obj as object (n1 number, n2 number, n3 number, n4 number);
  2  /

Type created.

Elapsed: 00:00:00.01
SQL>
SQL> create or replace type nb_list as table of nb_obj;
  2  /

Type created.

Elapsed: 00:00:00.15
SQL>
SQL> create or replace function f_maths (p_min number,p_max number) return nb_list pipelined is
  2      x number:=p_min-1;
  3      y number;
  4      t number;
  5      z number;
  6      x1 number;
  7      x2 number;
  8  begin
  9      loop
 10          --X is a positive number
 11          x := x+1;
 12          x1 := substr(x,1,floor(length(x)/2));
 13          x2 := substr(x,-floor(length(x)/2));
 14          --dbms_output.put_line(x||' '||x1||' '||x2);
 15          if    x1 < x2 or length(x2) < length(x1)
 16          then  x := ceil(x/power(10,length(x2)))*power(10,length(x2));
 17          end if;
 18          exit when x > p_max;
 19
 20          y := null;
 21          t := 0;
 22          z := 1;
 23          for j in 1..length(x) loop
 24              --Y is the reverse of X
 25              y:=y||to_char(substr(x,-j,1));
 26              exit when y > substr(x,1,j);
 27              --T is the sum of each number which X has
 28              t:=t+substr(x,-j,1);
 29              --Z is the product of each number which X has
 30              z:=z*substr(x,-j,1);
 31          end loop;
 32          --X - Y = T + Z
 33          if x - y = t + z then
 34              pipe row (nb_obj(x,y,t,z));
 35          end if;
 36      end loop;
 37  end;
 38  /

Function created.

Elapsed: 00:00:00.04
SQL> show err
No errors.
SQL> select *
  2  from   table(f_maths(1,100000000));

        N1         N2         N3         N4
---------- ---------- ---------- ----------
        63         36          9         18
       726        627         15         84
      8937       7398         27       1512

Elapsed: 00:12:43.79
SQL>

Nicolas.

MichaelS
Probaly just interesting from an academical point of view. Performancewise it is just not acceptable:
SQL> select * from
  2   xmltable('declare function local:reverse($a)
  3             {
  4               if (string-length($a) != 0) then
  5                concat(substring($a,string-length($a),1), local:reverse(substring($a,1,string-length($a)-1)))
  6               else ()
  7             }; (: eof :)
  8             declare function local:sum($a)
  9             {
 10               if (string-length($a) != 0) then
 11                 xs:integer(substring($a,1,1)) + xs:integer(local:sum(substring($a,2)))
 12               else (0)
 13             }; (: eof :)
 14             declare function local:prod($a)
 15             {
 16               if (string-length($a) != 0) then
 17                 xs:integer(substring($a,1,1)) * xs:integer(local:prod(substring($a,2)))
 18               else (1)
 19             }; (: eof :)
 20             for $i in 1 to 10000
 21              where $i - local:reverse(xs:string($i)) = local:sum(xs:string($i)) + local:prod(xs:string($i))
 22                    return $i' columns x integer path '.')
 23  /

         X
----------
        63
       726
      8937

Abgelaufen: 00:01:24.51
Nicolas Gasparotto
Abgelaufen: 00:01:24.51
Around 1400 times slower than function defined earlier above, who said SQL was always faster than PL/SQL ?
;-)

Nicolas.
MichaelS
Even without xml no faster than Nicolas ;)
SQL> select * from (select level x from dual connect by level <= 10000)
  2         connect by nocycle level <= length (x) and prior x = x - 1
  3   group by x
  4  having x - reverse(to_char(x)) = sum(substr(x,level,1)) + round(exp(sum(ln(decode(substr(x,level,1),0,1,substr(x,level,1))))))
  5   order by x
  6  /

         X
----------
        63
       726
      8937

Abgelaufen: 00:00:02.29
NicloeiW
dats great michaels,


you beat nic this time though,

can i have link from where i can learn more about xml with sql and they way u used it,
Nicolas Gasparotto
you beat nic this time though,
No, mine was less than 1 second for 10,000...
However, really nice sql and obviously shorter.

Nicolas.
NicloeiW
ok, both are same ;-)
can i have few links which explains reagarding xml things
MichaelS
can i have link from where i can learn more about xml with sql and they way u used it,
Some places to start would be

Using XQuery with Oracle XML DB
XQuery Tutorials
XQuery 1.0 and XPath 2.0 Functions and Operators
NicloeiW
dan ke
Nicolas Gasparotto

reverse(to_char(x))

You could use

sum(substr(x,level,1)*power(10,level-1))

to keep a supported way.
;-)

Nicolas.

Colin'tHart

How about:

SELECT x
FROM (
SELECT level AS x, TO_NUMBER(REVERSE(TO_CHAR(level))) AS y, (
       NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '234567890', 'xxxxxxxxxx'), 'x')), 0) * 1
     + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '134567890', 'xxxxxxxxxx'), 'x')), 0) * 2
     + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '124567890', 'xxxxxxxxxx'), 'x')), 0) * 3
     + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123567890', 'xxxxxxxxxx'), 'x')), 0) * 4
     + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123467890', 'xxxxxxxxxx'), 'x')), 0) * 5
     + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123457890', 'xxxxxxxxxx'), 'x')), 0) * 6
     + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456890', 'xxxxxxxxxx'), 'x')), 0) * 7
     + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456790', 'xxxxxxxxxx'), 'x')), 0) * 8
     + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456780', 'xxxxxxxxxx'), 'x')), 0) * 9
     + NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456789', 'xxxxxxxxxx'), 'x')), 0) * 0
) AS t, (
       POWER(1, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '234567890', 'xxxxxxxxxx'), 'x')), 0))
     * POWER(2, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '134567890', 'xxxxxxxxxx'), 'x')), 0))
     * POWER(3, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '124567890', 'xxxxxxxxxx'), 'x')), 0))
     * POWER(4, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123567890', 'xxxxxxxxxx'), 'x')), 0))
     * POWER(5, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123467890', 'xxxxxxxxxx'), 'x')), 0))
     * POWER(6, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123457890', 'xxxxxxxxxx'), 'x')), 0))
     * POWER(7, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456890', 'xxxxxxxxxx'), 'x')), 0))
     * POWER(8, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456790', 'xxxxxxxxxx'), 'x')), 0))
     * POWER(9, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456780', 'xxxxxxxxxx'), 'x')), 0))
     * POWER(0, NVL(LENGTH(REPLACE(TRANSLATE(TO_CHAR(level), '123456789', 'xxxxxxxxxx'), 'x')), 0))
) AS z
FROM dual
CONNECT BY level < 10000
)
WHERE x - y = t + z
;

Cheers,

Colin

Rene W.
Thanks Colin,

A very straightforward and easy to understand solution.
My brain still hurts trying to understand Michaels sql statement ;)

Rene
Anton Scheffer
Not the fastest method, but it works.
       select n1
       from ( select to_char( level ) n1
                   , length( to_char( level ) ) sl
              from dual
              where level > reverse( to_char( level ) ) 
              connect by level < 100000
            )
       where 0 = ( select n1
                        - sum( substr( n1, level, 1 ) * case level
                                                          when 1 then 1
                                                          when 2 then 10
                                                          when 3 then 100
                                                          when 4 then 1000
                                                          when 5 then 10000
                                                          when 6 then 100000
                                                          when 7 then 1000000
                                                          when 8 then 10000000
                                                          when 9 then 100000000
                                                        end
                             )
                        - sum( substr( n1, level, 1 ) )
                        - round( exp( sum( ln( nullif( substr( n1, level, 1 ), '0' ) ) ) ) )
                   from dual
                   connect by level <= sl
                 )
Anton
598210
Thanks to everyone who gave feedback to this thread, pipelined pl/sql solution until now was the fastest for me. But other solutions was great to know, each made me think how alternatives may vary for any kind of question.
486393
I changed Anton's query a little bit.

select n1
from ( select to_char( level ) n1
, length( to_char( level ) ) sl
from dual
where level > reverse( to_char( level ) )
connect by level < 100000
)
where 0 = ( select n1
- sum( substr( n1, level, 1 ) * power(10,level-1) )
- sum( substr( n1, level, 1 ) )
- round( exp( sum( ln( nullif( substr( n1, level, 1 ), '0' ) ) ) ) )
from dual
connect by level <= sl
)
/
486393
I changed the case statement in power(10,level-1)
       select n1
       from ( select to_char( level ) n1
                   , length( to_char( level ) ) sl
              from dual
              where level > reverse( to_char( level ) ) 
              connect by level < 100000
            )
       where 0 = ( select n1
                        - sum( substr( n1, level, 1 ) * power(10,level-1)  )
                        - sum( substr( n1, level, 1 ) )
                        - round( exp( sum( ln( nullif( substr( n1, level, 1 ), '0' ) ) ) ) )
                   from dual
                   connect by level <= sl
                 )
Anton Scheffer
That's my original version.
The power-function is as fast as the "case version"
The method using "round( exp( sum( ln( nullif( substr( n1, level, 1 ), '0' ) ) ) ) )"
is, to my surprise, faster as using a user-defined aggregrate function.


Anton
486393
Interesting.

What kind of user defined aggregate function did you built? With or without collect?
Anton Scheffer 
create type myaggImpl as object
(
  prod NUMBER,
  static function ODCIAggregateInitialize( sctx IN OUT myaggImpl ) return number,
  member function ODCIAggregateIterate( self IN OUT myaggImpl, value IN number ) return number,
  member function ODCIAggregateTerminate( self IN myaggImpl, returnValue OUT number, flags IN number ) return number,
  member function ODCIAggregateMerge( self IN OUT myaggImpl, ctx2 IN myaggImpl ) return number
);
/

create or replace type body myaggImpl
is 
static function ODCIAggregateInitialize( sctx IN OUT myaggImpl ) 
return number
is 
begin
  sctx := myaggImpl( 1 );
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate( self IN OUT myaggImpl, value IN number )
return number
is
begin
  self.prod := self.prod * value;
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate( self IN myaggImpl, returnValue OUT number, flags IN number )
return number
is
begin
  returnValue := self.prod;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT myaggImpl, ctx2 IN myaggImpl )
return number
is
begin
  self.prod := self.prod * ctx2.prod;
  return ODCIConst.Success;
end;
end;
/

CREATE FUNCTION myagg( input NUMBER )
RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING myaggImpl;
/
486393
Collect makes it faster, less context switching between sql and pl/sql . Collect works in Oracle 10 and 11. 
create  type number_table is table of number;
/

create or replace function product (l_numbers number_table )
return number
is
l_result number := 1;
begin
  for i in l_numbers.first..l_numbers.last loop
   l_result := l_result * l_numbers(i);
  end loop;
  return l_result;
end;
/

       select n1
       from ( select to_char( level ) n1
                   , length( to_char( level ) ) sl
              from dual
              where level > reverse( to_char( level ) ) 
              connect by level < 100000
            )
       where 0 = ( select n1
                        - sum( substr( n1, level, 1 ) * power(10,level-1)  )
                        - sum( substr( n1, level, 1 ) )
                        - product(cast(collect(to_number(substr( n1, level, 1 ))) as number_table))
                   from dual
                   connect by level <= sl
                 )
/
1 - 28
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 8 2018
Added on Jan 9 2018
#general-database-discussions, #performance
9 comments
969 views