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:

  1. 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

    ReplyDelete
  2. 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

    ReplyDelete
  3. 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

    ReplyDelete
  4. 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

    ReplyDelete
  5. 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

    ReplyDelete
  6. Dear Laurent,

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

    Regards,

    Andrew

    ReplyDelete