Skip to Main Content
  • Questions
  • Mechanism to perform the product of a single column in a multirow table

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 12, 2001 - 9:17 am UTC

Last updated: June 20, 2011 - 1:11 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I want to perform the product of a single field for a multi row query

table is
Table Name - TRY
FieldA FieldB
1 10
2 100
3 20
4 10
5 10

My requirement is I want a output as (10 * 100 * 20 * 10 * 10) using a single query (Joins statement are acceptable)
This can be performed using the PL/SQL by reading each row and storing it in a variable.The next row read can be * with the previous value.

and Tom said...

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2196162600402 <code>

in 8i, we don't have a "product" aggregate, in 9i you can write one. In 8i, we would use one of the solutions offered by me using a PLSQL function, for example:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace
2 function product( p_key_name in varchar2,
3 p_key_val in varchar2,
4 p_other_col_name in varchar2,
5 p_tname in varchar2 )
6 return number
7 as
8 type rc is ref cursor;
9 l_num number;
10 l_val number;
11 l_cur rc;
12 begin
13
14 open l_cur for 'select '||p_other_col_name||'
15 from '|| p_tname || '
16 where '|| p_key_name || ' = :x
17 and '||p_other_col_name|| ' is not null'
18 using p_key_val;
19
20 loop
21 fetch l_cur into l_val;
22 exit when l_cur%notfound;
23 l_num := nvl(l_num,1) * l_val;
24
25 end loop;
26 close l_cur;
27
28 return l_num;
29 end;
30 /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select deptno, product( 'deptno', deptno, 'sal', 'emp' ), cnt
2 from (select deptno, count(*) cnt
3 from dept
4 group by deptno )
5 /

DEPTNO PRODUCT('DEPTNO',DEPTNO,'SAL','EMP') CNT
---------- ------------------------------------ ----------
10 1.5925E+10 1
20 2.3562E+16 1
30 1.0153E+19 1
40 1



In 9i, we could use a user defined aggregate:



scott@ORA9I.WORLD> create or replace type product_type as object
2 (
3 total number,
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT product_type )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT product_type ,
11 value IN number )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN product_type,
16 returnValue OUT number,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT product_type,
22 ctx2 IN product_type)
23 return number
24 );
25 /

Type created.

scott@ORA9I.WORLD>
scott@ORA9I.WORLD> create or replace type body product_type
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT product_type)
5 return number
6 is
7 begin
8 sctx := product_type( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT product_type,
13 value IN number )
14 return number
15 is
16 begin
17 self.total := nvl(self.total,1) * value;
18 return ODCIConst.Success;
19 end;
20
21 member function ODCIAggregateTerminate(self IN product_type,
22 returnValue OUT number,
23 flags IN number)
24 return number
25 is
26 begin
27 returnValue := self.total;
28 return ODCIConst.Success;
29 end;
30
31 member function ODCIAggregateMerge(self IN OUT product_type,
32 ctx2 IN product_type)
33 return number
34 is
35 begin
36 self.total := nvl(self.total,1) * nvl(ctx2.total,1);
37 return ODCIConst.Success;
38 end;
39
40
41 end;
42 /

Type body created.

scott@ORA9I.WORLD>
scott@ORA9I.WORLD> CREATE or replace
2 FUNCTION product(input number )
3 RETURN number
4 PARALLEL_ENABLE AGGREGATE USING product_type;
5 /

Function created.

scott@ORA9I.WORLD>
scott@ORA9I.WORLD> select deptno, product(sal)
2 from emp
3 group by deptno
4 /

DEPTNO PRODUCT(SAL)
---------- ------------
10 1.5925E+10
20 2.3562E+16
30 1.0153E+19

scott@ORA9I.WORLD>

