Float vs Number Datatype

8931

I am relatively new to Oracle. What is the underlying
differences to defining a table with column datatype float vs
number. Running the Oracle Reorg wizard on a table space gave
me the error message float datatype not supported. Is this a
bad idea to use float or not?

4162

FLOAT is a subtype of NUMBER supported in PL/SQL.

NUMBER is database column type supported by the Oracle database.

To store a FLOAT type in the databsse in a table in a column,
define the column as NUMBER.

You may fetch a NUMBER column type into the FLOAT subtype in
PL/SQL.

You may want to set the precision and scale for NUMBER to match
the FLOAT subtype but it is not necessary.

Good Luck....

8931

with this table definition will the float datatypes be stored as
number even though the sql text defines it as float.

CREATE TABLE Curve2Ds (
Name varchar (64) NOT NULL ,
XSensorOID NUMBER(12) NOT NULL ,
YSensorOID NUMBER(12) NOT NULL ,
FitType NUMBER(4) NOT NULL ,
ZAverage FLOAT NOT NULL ,
SumOfTheSquares FLOAT NOT NULL ,
MaxError FLOAT NOT NULL ,
MinError FLOAT NOT NULL
);

what is total number of bytes stored for a float and a number
and is difference in the way they are used in calculations.

Thanks.

4162

The FLOAT datatype will be stored as NUMBER(38) but will appear
as FLOAT(126).

The FLOAT datatype can be stored as variable binary precision
from 1 to 126, i.e. FLOAT(22).

Multiply the binary precision by 0.30103 to get decimal
precision. 126*0.30103 = 38.

So a FLOAT(22) would store at 22 * 0.30103 = 6.62266 decimal
precision.

FLOAT would be 126 binary or 38 decimal.

The size can be calculated from the equation below.

You will find extensive information on Oracle datatypes at.....

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/
server.817/a85397/sql_elem.htm#45443

PL/SQL datatypes are found at....

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/
appdev.817/a77069/02_funds.htm#10531

The FLOAT datatype is at....

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/
server.817/a85397/sql_elem.htm#45865

Size of stored Number is at.....

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/
server.817/a76965/c10datyp.htm#743

Internal Numeric Format

Oracle stores numeric data in variable-length format. Each value
is stored in scientific notation, with one byte used to store
the exponent and up to 20 bytes to store the mantissa. The
resulting value is limited to 38 digits of precision. Oracle
does not store leading and trailing zeros. For example, the
number 412 is stored in a format similar to 4.12 x 102, with one
byte used to store the exponent (2) and two bytes used to store
the three significant digits of the mantissa (4, 1, 2). Negative
numbers include the sign in their length.

Taking this into account, the column size in bytes for a
particular numeric data value NUMBER (p), where p is the
precision of a given value, can be calculated using the
following formula:

ROUND((length(p)+s)/2))+1

where s equals zero if the number is positive and s equals 1 if
the number is negative.

Zero and positive and negative infinity (only generated on
import from Version 5 Oracle databases) are stored using unique
representations. Zero and negative infinity each require one
byte; positive infinity requires two bytes.

There is no difference in how they are used in a calculation.

If you are dealing with engineering or scientific data and it's
analysis, you might consider using Oracle Objects
as shown in
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/
appdev.817/a76976/toc.htm

Good Luck...