Thursday, July 26, 2012

Logarithms


This example was tested on Oracle 11.2. You can display logarithms as follows. Select log(a,b) from dual where a is the base and b is the number you want the logarithm of:

SQL> select log(2,8) "Log base 2 of 8" from dual
  2  /

Log base 2 of 8
---------------
              3

SQL> select log(9,3) "Log base 9 of 3" from dual
  2  /

Log base 9 of 3
---------------
             .5

SQL> select log(1000000,100)
  2  "Log base 1000000 of 100" from dual
  3  /

Log base 1000000 of 100
-----------------------
             .333333333

SQL>

The base must be a positive number > 1:

SQL> select log(0,1) "Log base 0 of 1" from dual
  2  /
select log(0,1) "Log base 0 of 1" from dual
           *
ERROR at line 1:
ORA-01428: argument '0' is out of range

SQL>

The number must be positive:

SQL> select log(5,-3) "Log base 5 of -3" from dual
  2  /
select log(5,-3) "Log base 5 of -3" from dual
             *
ERROR at line 1:
ORA-01428: argument '-3' is out of range 

SQL>

To multiply 2 numbers, take the logarithm of each and add them together. Then raise the base to the power of the sum to get the answer:

SQL> select power(3,log(3,6)+log(3,7))
  2  "Should be 42" from dual
  3  /

Should be 42
------------
          42

SQL>

6 comments:

laurent schneider said...

The base must be a positive number > 1

why this???

SQL> select log(0.1,0.01)from dual;

LOG(0.1,0.01)
-------------
2

Laurent Schneider said...

to multiply 2 numbers, take the logarithm of each and add them together
I use this sometimes to get the product via sum


SQL> select exp(sum(ln(sal))) from emp;

EXP(SUM(LN(SAL)))
--------------------------------------------------
3809704805156250000000000000000000000606000000

Laurent Schneider said...

but maybe you found some rounding issue...

SQL> select 800*1600*1250*2975*1250*2850*2450*3000*5000*1500*1100*950*3000*1300 from dual;

800*1600*1250*2975*1250*2850*2450*3000*5000*1500*1100*950*3000*1300
-------------------------------------------------------------------
3809704805156250000000000000000000000000000000

Andrew Stuart Reid said...

Dear Laurent,

3 excellent comments as always. I will be away from the Internet for a week or so but I will reply when I return.

Regards,

Andrew

Andrew Stuart Reid said...

Dear Laurent,

I'm back. When I wrote this example, I was working from some old Oracle 9 documentation and I read the following:

"The base m can be any positive number other than 0 or 1 ..."

I assumed incorrectly that the base had to be a whole number and reworded Oracle's documentation to say that the base had to be > 1.

As you pointed out, this was wrong.

I will try harder in future!

Regards,

Andrew

Andrew Stuart Reid said...

Dear Laurent,

I used your idea with ln and exp in the next post.

Regards,

Andrew