Re: Oracle internal NUMBER type

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

There's no question about it in my mind - of course there is. But working with floating point numbers in binary is not an exact science anyway - if you try to store 0.1 in a non-infinite binary space you lose precision as the binary expansion of 0.1 is repeating (just as 1/3 cannot be represented exactly with a finite number of decimal digits.)

Also when working with floating point numbers in a computer, results that require rounding will inevitably introduce errors and the common solution to this is to work to a higher accuracy than is needed so that rounding errors and other artifacts of the conversion between bases are insignificant. Other solutions come to mind, invoking thoughts of FRACTINT where complex floating point calculations are handled exactly with integers. If you need to work with exact floating point numbers, then you need an exact format (surds?) for which I doubt any of the eight cater so Oracle is no different in this regard. Working to a higher accuracy than needed will also minimise problems converting between IEEE 754 and Oracle NUMBER.

I'm not familiar with IEEE 754 but I can't think of a good reason why it shouldn't be possible to store data in that format directly in Oracle using, for example, a BLOB, or as a hex string in a VARCHAR2. Ok, Oracle may not recognise it as a NUMBER but then you can extend Oracle through external procedures or Java stored procedures so that it can appear to handle IEEE 754 directly.

For the book presumably you also considered problems outside the computer domain where number ranges fit conveniently into 8, 16 or 32 binary digits (i.e. why you would want to store numbers in a database in the first place). Are there any? These values are programmer friendly, but are they appropriate for real world issues? Decimal is the first number system we learn, followed shortly by bases 12 and 60 for telling the time (and all this decimalisation has removed the other interesting bases for weights, LSD, etc), with binary, octal and hex only following much later.

You can of course store 8-bit numbers in Oracle; you probably already know that it is no more complex than creating a NUMBER(3) column and an insert/update trigger which throws an error when you attempt to store a number that doesn't fit into 8 bits. Ditto for 16 bits, NUMBER(5); 32 bits, NUMBER(10) etc. So if you really need programmer friendly integer values it isn't difficult.

-- 
Email address munged to avoid SPAM
Reverse the last two parts to reply directly