Rating

  (30 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I heard that 9i supports object inheritance...

Scott Sowers, December 12, 2001 - 10:09 am UTC

Tom,

I've seen you define a few 9i aggregate object types. Is object inheritance in 9i sufficiently robust to allow us to just define an interface/abstract class/concrete class with those ODCI... members?

Thank you much.

Tom Kyte
December 12, 2001 - 10:58 am UTC

Not following you on this question.

The aggregates use a "template" construct -- similar to an interface, very similar, but not identical. You create a type with various well defined entry points. You register (associate) this type with a function. The database calls the appropriate type methods in your type at runtime with the correct inputs.

So, this is alot like implementing an interface...

I don't see where inheritance would come into play here at all? Why would you want to use that construct, how would it be useful?

A little mathematics...

Connor, December 12, 2001 - 6:03 pm UTC

select exp(sum(ln(col))
from table

is a way for doing products.

Tom Kyte
December 13, 2001 - 8:31 am UTC

very good -- i knew there must have been a way to do this, very nice...

Not really mathematics, just calculus...

Sergei, December 13, 2001 - 6:58 am UTC

Conner right, but it works for positive numbers only (it's easy to make it working on the whole NUMBER range tough).

I was just wondering isn't it would be more efficient to use package variable? Something like:
exec my_product.reset_product; -- we need to reset each time
select test_package.show_product, count(my_product.product(x)) from my_table; --discarding results from count
select my_product.show_product from dual; -- this could be used either
I have that feeling that with dicent data samples ln(x) will eat all the cpu resources.

Cheers

Tom Kyte
December 13, 2001 - 9:35 am UTC

Benchmark it!  It depends on the approach.  My approach where you hide the group by in an inline view and call product AFTER The grouping is pretty efficient (only calls plsql one for each OUTPUT row).  Calling PLSQL once for every row as you have would be much slower then LN().  Cursory tests do show that calling product against the inline view to be faster then calling exp(ln()) for each row -- however, if you do not have the ability to write a function for whatever reason (draconian rules where you work for example), exp(ln()) is a nice solution.

That and you cannot group by in your solution....

So, lets fix that negative number problem:

tkyte@TKYTE816> create table t ( x int );
Table created.

tkyte@TKYTE816> insert into t values ( 100 );
1 row created.

tkyte@TKYTE816> insert into t values ( -100 );
1 row created.

tkyte@TKYTE816> select exp(sum(ln(abs(x))))*decode(mod( count(decode(sign(x),-1,1,null)),2),1,-1,1)
  2  from t
  3  /

EXP(SUM(LN(ABS(X))))*DECODE(MOD(COUNT(DECODE(SIGN(X),-1,1,NULL)),2),1,-1,1)
---------------------------------------------------------------------------
                                                                     -10000

tkyte@TKYTE816>
tkyte@TKYTE816> insert into t values ( -1 );

1 row created.

tkyte@TKYTE816>
tkyte@TKYTE816> select exp(sum(ln(abs(x))))*decode(mod( count(decode(sign(x),-1,1,null)),2),1,-1,1)
  2  from t
  3  /

EXP(SUM(LN(ABS(X))))*DECODE(MOD(COUNT(DECODE(SIGN(X),-1,1,NULL)),2),1,-1,1)
---------------------------------------------------------------------------
                                                                      10000


All i did here was count the number of negative entries -- if ODD then mulitply by -1, else multiply by +1....


Still that won't handle 0, so we can:

tkyte@TKYTE816> select exp(sum(ln(abs(decode(x,0,1,x)))))*
  2         decode(mod( count(decode(sign(x),-1,1,null)),2),1,-1,1)*
  3             nvl(max(decode(x,0,0,null)),1) prod
  4  from t
  5  /

      PROD
----------
     10000

tkyte@TKYTE816> insert into t values ( 0 );

1 row created.

tkyte@TKYTE816>
tkyte@TKYTE816> select exp(sum(ln(abs(decode(x,0,1,x)))))*
  2         decode(mod( count(decode(sign(x),-1,1,null)),2),1,-1,1)*
  3             nvl(max(decode(x,0,0,null)),1) prod
  4  from t
  5  /

      PROD
----------
         0

that'll multiply the product by either 0 (if we detect a zero in the group) or by 1 otherwise....

In 9i, it is interesting to note that enhancements to the engine make the PLSQL solution better:

ops$tkyte@ORA9I.WORLD> create table t as select mod(object_id,5)+1 x, owner from all_objects
  2  where owner not in ( 'PUBLIC', 'SYS', 'SYSTEM' );

Table created.

Elapsed: 00:00:00.35
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> insert into t select -x, owner from t;

294 rows created.

Elapsed: 00:00:00.02
ops$tkyte@ORA9I.WORLD> select count(*) from t;

  COUNT(*)
----------
       588

Elapsed: 00:00:00.00
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> set timing on
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> select owner,
  2         exp(sum(ln(abs(decode(x,0,1,x)))))*
  3         decode(mod( count(decode(sign(x),-1,1,null)),2),1,-1,1)*
  4         nvl(max(decode(x,0,0,null)),1) prod
  5  from t
  6  group by owner
  7  /

OWNER                                PROD
------------------------------ ----------
A                                     -25
BENCH                                3600
BOOK                           7464960000
CLBECK                              -2025
DEMO11                         1.7199E+15
DEMO2                                  -9
DEMO20                               -625
DEMO21                                 -4
DEMO4                                1600
DEMO5                                  -1
DEMO6                                  25
FORUM                          3.8698E+19
HR                             2.0543E+32
HR1                            1.2839E+27
LOW_PRIORITY                           -1
OPS$CLBECK                     7.1547E+48
OPS$ORACLE9I                            9
OPS$SDILLON                       -360000
OPS$TKYTE                      -1.548E+16
OUTLN                            -1440000
SCOTT                          -1.548E+20
SH                             -4.778E+17
TEST                                   -4
TEST1                                 144
TMC                                    64
TMC1                                   -1
TYPES                                 144

27 rows selected.

Elapsed: 00:00:00.21
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> select owner, product(x)
  2  from t
  3  group by owner
  4  /

OWNER                          PRODUCT(X)
------------------------------ ----------
A                                     -25
BENCH                                3600
BOOK                           7464960000
CLBECK                              -2025
DEMO11                         1.7199E+15
DEMO2                                  -9
DEMO20                               -625
DEMO21                                 -4
DEMO4                                1600
DEMO5                                  -1
DEMO6                                  25
FORUM                          3.8698E+19
HR                             2.0543E+32
HR1                            1.2839E+27
LOW_PRIORITY                           -1
OPS$CLBECK                     7.1547E+48
OPS$ORACLE9I                            9
OPS$SDILLON                       -360000
OPS$TKYTE                      -1.548E+16
OUTLN                            -1440000
SCOTT                          -1.548E+20
SH                             -4.778E+17
TEST                                   -4
TEST1                                 144
TMC                                    64
TMC1                                   -1
TYPES                                 144

27 rows selected.

Elapsed: 00:00:00.14


and easier to read by far ;)

 

comment about string aggregate package

Mikito Harakiri, December 13, 2001 - 1:55 pm UTC

First, it's better to move hardcoded comma outside of the aggregate function implementation and concatenate with comma in the query itself like this:

select deptno, ltrim((stragg(','||ename)),',')
from emp
group by deptno
/

Second, in my hierarchical query I want to show the path to the employees like this:

KING.JONES.SCOTT

(Because of the dot see the comment#1).

Now if I perform corellated scalar subquery like this:

select
(select stragg(ename||'.') from emp ee
connect by prior mgr=empno
start with ee.empno=e.empno)
from emp e
connect by prior empno=mgr
start with ename = 'KING';

then I need to flag to the aggregate function that I want to concatenate in reverse order. How do I do this with the package?


Tom Kyte
December 13, 2001 - 2:54 pm UTC

well, thats your opinion -- sure. I think moving it out obscures the simplicity but hey -- its up to you. (stragg is in a totally different question as well).

In the connect by, why not just use the sys_connect_by_path
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1014464646711 <code>
...

To "pass" things to a user defined aggregate, I suppose you could use a package - put globals into, (like separator, reverse_flag) and let people set those values -- your user defined aggregate would look to the values in the package to decide how to work.

minor correction

Mikito Harakiri, December 13, 2001 - 1:59 pm UTC

outer "connect by" is unecessary, of course:

select
(select stragg(ename||'.') from emp ee
connect by prior mgr=empno
start with ee.empno=e.empno)
from emp e


sys_connect_by_path

Mikito Harakiri, December 13, 2001 - 5:40 pm UTC

What if my concatenation needs to take "level" into account as well?

KING./..SMITH../...JONES

Moralizing: All other concerns beeing ignored (performance, etc), it is better to have subquery than a black box function.

Sorry, for writing into the wrong thread.

Tom Kyte
December 13, 2001 - 9:42 pm UTC

If we could ignore all other concerns (performance, et. al.) we would just be using sed, grep and awk.

You've brought this up before with analytic functions -- where you don't like them for some reason simply because you can do it in sql (even though the analytic functions are literally thousands of times faster -- many thousands of times).

Performance is always important to me, I don't answer any questions here without considering performance -- I'll many times give two or three answers (slow -- but what you asked for, fast and almost what you wanted -- maybe you can live with it).

I can think of better ways to do what you ask -- using the sys_connect_by_path -- that would perform much better then a correlated subquery that redoes the work we just did (over and over and over)....

I'll moralize:

All other concerns aside -- it is better to do things simply and to use out of the box functionality -- rather then writing somewhat obscure implementations that are harder to maintain and harder to tune.


About Accuracy

Asim Naveed, September 25, 2003 - 7:06 am UTC

Regarding the Product solution earlier in this
thread.

select exp(sum(ln(col))
from table

Wouldnt it gives an inaccurate product?
Becuase LN is being used.

The above solution cannot be used when accuracy is
required, agree???



am i wrong

Asim Naveed, September 26, 2003 - 5:12 pm UTC

Am I wrong?????

Tom Kyte
September 26, 2003 - 8:16 pm UTC

no

Nopparat V., April 10, 2004 - 11:36 pm UTC

Great! I can find my solution here again.

Three _related_ questions ...

Gabe, August 12, 2004 - 3:32 pm UTC

Hi Tom,

Hope my questions are applicable to this thread ...

#1: main problem

create table adj
( pid number not null
,ord number not null
,flg varchar2(1) not null
,val number not null
,constraint pk primary key (pid,ord)
,constraint chk check (flg in ('Y','N'))
-- flg='Y' when val represents an offset adjustment expressed as a percentage
-- flg='N' when val represents an offset adjustment expressed as an amount
);

insert into adj values (111,2,'N',1000);
insert into adj values (111,4,'Y', -40);
insert into adj values (111,6,'N',-100);
insert into adj values (111,8,'Y', +10);

Considering "partitions by pid order by ord" ... let us assume the first VAL in each partition is always represented as an amount (flg='N') and that a VAL representing a percentage (flg='Y') is always greater than -100 (would be interesting to see if we allow adjustments equal to -100 ... but for now " > -100").

The first line in the partition represents the original value ... each subsequent line represents an adjustment (expressed as a percentage or amount) over the previous adjusted value.

I'm looking for a sql query (w/o pl/sql) to return the cumulative adjusted values ... like this:

111,2,'N',1000 ==> 1000
111,4,'Y', -40 ==> 600
111.6.'N',-100 ==> 500
111,8,'Y', +10 ==> 550

Now, a variation/generalization to #1 above would be to have, instead of the FLG, VAL columns, 2 separate columns, one for offset percentages and one for offset amount ... like ...

#2: derived problem

create table adj
( pid number not null
,ord number not null
,amt number not null
,pct number not null
,constraint pk primary key (pid,ord)
,constraint chk check (pct > -100)
);

-- assume PCT always 0 in the first partition row
insert into adj values (222,2,1000, 0);
insert into adj values (222,4, 100,-40);
insert into adj values (222,5,-200,+20);
insert into adj values (222,6, 10,+10);

where each adjustment can have both a percentage offset and an amount offset over the previous adjusted value (percentage applied first, followed by the amount)

Again, we want a sql query returning something like:

222,2,1000, 0 ==> 1000
222,4, 100,-40 ==> 700 ... = (1000 - 400 + 100)
222,5,-200,+20 ==> 640 ... = ( 700 + 140 - 200)
222,6, 10,+10 ==> 714 ... = ( 640 + 64 + 10)

What I’ve (somehow) managed so far is something ... probably an order of magnitude easier than #2 ... adjustments represented only as percentages:

#3: easier problem

same table definition as #2

-- assume PCT always 0 in the first partition row
-- assume AMT always 0 in all the other partition rows (although my query ignores the non-zero amounts)
insert into adj values (333,1,1000, 0);
insert into adj values (333,2, 0,+40);
insert into adj values (333,3, 0,+20);
insert into adj values (333,4, 0,+10);

insert into adj values (444,2,1000, 0);
insert into adj values (444,3, 0,-40);
insert into adj values (444,4, 0,+20);
insert into adj values (444,5, 0,+10);

select pid,ord,amt,pct,(orig_amt*prd)/power(100,rn) cumm_amt
from (
select pid,ord,amt,pct
,row_number() over (partition by pid order by ord) rn
,first_value(amt) over (partition by pid order by ord) orig_amt
,exp(sum(ln(100+pct)) over (partition by pid order by ord)) prd
from adj
);

PID ORD AMT PCT CUMM_AMT
---------- ---------- ---------- ---------- ----------
222 2 1000 0 1000
222 4 100 -40 600
222 5 -200 20 720
222 6 10 10 792
333 1 1000 0 1000
333 2 0 40 1400
333 3 0 20 1680
333 4 0 10 1848
444 2 1000 0 1000
444 3 0 -40 600
444 4 0 20 720
444 5 0 10 792

I would prefer not to have to use the exp.sum.ln _trick_ ... I'm not sure if I can use your "product" function over the ODCIAggregate interface to calculate cumulative multiplications ... anything more elegant/precise/performant would be appreciated.


Tom Kyte
August 12, 2004 - 3:42 pm UTC

user defined aggregates are "analytic safe" as well -- plop it in and see what it says :)

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:15637744429336#16545124501853 <code>

shows an example we worked through once.

back at .... me?

Gabe, August 12, 2004 - 5:37 pm UTC

Thanks.

[one question at the end]

flip@flop> select pid,ord,amt,pct
2 ,adjust(adj_rec(pct,amt)) over (partition by pid order by ord) cumm_amt
3 from adj;

PID ORD AMT PCT CUMM_AMT
---------- ---------- ---------- ---------- ----------
222 2 1000 0 1000
222 4 100 -40 700
222 5 -200 20 640
222 6 10 10 714
333 1 1000 0 1000
333 2 0 40 1400
333 3 0 20 1680
333 4 0 10 1848
444 2 1000 0 1000
444 3 0 -40 600
444 4 0 20 720
444 5 0 10 792

create or replace type adj_rec as object
( pct number
,amt number
);
/
show errors


create or replace type adj_type as object
( total number
--
,static function ODCIAggregateInitialize
( sctx in out adj_type
) return number
--
,member function ODCIAggregateIterate
( self in out adj_type
,value in adj_rec
) return number
--
,member function ODCIAggregateTerminate
( self in adj_type
,returnValue out number
,flags in number
) return number
--
,member function ODCIAggregateMerge
( self in out adj_type
,ctx2 in adj_type
) return number
);
/
show errors


create or replace type body adj_type is

static function ODCIAggregateInitialize
( sctx in out adj_type
) return number is
begin
sctx := adj_type(null);
return ODCIConst.Success;
end;

member function ODCIAggregateIterate
( self in out adj_type
,value in adj_rec
) return number is
begin
self.total := (nvl(self.total,0) * (100+value.pct)) / 100 + value.amt;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate
( self in adj_type
,returnValue out number
,flags in number
) return number is
begin
returnValue := self.total;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge
( self in out adj_type
,ctx2 in adj_type
) return number is
begin
self.total := nvl(self.total,1) + nvl(ctx2.total,1);
return ODCIConst.Success;
end;
end;
/
show errors


create or replace function adjust(input adj_rec ) return number
aggregate using adj_type;
/
show errors

But, although the next query seems to produce the correct results, is it safe to say that the implementation is not deterministic for simple (non-analytical) aggregations? (because we cannot control the order in which adjustments are actually applied) ...

flip@flop> select pid,adjust(adj_rec(pct,amt))
2 from adj
3 group by pid
4 ;

PID ADJUST(ADJ_REC(PCT,AMT))
---------- ------------------------
222 714
333 1848
444 792


Tom Kyte
August 13, 2004 - 8:53 am UTC

it would be non-deterministic if the order in which the values are computed matters -- since there is no ordering of data (and group by doesn't have to sort anything)

not quite ready ...

Gabe, August 12, 2004 - 8:47 pm UTC

Actually the question I have is about the Merge method.

To make the implementation deterministic for simple aggregation one needs to array-collect the rows in the Iterator and do all the computation in the Terminator against the sorted arrays.

The Merge though...? For the "Product" and "Stragg" implementations (even for the sorted "Stragg") it is kind of obvious what the Merge method should do: for "Product", multiply the totals from the two contexts ... for "Stragg", concatenate the strings from the two contexts; both operations are associative.

Ooops ... I think I got it (while writing this down)! For my adjustment aggregation I need to do the same as for the sorted stragg ... save in arrays within the Iterator ... merge the arrays during the Merge ... and do all the calculation, against the properly sorted arrays, in the Terminate method. Just tell me if I got this totally backwards.

How does one actually make sure to exercise the Merge method?

Thanks.


Tom Kyte
August 13, 2004 - 9:57 am UTC

run lots of parallel queries?

and it does ...

Gabe, August 13, 2004 - 9:26 am UTC

<quote>it would be non-deterministic if the order in which the values are computed matters</quote>

The order of applying adjustements doesn't matter if they all are percentage based (problem #3 above) ...
adjust(adjust(adjust(1000,-40%),20%),10%) =
adjust(adjust(adjust(1000,10%),-40%),20%) =
any other permutation of the percentages ...

The order does matter when an adjustement is made up of both a percentage and a numeric component (problem #2 above) ...
adjust(adjust(1000,50%,100),20%,50) != adjust(adjust(1000,20%,50),50%,100) ... which is what I tried to implement with the cartridge interface.

I probably asked for it …

Gabe, August 13, 2004 - 7:54 pm UTC

Ok … I get it … by running _lots_ of parallel queries the Merge method will get all fit and healthy :-)

What doesn’t quite compute with me is the following ... from the ODCI reference documentation: <quote>The ODCIAggregateMerge function is invoked by Oracle to merge two aggregation contexts into a single object instance. Two aggregation contexts may need to be merged during either serial or parallel evaluation of the user-defined aggregate.</quote>. Why and what for would the merging be required during the _serial_ evaluation?

I built my “adjust” function without PARALLEL_ENABLE … I put some (very likely) incorrect code in the Merge (results were still OK) … when the intention is to have a function without parallelism enabled, should the method contain just “return ODCIConst.Success;”? ... does it matter at all what is in there?

Thanks.

Tom Kyte
August 14, 2004 - 1:11 pm UTC

Not sure when they would be invoked in serial -- I've not hit that myself.

Given that, it would probably be best to code the merge as

raise_application_error( -20001, 'Not implemented' );

just to be safe.


old dog new trick

ant, March 02, 2005 - 11:19 am UTC

SQL> select rownum r,
  2         exp(sum(ln(rownum))over(order by rownum)) prod
  3    from emp
  4   where rownum <= 10;

         R       PROD
---------- ----------
         1          1
         2          2
         3          6
         4         24
         5        120
         6        720
         7       5040
         8      40320
         9     362880
        10    3628800

10 rows selected.

SQL> select r, prod
  2     from (
  3   select rownum as r
  4     from emp
  5    where rownum <= 10
  6          )
  7    model
  8      dimension by(row_number()over(order by r) rn )
  9      measures(0 prod, r)
 10     rules (
 11       prod[any] = case when   r[cv()-1] is null then r[cv()]
 12                       else prod[cv()-1]*r[cv()]
 13                  end
 14     );

         R       PROD
---------- ----------
         1          1
         2          2
         3          6
         4         24
         5        120
         6        720
         7       5040
         8      40320
         9     362880
        10    3628800

10 rows selected.

and negatives are no prob:


SQL> select r, prod
  2     from (
  3   select -rownum as r
  4     from emp
  5    where rownum <= 10
  6          )
  7    model
  8      dimension by(row_number()over(order by r desc) rn )
  9      measures(0 prod, r)
 10     rules (
 11       prod[any] = case when   r[cv()-1] is null then r[cv()]
 12                       else prod[cv()-1]*r[cv()]
 13                  end
 14     );

         R       PROD
---------- ----------
        -1         -1
        -2          2
        -3         -6
        -4         24
        -5       -120
        -6        720
        -7      -5040
        -8      40320
        -9    -362880
       -10    3628800

10 rows selected.

sexy model. 

How to fix exp(sum(ln()) correctly

Mikito Harakiri, April 08, 2005 - 10:23 pm UTC

The functions should be defined on the domain of complex numbers (including infinity). Here is what maple says

> log(-1);

I Pi

> log(-2);

ln(2) + I Pi

> log(-3);

ln(3) + I Pi

> simplify(exp(log(-3)+log(2)+log(5)));

-30

If oracle is serious making inroad into scientific application area, implementing complex numbers domain is a must!

Tom Kyte
April 09, 2005 - 7:32 am UTC

we are most interested in practical things, such as partitioning. (yah, referring to a current newsgroup thread of yours...)

Match Formula using CASE stmt in SQL

sam, October 04, 2005 - 12:40 pm UTC

Tom,
After reading this article and i have used one of your exmaple to do following calculation but keep getting

error : ORA00936 Missing Expression in below query.

tab1(deptname,id,value);

tab1(a,1,10)
(a,2,33)
........
(a,10,91)
(b,1,33)
.....
(b,10,35)


I am trying to achieve something like this from below query

((a*b)+(c*d)+(e*f))/(b+d+f)

select case when sum(select value from (select value ,rownum rn from tab1 where rownum<27 ) where rn = 26) over (partition by deptname) = 0 then 0 ELSE
sum(select value from (select value ,rownum rn from tab1 where rownum<27 ) where rn = 26 * select value from (select value ,rownum rn from tab1 where rownum<34 ) where rn = 33) over (partition by deptname)/ sum(select value from (select value ,rownum rn from tab1 where rownum<34 ) where rn = 33) over( partition by deptname)
end
from tab1;


Error : ORA--00936 Missing Expression

what is wrong with this query?

Thanks


Tom Kyte
October 04, 2005 - 5:01 pm UTC

I'm not a sql compiler

if you format it pretty (readable by humans) it might become more clear

and if you spread it out over many lines, sqlplus will show you the line number


You do seem to think rows have some order?!? You are going after "row 33" and "row 34"??? they are random, they have no order without an order by.

DB recovery -Critical to organization

sam, April 18, 2007 - 6:59 am UTC

Tom,

We have got Oracle DB 9i and 10g both are running on sun-unix server.

Yesterday night i have got a news that our 10g db was facing some problem after adding some extra new space.

Now, no jobs could run means, customer cant you use service.

I have checked with DBA, found out they were taking incremental backup, they took last full back on 11th APr07 and after that incremental backup, but logs were not backed up on tapes or other disk, and all the db nodes logs and data corrupted.

Now this db was holding many delata tables and now dont know how to get them back, as logs are corrupted, may be we need to all the jobs, changes in the past again to create to back db upto date.

Now i think they will restore db till 11th apr, then we have to start our jobs, god knows what data we will get after that.

Can you please suggest any way so we could get back data or restore it back in nearest past , like db was live till yesterday 17th apr 07.

Thanks

Tom Kyte
April 18, 2007 - 12:11 pm UTC

please use support

there is no way anyone can give you useful information given this input - please, use support.

NO_MERGE, USE_HASH - Hints

sam, April 19, 2007 - 8:20 am UTC

Tom,

wondering how do below hints work and what is the best query for them to use.

USE_HASH
NO_MERGE

Thanks
Tom Kyte
April 19, 2007 - 9:45 am UTC

it is best to never use them.

they are documented... You can peek in the docs for more info....

Slow Running SQL

sam, May 08, 2007 - 8:15 am UTC

Tom,

Wondering i have got below sql is running forever,takes long time, it has nearly 10 to 12 tables, some of them have 100,000 / 500,000 rows, some of them have few hundreds.

SQL is like this

INSERT INTO T VALUES(.....)
SELECT (......)
FROM
(SELECT /+* parallel() */.....
FROM
WHERE d.field = 'aaa'
a.id = b.id(+)
a.field2 = b.field2
a.date >= c.date
)ip,
(select /+* parallel() */.....
FROM p, q, r, (select /+* parallel() */... from m where ....)s
WHERE p.field = q.field
r. id = p. id
s.date > r.date
)sp

where ip.id = sp.id(+)
ip.date >= sp.date
.....

========================

Any suggestion how i can above query efficient.

Thanks
Sam

PARALLEL Hint with DML-Multiple DML on Same table

sam, May 29, 2007 - 6:35 am UTC

ALTER SESSION ENABLE PARALLEL DML;
Tom,

below is my query to delete 10 millions of records


DELETE /*+ PARALLEL(ra 4) */ ira ra
WHERE id_cde LIKE 'QP%'
AND edte > TO_DATE('01-APR-2007', 'DD-MON-YYYY')
AND sdte >= TO_DATE('31-DEC-2006', 'DD-MON-YYYY')
/

COMMIT;

Oracle 9.2.0.7, Sun Solaris, Unix OS, 1 index on tab


Observations

1>PARALLEL HINT didnt work

2>Noticed 4 parallel slaves for above sql on v$SESSION_LONGSOPS

3> DML was deleting rows from the table at the same time another dml (insert statement ) was inserting new records in the same table, should not it lock the table on oracle??
but it inserted records and at the same deleted.


3>how and where can i see mutiple slaves running for single query with PARALLEL hint

Thanks
Tom Kyte
May 30, 2007 - 10:34 am UTC

hmm, how do you derive your #1 given your #2?

ops$tkyte%ORA10GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session enable parallel dml;

Session altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "parallelized"
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE
---------------------- ----------
queries parallelized            0
DML statements paralle          0
lized

DDL statements paralle          0
lized

DFO trees parallelized          0

ops$tkyte%ORA10GR2> delete /*+ parallel(ra 4) */ from t ra
  2  where object_name like 'Q%'
  3  and created >= to_date( '01-apr-2005', 'dd-mon-yyyy' )
  4  and last_ddl_time >= to_date( '31-dec-2005', 'dd-mon-yyyy' )
  5  /

4 rows deleted.

ops$tkyte%ORA10GR2> @mystat "parallelized"
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE
---------------------- ----------
queries parallelized            0
DML statements paralle          1
lized

DDL statements paralle          0
lized

DFO trees parallelized          1


would be one way, another would be to monitor the v$px* views from another session whilst this is running.


#3a - cannot address, for I have no idea how you measured and I'm a little suspect given you say #2.

Oracle 10g-Linux Reliability Issue

sam, July 23, 2007 - 8:50 am UTC

Tom,

I have got oracle 10g production database is running linux server and we got below errors everyday atleast 3/4
times and many times a week. That means we have to restart our job everytime.

ORA-12827: insufficient parallel slaves available
ORA-03114: not connected to ORACLE

we have got 300 data warehouse jobs runs everyday, data marts etc.

I donot see this kind of error on oracle 9i/Sun solaris Unix environment where we have got more than 3000 jobs are running everyday.


Thanks
Tom Kyte
July 23, 2007 - 2:57 pm UTC

[tkyte@dellpe ~]$ oerr ora 12827
12827, 00000, "insufficient parallel query slaves available"
// *Cause: PARALLEL_MIN_PERCENT parameter was specified and fewer than
//         minimum slaves were acquired
// *Action: either re-execute query with lower PARALLEL_MIN_PERCENT or
//          wait until some running queries are completed, thus freeing
//          up slaves
[tkyte@dellpe ~]$



you set it up, you are causing this - you have simply asked to have more parallel execution servers running than you have and you have said to us "do not run this if there are insufficient servers"

(this by the way has not a thing to do with the platform, everything to do with either different init.ora settings OR different concurrent loads on the systems)

you might consider not specifying a degree of parallelism - using the adaptive algorithms.

Sam, August 06, 2007 - 8:07 am UTC

Tom,

I have got below table T1,

Oracle 9.2.0.7, Unix sun solaris

ID ACT_DATE START_DATE FINISH_DATE

DD1 1/01/2006 2/01/2006 12:03:08 PM 2/01/2006 12:03:09 PM
DD1 2/01/2006 3/01/2006 8:30:59 AM 3/01/2006 8:31:01 AM
DD1 3/01/2006 4/01/2006 3:24:41 PM 4/01/2006 3:24:44 PM
DD1 4/01/2006 5/01/2006 5:15:51 PM 5/01/2006 5:15:52 PM
DD1 5/01/2006 6/01/2006 9:48:51 AM 6/01/2006 9:48:53 AM
DD1 6/01/2006 7/01/2006 10:53:50 AM 7/01/2006 10:53:51 AM
WW1 1/01/2006 2/01/2006 7:59:14 PM 2/01/2006 7:59:49 PM
WW1 2/01/2006 3/01/2006 10:05:21 PM 3/01/2006 10:05:51 PM
WW1 3/01/2006 4/01/2006 9:17:40 PM 4/01/2006 9:18:22 PM
WW1 4/01/2006 5/01/2006 9:20:48 PM 5/01/2006 9:21:25 PM
WW1 5/01/2006 6/01/2006 9:30:24 PM 6/01/2006 9:30:53 PM
WW1 6/01/2006 7/01/2006 7:59:27 PM 7/01/2006 8:00:03 PM


select round(((b.finish_date - a.start_date)*24),2) hours
from
(select start_date from T1 where act_date >= '01jun2006' and id = 'WW1') a,
(select finish_date from T1 where act_date >= '01jun2006' and id = 'DD1') b

when i am above query to calculate time difference in hours
i am getting invalid data number error on a.start_date

Basically i want to calculate time difference in hours where

finish_date is where id = DD1 and start_date is where id =WW1 from table T1 for each ACT_DATE.

There is one record for each ID = DD1 and ID= WW1 for each ACT_DATE.

Any help to get this query working.

Thanks
Sam
Tom Kyte
August 06, 2007 - 12:04 pm UTC

oh gosh:

act_date >= '01jun2006'

what the heck is that.


no create table
no inserts
no look

but start by using to-date on strings to convert them into dates using a valid format.

sam, August 07, 2007 - 7:45 am UTC

Accept apology,

create table t1(ID varchar2(10),ACT_DATE DATE, START_DATE DATE, FINISH_DATE DATE);

INSERT INTO T1 VALUES('DD1',TO_DATE('1/01/2006', 'dd/mm/yyyy'),to_date('2/01/2006 12:03:08 PM','dd/mm/yyyy hh24:mi:ss'),to_date('2/01/2006 12:03:09 PM','dd/mm/yyyy hh24:mi:ss') );


INSERT INTO T1 VALUES('DD1',TO_DATE('2/01/2006', 'dd/mm/yyyy'),to_date('3/01/2006 8:30:59 AM','dd/mm/yyyy hh24:mi:ss'),to_date('3/01/2006 8:31:01 AM','dd/mm/yyyy hh24:mi:ss') );


INSERT INTO T1 VALUES('DD1',TO_DATE('3/01/2006', 'dd/mm/yyyy'),to_date('4/01/2006 3:24:41 PM','dd/mm/yyyy hh24:mi:ss'),to_date('4/01/2006 3:24:44 PM','dd/mm/yyyy hh24:mi:ss') );


INSERT INTO T1 VALUES('DD1',TO_DATE('4/01/2006', 'dd/mm/yyyy'),to_date('5/01/2006 5:15:51 PM','dd/mm/yyyy hh24:mi:ss'),to_date('5/01/2006 5:15:51 PM','dd/mm/yyyy hh24:mi:ss') );


INSERT INTO T1 VALUES('DD1',TO_DATE('5/01/2006', 'dd/mm/yyyy'),to_date('6/01/2006 9:48:51 AM','dd/mm/yyyy hh24:mi:ss'),to_date('6/01/2006 9:48:51 AM','dd/mm/yyyy hh24:mi:ss') );


INSERT INTO T1 VALUES('DD1',TO_DATE('6/01/2006', 'dd/mm/yyyy'),to_date('7/01/2006 10:53:50 AM','dd/mm/yyyy hh24:mi:ss'),to_date('7/01/2006 10:53:51 AM','dd/mm/yyyy hh24:mi:ss') );


INSERT INTO T1 VALUES('WW1',TO_DATE('1/01/2006', 'dd/mm/yyyy'),to_date('2/01/2006 7:59:14 PM','dd/mm/yyyy hh24:mi:ss'),to_date('2/01/2006 7:59:49 PM','dd/mm/yyyy hh24:mi:ss') );


INSERT INTO T1 VALUES('WW1',TO_DATE('2/01/2006', 'dd/mm/yyyy'),to_date('3/01/2006 10:05:21 PM','dd/mm/yyyy hh24:mi:ss'),to_date('3/01/2006 10:05:51 PM','dd/mm/yyyy hh24:mi:ss') );


INSERT INTO T1 VALUES('WW1',TO_DATE('3/01/2006', 'dd/mm/yyyy'),to_date('4/01/2006 9:17:40 PM','dd/mm/yyyy hh24:mi:ss'),to_date('4/01/2006 9:18:22 PM','dd/mm/yyyy hh24:mi:ss') );


INSERT INTO T1 VALUES('WW1',TO_DATE('4/01/2006', 'dd/mm/yyyy'),to_date('5/01/2006 9:20:48 PM','dd/mm/yyyy hh24:mi:ss'),to_date('5/01/2006 9:21:25 PM','dd/mm/yyyy hh24:mi:ss') );

INSERT INTO T1 VALUES('WW1',TO_DATE('5/01/2006', 'dd/mm/yyyy'),to_date('6/01/2006 9:30:24 PM','dd/mm/yyyy hh24:mi:ss'),to_date('6/01/2006 9:30:53 PM','dd/mm/yyyy hh24:mi:ss') );


INSERT INTO T1 VALUES('WW1',TO_DATE('6/01/2006', 'dd/mm/yyyy'),to_date('7/01/2006 7:59:27 PM','dd/mm/yyyy hh24:mi:ss'),to_date('7/01/2006 8:00:03 PM','dd/mm/yyyy hh24:mi:ss') );



Question: below query gives invalid number error,

select round(((b.finish_date - a.start_date)*24),2) hours
from
(select start_date from T1 where act_date >= to_date('01/06/2006','dd/mm/yyyy') and id = 'WW1') a,
(select finish_date from T1 where act_date >= to_date('01/06/2006','dd/mm/yyyy') and id = 'DD1') b

when i am above query to calculate time difference in hours
i am getting invalid data number error on a.start_date

Basically i want to calculate time difference in hours where

finish_date is where id = DD1 and start_date is where id =WW1 from table T1 for each ACT_DATE.

There is one record for each ID = DD1 and ID= WW1 for each ACT_DATE.

Any help to get this query working.

Thanks
Sam
Tom Kyte
August 07, 2007 - 1:59 pm UTC

none of your inserts could have worked...

and I don't see the problem

you'll need to work on a better example (it could be SMALLER too I think!!)

ops$tkyte%ORA10GR2> INSERT INTO T1 VALUES('WW1',TO_DATE('6/01/2006',
  2  'dd/mm/yyyy'),to_date('7/01/2006 7:59:27 PM','dd/mm/yyyy hh:mi:ss am'),to_date('7/01/2006 8:00:03 PM','dd/mm/yyyy hh:mi:ss am') );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select round(((b.finish_date - a.start_date)*24),2) hours
  2  from
  3  (select start_date from T1 where  act_date >=
  4  to_date('01/06/2006','dd/mm/yyyy') and id = 'WW1') a,
  5  (select finish_date from T1 where  act_date >=
  6  to_date('01/06/2006','dd/mm/yyyy') and id = 'DD1') b
  7  /

no rows selected

To sam or mymot or pluto

Michel Cadot, August 07, 2007 - 2:15 pm UTC


You should also thank Maarten Hereijgers to have corrected your script and apologize for grabbing it without any word.
http://www.orafaq.com/forum/m/257026/102589/?srch=finish_date#msg_257026

Regards
Michel

It doesn't make sense

Bill B., August 07, 2007 - 5:15 pm UTC

The query makes no sense. It will produce a cartisian join. the sub-queries a and b are never joined.

[CODE]select round(((b.finish_date - a.start_date)*24),2) hours
from
(select start_date from T1 where act_date >= '01jun2006' and id = 'WW1') a,
(select finish_date from T1 where act_date >= '01jun2006' and id = 'DD1') b[/CODE]

If you have 100 rows in t1 that has an act_date greater then equal then 01-jun-2006 and 50 are id WW1 and 50 are id DD1, you will have a total of 2500 rows returned.



Freelist - Parallel server

sam, August 11, 2007 - 10:42 pm UTC

Hi,

we have got lots of job running(unix script embedded with SQL) everyday. Oracle 9i db is used on sun solaris.

Recently i have noticed some INSERT statements takes significant amount of time to inserts even 10000 rows. Also table has no index and insert statement is like this

INSERT INTO (.....) select /*+ parallel(e 3) parallel(a 3) */
from table1 e,
(select .....
from table 2
where <conditions>) a
where e.<field > = a. <field>
....

Now my question is, having few or more freelists help parallel query server (processes) to execute task quickly.

Freelist again is the empty block can be used directly from the list for INSERT .

Thanks
Tom Kyte
August 14, 2007 - 3:43 pm UTC

that is a serial insert. you might run a parallel query but I see nothing to indicate parallel dml.

and parallel dml would write ABOVE the high water mark, freelists would not be used.

when tuning, I typically try to find out why something is "slow", not come up with a list of hypothetical possible fixes that may or may not do something (and something means

a) goes faster
b) goes slower
c) stays the same)


so, find out why this is "slow", then we can talk

ORA-12853 insufficient memory for PX buffers:

sam, February 08, 2008 - 6:55 am UTC

Tom

we have oracle 10.2.0.2 64 bit edition, using Oracle RAC technology.

Since last 7 days our production system batch jobs failing due to above oracle error. DBA have contacted oracle and advised that its oracle bug and fixes will be coming in release.

Oracle support suggested to bounce db every 2 weeks until we will get new oracle version.

This Oracle 10g RAC is set up on DELL/Linux server.

IS there anything we can do to fix this problem.


Thanks

Row data multiplication

Raj, June 19, 2011 - 8:50 am UTC

Thanks a ton Tom for putting in this great code.
I am working on oracle 10g and want to achieve the same thing i.e. multiplying values of a column. Has there been any update in 10g, like a direct function, as in mySQL which gives PROD function?

Further, I used both your solutions, 9i and logarithmic, but I am still getting numeric overflow. What can be possibly wrong?
Pls do let me know if you need more i/p from my side.
Tom Kyte
June 20, 2011 - 10:02 am UTC

give an example for the numeric overflow.

If you want the "prod" function, you can use my product aggregate above, there is no builtin for this particular aggregate, but it is easy to do.

Multiplying column rows

Raj, June 20, 2011 - 2:07 am UTC

Hi Tom, further to my previous query I thought I rather put more clarity to it.
I tried running "SELECT EXP(12345678) FROM DUAL" and it gives numeric overflow. So how do you suggest to overcome this issue?
Further, I have only around 170 records and I tried the logarithmic approach as well as your procedure approach, it gives me the same error.
Tom Kyte
June 20, 2011 - 1:11 pm UTC

you exceeded the range of that function, use the product aggregate I supplied above.

If you have 170 numbers to multiply together - I can easily see them exceeding our ability (38 digits) to do that. You have a number that cannot be useful for anything there. Look at my example above -


scott@ORA9I.WORLD> select deptno, product(sal)
  2    from emp
  3   group by deptno
  4  /

    DEPTNO PRODUCT(SAL)
---------- ------------
        10   1.5925E+10
        20   2.3562E+16
        30   1.0153E+19


they get really big, really fast.

what are you really trying to do? what is your ultimate goal? what are you going to do with this number??



More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library