Is there any benefit in limiting the number precision on a surrogate PK in Oracle?

Question

I'm reviewing some DDL that creates surrogate primary keys of type NUMBER(38). When I first looked at this I thought it was a problem because 10^38 is a HUGE number, much much bigger than the number of stars in the universe or all the grains of sand on earth. But I've come to learn that this doesn't matter in terms of how the numbers are stored. And there doesn't seem to be any other options than say using a float type which I'm not really ready to seriously contemplate. Here are some references about those facts:

• Is there any point in specifying the precision and scale of a number for a foreign key?
• Oracle integer to number conversion
• Oracle Datatypes Reference

Given that, it would seem that it's pointless to bother with declaring a limit on the size of the PK i.e. just use NUMBER (which apparently can hold 40 digits.) The only answer that I've seen is that you should do it for consistency. But for a surrogate PK, the only thing it needs to be consistent with is itself. What I mean is, if we set a limit, it's going to be something larger than what we would possibly reach so it seems completely pointless.

One piece of this I'm not sure about is whether the declaration could matter for indexing. I think it probably doesn't for the most common approaches but are there any indexing strategies that might take this limit into consideration or use more storage because the column is not limited?

Answer 1

Just use

my_key number not null

If you are generating the primary key using an Oracle sequence (PS: you should), you are guaranteed to get integers so you don't have to have Oracle checking precision, etc. if you specified number(7,0) to specify an integer with up to seven digits.

And then if you ever exceed 7 digits, not a fun day to fix that. As the links you've referenced above mention that Oracle only uses enough space to store the number, no need to worry about how much storage is being used; and therefore you don't have to concern yourself with indexing of the columns holding the primary and foreign keys.

The only time I would specify a fixed length integer would be if the primary key was an intelligent key such as a USA social security number, but intelligent keys are a bad idea too.

Answer 2

To my knowledge no index method or statistics depend on the potential range of the type. They all depend on the actual values and their current min and max. (This also applies to columnar compression store)

So this is not a reason for restricting your PK (and columns referencing it).

The only difference it makes is if you have a combined index with many large rows, because when you create those Oracle will check the potential maximum length of each column to see if it fits into the page induced restriction for maximum bytes. You will hit ORA-01450 in this case. But this is as DDL time not when you do DML.

However that's not a very strong reason as you should stay away from operating with such a wide index which is close to the maximum.

Answer 3

Is there any benefit in limiting the number precision on a surrogate PK in Oracle?

I don't think there is any benefit. Sequences can go up to precision 28 so if you would have any reason for imposing a limit, it would be NUMBER(28). However, the only reason you would limit a number is to implement a "logical constraint", so if someone tries to exceed it in your app, an error is thrown. Number storage works similar to VARCHAR2, in that it's a varying length data type and will consume up to 22 bytes of storage.

Example:

select vsize(999) from dual;

to check storage.

Oracle shrinks or grows it when necessary, so if your surrogate key goes only up to 3 digits ever, then Oracle would only use 3 bytes of storage for your NUMBER. Even if you chose NUMBER(28), it would be pointless, since Oracle would only allocate storage in the blocks as necessary. Storage is not preallocated such as with DATE or CHAR.

Are there any indexing strategies that might take this limit into consideration or use more storage because the column is not limited?

I don't think so, because of the storage methodology explained above. I'm not sure which 'exotic' indexing strategies you are referring to, but I know of B-tree (including index-organized tables), reverse-key, bitmap and function-based indexes.

B-trees: The upper blocks (branch blocks) of a B-tree index contain index data that points to lower-level index blocks. The lowest level index blocks (leaf blocks) contain every indexed data value and a corresponding rowid used to locate the actual row. Limiting numbers would not have any meaning here, as Oracle just allocates higher index values the further you go down the tree. So Oracle doesn't look at 'entire potential space' when creating new index blocks.

Bitmaps: take into account cardinality and contain nulls, so limiting precision would be irrelevant.

Conclusion

The only benefit to limiting number precision is for a logical constraint.

Answer 4

There is not performance benefit.

Limiting the precision is just a costraint. Similar when you limit the size of a string fiels.

The only benefit is "integrity". I mean: that field can be manipulated by an external application that can handle x digits.
This can sound odd if you are using a sequence, but from my experience, sometimes surrogate keys integrate a logic. For example the can start from a big number because positional digits have a meaning.