Home / Questions / 124818
Accepted
Goerman
Asked: 2020-12-15 08:39:51 +0800 CST 2020-12-15 08:39:51 +0800 CST

How does precision work in Float data type in Oracle?

  • 772
-- Se tiene definida la siguiente tabla
CREATE TABLE MY_TABLA
(
   ID        NUMBER (3),
   NUM_TAB   FLOAT (3)
);

INSERT INTO MY_TABLA
     VALUES (24, 24);

SELECT ID, NUM_TAB FROM MY_TABLA;
-- 24   20
```
  • Why NUM_TABwas it inserted 20, if it was sent 24?
  • In what situations is it advisable to change the precision of a field FLOATby default?
sql

1 Answers

  1. Best Answer

    How does precision work in Float data type in Oracle?

    Although not very clear, the explanation is in the documentation here: FLOAT Data Type .

    When you define a column with FLOAT(n), as you may have noticed, nit does not express the precision in number of decimal digits, as it happens with the type NUMBER(n)for example. Rather, it nrepresents the precision in number of bits . And even that is not entirely true, the fullest explanation is much more complicated. You can find more details in this answer on SO if you want to go deeper into the topic beyond my simplistic explanation.

    A rough way to understand the value nis that for every 3 bits (give or take) you set to the type FLOAT, this gives you one more digit of allowed precision. A simple formula but it is only an approximation is:

    precisión en dígitos = (n / 3) + 1

    Why NUM_TABwas it inserted 20, if it was sent 24?

    It's because when specifying a 3bit precision ( FLOAT(3)), this is actually equivalent to a single digit of precision ( 1 = (3 / 3) + 1). In order to express 24, you need at least 2 digits of precision, so you would have to declare the type FLOAT(n)with a value nof at least 4.

    Now, to understand why the value was replaced 24with 20, you first need to understand that when we talk about precision digitsFLOAT , we 're actually talking about significant digits when it comes to type . In other words, when we say that it FLOAT(3)is equivalent to a single digit precision, this does not mean that only the numbers from -9 to 9 can be expressed. Rather it means that, whatever value is expressed, it can only include a maximum of 1 significant digit.

    For example, all of the following numbers can be expressed with a FLOAT(3)without problems, because they only have 1 significant digit:

    • 2 (2e0)
    • 30 (3e1)
    • 400 (4e2)
    • 5000 (5e3)
    • 60000 (6e4)

    In contrast, the following numbers, although smaller in some cases, require more significant digits:

    • 24 (24e0) (2 significant digits: 24)
    • 480 (48e1) (2 significant digits: 48)
    • 5001 (5001e0) (4 significant digits: 5001)
    • 60300 (603e2) (3 significant digits: 603)

    In all of these cases, what the type FLOATdoes is replace the input with the closest approximate number that can be expressed within the number of significant digits allowed. For example, taking the examples above again, they are substituted like this:

    • 24 --> 20 (2e1)
    • 480 --> 500 (5e2)
    • 5001 --> 5000 (5e3)
    • 60300 --> 60000 (6e4)

    In what situations is it advisable to change the precision of a field FLOATby default?

    In theory you would use it when you want to control the maximum number of bytes the column can consume, and if you are willing to sacrifice value precision to do so.

    But Oracle itself recommends that, if you have that goal, it's better to use types BINARY_FLOATthat BINARY_DOUBLEalready have clearly defined byte boundaries, rather than messing around FLOAT(n)where the meaning and effect of the value nis very confusing.

    And, of course, if you don't intend to allow Oracle to silently substitute values ​​when the precision exceeds a certain limit, then it's better to use the type NUMBER(n)that is more strict in its use, and that throws an error when a value exceeds the defined limit, instead of replacing it with an approximation.

    • 5