Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Wouter.

Asked: September 29, 2021 - 7:15 am UTC

Last updated: October 05, 2021 - 3:55 am UTC

Version: 19.0.0.0.0

Viewed 1000+ times

You Asked

Hello 'Tom'
This question is regarding the NUMBER data type in an Oracle table. Or in PL/SQL and so on.

I used to define a number-column always like NUMBER(10) or NUMBER(12,2) or NUMBER (1).
Nowadays I see more and more that the columns are defined as only NUMBER. The use of length and/or precision is skipped.

My question is:
What should be the best practise?
Go further with NUMBER data type or try to make sense out of the definition of the column and define like NUMBER (4) or NUMBER (10) or NUMBER (20,3) etc. Obviously if I want to add an implicit constraint to the column, the NUMBER(4) is a logical use.

Within the answer how and why to use the NUMBER data type, also PGA - usage / performance / cursors / collection's / index- and tablesize's etc should be taken into account.

Thanks in advance

Greetings Wouter

and Connor said...

Yeah I'm not a fan of NUMBER unless you actually need that level of precision.

Especially when you go outside the realm of integers. If you were storing (for example) dollars and cents, then you get into these kinds of messes

SQL> create table t ( x1 number, x2 number(6,2));

Table created.

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

1 row created.

SQL> select * from t;

        X1         X2
---------- ----------
.333333333        .33

SQL> select dump(x1), dump(x2) from t;

DUMP(X1)
----------------------------------------------------------------------------------
DUMP(X2)
----------------------------------------------------------------------------------
Typ=2 Len=21: 192,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34,34
Typ=2 Len=2: 192,34


You now have to deal with fictional decimal points and you've gone from 2 bytes to 21 bytes potentially.

I view datatypes as just another form of data correctness. If I'm storing (say) highway speed limits, then NUMBER(3) means that I have guaranteed that any erroneous speed limit of 1000+ will be rejectged. I am always lowering the chances of a data corruption.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

INTEGER

Jonathan Taylor, September 30, 2021 - 3:01 pm UTC

I often see NUMBER where an "infinite" integer is required - e.g. a sequence-backed ID column.

In which case, INTEGER is a better datatype, NUMBER(10) etc are artificially constrained. I think also NUMBER (*,0) is the same.
Connor McDonald
October 04, 2021 - 4:45 am UTC

Agree

Is integer even a datatype?

Peter G, October 04, 2021 - 11:01 pm UTC

<pre>
SQL> create table t (x number(38), y integer, z number(*,0));

Table created.

SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(38)
Y NUMBER(38)
Z NUMBER(38)

SQL> drop table t purge;

Table dropped.

SQL>
</pre>
Connor McDonald
October 05, 2021 - 3:55 am UTC

It is synonymous with NUMBER with 0 decimal places.

https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF00213
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.