3 Replies Latest reply on Oct 18, 2001 11:41 PM by 4162

    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?
        • 1. re:Float vs Number Datatype
          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....
          • 2. re:re:Float vs Number Datatype
            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.
            • 3. re:re:re:Float vs Number Datatype
              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...