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?