NUMBER data type…what harm can it do ?

There’s a somewhat sour discussion going on based attached to the video at

Whether you agree or disagree with the video or the comments, or (sadly) the animosity in them, it does lead to an interesting bit of investigation when it comes to data types with arbitrary precision, which was stumbled upon by a friend at work.

Let’s start with a simple comparison between two dates. In this case, I’ve just used times, because it still serves to demonstrate the anomaly.

SQL> SELECT (TO_DATE('14:03:15','hh24:mi:ss')- TO_DATE('14:02:25','hh24:mi:ss')) * 24 * 60 * 60 delta
  2  FROM   dual;


Thankfully, the database has come back with the right answer of 50 seconds. Of course, we might want to remember that result – so lets store it in a variable:

SQL> variable x number
SQL> begin
  2  SELECT (TO_DATE('14:03:15','hh24:mi:ss')- TO_DATE('14:02:25','hh24:mi:ss')) * 24 * 60 * 60
  3  into   :x
  4  FROM   dual;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> print x


So far so good…let’s now make the tiniest of changes. We’ll use a local PL/SQL variable

SQL> set serverout on
SQL> declare
  2    x number;
  3  begin
  4  SELECT (TO_DATE('14:03:15','hh24:mi:ss')- TO_DATE('14:02:25','hh24:mi:ss')) * 24 * 60 * 60
  5  into x
  6  FROM   dual;
  7  dbms_output.put_line(x);
  8  end;
  9  /

PL/SQL procedure successfully completed.

The use of arbitrary precision for ‘x’ shows how things can go a little bit awry. Similarly, lets look at what happens if convert the dates to simple ‘seconds past midnight’.

SQL> SELECT to_char(TO_DATE('14:03:15','hh24:mi:ss'),'SSSSS') d1,
  2         to_char(TO_DATE('14:02:25','hh24:mi:ss'),'SSSSS') d2
  3  FROM   dual;

D1    D2
----- -----
50595 50545

You might think that the calculation would the same, but when we insert those numbers into the equivalent calculation, we get a slightly different answer

SQL> exec dbms_output.put_line((50595/86400 - 50545/86400)* 24 * 60 * 60 );

Ultimately, this probably boils down to the fact that the certain division operations can never give a prefect answer in floating point arithmetic. In the example above, 50595/86400 yields the never ending result 0.58559027777777777777777…

Putting PL/SQL aside, there might also be hidden costs if you start using unbounded precision in your database tables. I always remember this example from Steve Adams many years ago.

SQL> create table T ( x1 number, x2 number(6,3));

Table created.

SQL> insert into T values ( 3*(1/3), 3*(1/3) );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from T;

        X1         X2
---------- ----------
         1          1

No strange decimals there…but then take look at how you stored that data

SQL> select vsize(x1), vsize(x2) from T;

---------- ----------
        21          2

Bottom line – things can go astray when you dont keep a handle on the appropriate precision to use for your data types.

2 thoughts on “NUMBER data type…what harm can it do ?

  1. You have an error in starting point: You see “50” just becase of sql*plus default format.

    Try this one:

    SQL> column delta format tm9
    SQL> SELECT (TO_DATE(’14:03:15′,’hh24:mi:ss’)- TO_DATE(’14:02:25′,’hh24:mi:ss’)) * 24 * 60 * 60 delta from dual;


    1 row selected.

    Sayan Malakshinov

  2. Storing the value 42 in the two columns will lead to the same storage size, so apparently it has something to do with the rounding involved in the expression 3 * (1/3)

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.