Tuesday, August 07, 2012

Natural Logarithms



My last post was about logarithms. Laurent Schneider, the author of the book advertised above, added a comment to it suggesting how I could use natural logarithms. These are logarithms to the base of e, which is an irrational constant roughly equal to 2.7 (see below for a more accurate approximation). I used his idea to create this example, which was tested on Oracle 11.2.

Oracle has the sum function to add all the values in a column. This example shows how to multiply them instead. I’m not sure yet why you might want to do this but, as soon as I think of a reason, I will come back to the post and update it.

First I set numwidth to a high value so that Oracle would show lots of significant figures:

SQL> set numwidth 40
SQL>

Then I created a table and stored some numbers to be multiplied:

SQL> create table andrew (col1 number)
  2  /

Table created.

SQL> insert into andrew values (1)
  2  /

1 row created.

SQL> insert into andrew values (2)
  2  /

1 row created.

SQL> insert into andrew values (3)
  2  /

1 row created.

SQL> insert into andrew values (4)
  2  /

1 row created.

SQL> insert into andrew values (5)
  2  /

1 row created.

SQL> insert into andrew values (6)
  2  /

1 row created.

SQL> insert into andrew values (7)
  2  /

1 row created.

SQL> insert into andrew values (8)
  2  /

1 row created.

SQL> insert into andrew values (9)
  2  /

1 row created.

SQL> insert into andrew values (10)
  2  /

1 row created.

SQL>

Next I used the ln function to calculate the natural logarithm of each number. I added all these logarithms together. Then, I used the exp function on the result. This raises e to the power of the sum just calculated and gives the result of multiplying all the original numbers together:

SQL> select exp(sum(ln(col1))) from andrew
  2  /

                      EXP(SUM(LN(COL1)))
----------------------------------------
3628800.00000000000000000000000000000011

SQL>

Finally I calculated the same result by multiplying the numbers in the normal way. If the table had several hundred rows, this would have been extremely difficult. Note that there is a very small difference between this and the first result above. This is because logarithms are only approximations:

SQL> select 1*2*3*4*5*6*7*8*9*10 from dual
  2  /

                    1*2*3*4*5*6*7*8*9*10
----------------------------------------
                                 3628800

SQL>

The query below shows the value of e which Oracle used with a numwidth of 40 i.e. 39 significant figures, assuming that you do not count the decimal point as a significant figure:

SQL> select exp(1) from dual
  2  /

                                  EXP(1)
----------------------------------------
2.71828182845904523536028747135266249776

SQL>

No comments: