Forum Stats

  • 3,752,055 Users
  • 2,250,452 Discussions
  • 7,867,704 Comments

Discussions

Float vs Number Datatype

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?

Comments

  • 4162
    4162 Member Posts: 202
    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
    8931 Member Posts: 4
    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
    4162 Member Posts: 202
    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...
This discussion has been closed.