Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle internal NUMBER type
Dave <David.W.Spencer_at_elcaro.moc> wrote in message news:<3DC7AD14.1000705_at_elcaro.moc>...
> Jaroslaw Gramacki wrote:
> > On Tue, 5 Nov 2002 12:09:34 +0300, "Vladimir M. Zakharychev"
> > <bob_at_dpsp-yes.com> wrote:
> >
> >
> >>Different processors implement floating-point arithmetic differently.
> >>Besides, Oracle may be ran on a platform with no FPU unit whatsoever.
> >>At the same time, Oracle guarantees portability of values with precision
> >>up to 38 decimal digits across all supported platforms. No wonder they
> >>implemented floating-point math that way...
> >>
> >
> > OK, but ...
> > Say you have a looooot of data in your database and need to perform
> > math intensive calculations on them.
> > What would be better - perform all math (algorithms) in e.g pl/sql or
> > outside oracle in e.g C language but in the latter you are thrown back
> > on float, double or long double.
> > And what if you perform some calculations in oracle and some outside?
>
> PL/SQL is well optimised for database access but not for number
> crunching, although native compilation partially addresses this. There
> are a variety of ways you can proceed; you can write a Pro*C or OCI
> program, or use a Java Stored Procedure as Java is better suited to
> number crunching than PL/SQL, or use external procedures to perform the
> maths.
>
> There are a variety of high precision libraries available for C that
> will be able to handle the precision within Oracle. The basic data
> types are not sufficient (and it's not just the FP stuff - an int can't
> hold a NUMBER, although a 128 bit data type, if you've got one, can hold
> 10^38.)
>
I think the question remains, whether there is a mismatch (sometimes
called an "impedance") between Oracle's NUMBER and the format that is
used outside Oracle, which for floating-point would almost certainly
be IEEE 754. The potential difficulties, as I see them, would be:
(a) the range of numbers is different so attempts to store and retrieve
could result in errors; (b) results which require rounding may differ
because Oracle will in fact use "fixed point" arithmetic for addition
and subtraction, and that will differ from what a compiler does with a
number that's declared as float/double. Incidentally: for our recent book
we were looking at internal formats for number storage with eight DBMSs,
and Oracle was the only one that operated this way, the other seven
(DB2, Informix, Ingres, InterBase, SQL Server, MySQL, Sybase) all depend
on 16-bit binary for SMALLINT, 32-bit binary for INTEGER, and so on.
Peter Gulutzan
www.ocelot.ca
Co-Author of SQL Performance Tuning (http://www.ocelot.ca/tuning.htm)
Received on Tue Nov 05 2002 - 19:21:35 CST