Oracle primary keys: NUMBER vs NUMBER(7,0)

Question

Is there any benefit to specifying the precision on the PK? Is 7,0 sufficient, given that there will probably never be more than a few thousand records?

Any dangers to not specifying the precision?

Answer 1

NUMBER(7, 0) just constrains the domain of values.

Their internal represenations do not differ:

CREATE TABLE t_pk (col1 NUMBER(7, 0) NOT NULL, col2 NUMBER(38) NOT NULL)

INSERT
INTO    t_pk
VALUES  (9999999, 9999999)

SELECT  DUMP(col1), DUMP(col2)
FROM    t_pk

DUMP(col1)                        DUMP(col2)
---                               ---
Typ=2 Len=5: 196,10,100,100,100   Typ=2 Len=5: 196,10,100,100,100

In Oracle, the NUMBERs are stored as centesimal digits of the numeric value normalized to 0.01 <= N < 1 and prepended with the exponent.

In the example above:

• 196 is the 192-based exponent (4).
• 10 is decimal 9
• 100's are decimal 99's

The whole number reads in decimal as 00.09 99 99 99 * (100 ^ 4) = 9,999,999

The more digits are required to satisfy the precision requested, the more of them will be stored of course.

When you insert a precise value into a less precise column, it just gets rounded to column's precision and is stored rounded.

Therefore, it is safe performance-wise to declare you column NUMBER(38), since it implies no overhead over NUMBER(7, 0) (for the numbers that fit both types).

However, if your PRIMARY KEYs are integer by nature, you better specify precision as 0 to make sure no fractional value ever gets to your table.

Update:

@Mac also pointed that the clients may rely on the column datatype to figure out the values domain.

If your application expects an INT32, you should make your number a NUMBER(9) or below (or whatever type your client considers to be convertable to Int32).

Answer 2

On the database side of the problem, I have nothing to add to Quassnoi's answer.

But it is worth noting that it may as well have an impact on applications that access the database (or, to be more accurate, on the developers of these applications). In .NET for instance, if you get an IDataRecord including your primary key (using ODP .NET), a call to GetInt32 will miserably fail when your column is defined as NUMBER(38) and succeed when defined as NUMBER(7) (even when when the value is in the correct range).

Answer 3

If you're not expecting more than, say 100K records in the table, if you specify the PK with N(7,0) you'll get an early warning if some runaway process ends up overflowing the PK. If you specified it with N(38) the warning will not appear so early, perhaps.

I'd always err on the side of constraining sizes to the smallest expected for the "life of the product", with a reasonable margin for error.