Re: Number vs. Number(p,s)

From: Spencer <spencerp_at_swbell.net>
Date: Wed, 16 May 2001 00:03:43 -0500
Message-ID: <3RnM6.105$tC2.9162@nnrp1.sbc.net>

i'd recommend using NUMBER(p,s) if you can. if this is not possible, consider using datatype FLOAT, before resorting to the dreadfully generic NUMBER.

this is especially true if the column is modified from a Pro*C program that uses a (C datatype) double or float as the bind variable.

On HP-UX, for example, (C datatype) float provides 23 binary digits of precision, which is roughly equivalent to 7 decimal digits of precision. A double has even more binary digits (51?) of precision...

The problem with this is that when a SQL statement is processed from a Pro*C application, the value of a bind variable of datatype float or double is translated from the native binary representation to the Oracle representation, which results in a decimal value has a LOT more decimal digits than are required to uniquelly represent the value.

when this value is assigned to a column of type NUMBER, Oracle stores ALL of the decimal digits, whether they are truly significant or not. when the same value, however, is assigned to a column of type NUMBER(p,s) or FLOAT(p), then unnecessary digits are discarded, and not stored.

The extra storage required for these extraneous digits can cause a significant increase in the size of a row, which can have a significant impact on performance.

others, of course, may see things differently that i do. but then again, they may not have had first hand experience with columns defined as NUMBER being populated from Pro*C apps using C-datatype double as bind variables.

the dump() built-in function can provide you with some insight as to what Oracle actually stores in a NUMBER column. i created test tables, with columns of several different types in order to test "insert", and "update" operations as well as "select" statements with "where" clauses referencing the various NUMBER columns.

inserting a value of '123' into any of these datatypes:

NUMBER(38)
NUMBER(38,2)
NUMBER(7,4)

FLOAT(23) results in the exact same thing being stored.

the differences between these datatypes becomes apparent when you insert (in some cases, i should qualify that with "attempt to" insert) values like:

'1.515151515151515151515151515'
'1233456790.12'
'0.0000123456'

HTH "Martin Haltmayer" <Martin.Haltmayer_at_0800-einwahl.de> wrote in message news:3B0146FB.294A517C_at_0800-einwahl.de...
> There is no memory advantage whatsoever because the memory needed only
 depends
> on the value stored but not on its definition as you can easily see by the
> dump() function.
>
> Martin
>
>
>
> VWP914 wrote:
> >
> > I am trying to decide whether I wan't to use floating point (NUMBER) or
 fixed
> > point
> > (NUMBER(p, s)) datatypes for my database design. Apart from having more
> > control with the fixed point option, is there any advantage memory wise?
> > For instance, if I know that I have a number that will never be greater
 than,
> > say
> > 99999.99, should I bother and assign it to NUMBER(7,2), or just go ahead
 and
> > make it a NUMBER with no scale or precision declared?
> >
> > I just don't see the advantage of specifically declaring a precision and
 scale,
> > when you can just make it a floating point. Especially, when you
 consider that
> > you are making a guess (albeit an edjucated guess) as to the scale and
> > precision of you column. If this guess turns out to be incorrect, and
 say you
> > really should have chosen NUMBER(8,2), for example, your application
 will give
> > you an error.
> >
> > Any advice here would be appreciated.
> >
> > Thomas
>
Received on Wed May 16 2001 - 00:03:43 CDT