3

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:

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?

  • you haven't mentioned anything about how/where/by-whom/why this column may be accessed; I'm thinking an explicit declaration will make life a bit easier for those coming behind you who need to write code that retrieves the column (plsql developers, front-end apps coded in a slew of different languages w/ varying datatypes, etl tools, defining remote proxy tables, ddl extraction/modeling tools); the flip side is that everyone will just have to dig into the schema to determine the size of the datatype – markp Aug 26 '17 at 14:57
  • @markp The column as noted in the question is a surrogate key. It has no meaning and will be used to join to dependent tables. When you say an explicit declaration, explicitly what? Are you saying NUMBER(38) is better than NUMBER? – JimmyJames Aug 28 '17 at 14:56
  • No, unless you have a combined index exceeding the maximum byte length limit it does not help to declare shorter types,- it does not make the storage or index more efficient. The column will take as much space is needed to represent the actual decimals, somit you only use small numbers you do not waste extra space. (Unless you use a different, shorter, maybe fixed length type). Same is true for calculating the statistic buckets, they depend on actual min/max values. – eckes Aug 28 '17 at 19:39
  • The keys are also have to be consistent with FK reference columns. – eckes Aug 28 '17 at 19:42
  • @eckes Thanks. I'm not an expert in this area and what you are saying seems correct in general. I'm just unsure about all the various indexing options as I note in my comment on Mark Stewart's answer. – JimmyJames Aug 28 '17 at 20:38
2
+50

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.

0

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.

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.

  • BTW: with MSSQL you can combine columns with potential maximum length exceeding the index length maximum. That's handy if you have types defined with too generous maximum length but it is very tricky if you actually happen to exceed the length. (However you'd not see those kind of problems with limited length numbers) – eckes Aug 30 '17 at 18:33
3

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.

  • 2
    "And then if you ever exceed 7 digits, not a fun day to fix that." Exactly where my head is at. I've experienced this first hand. I'd much rather have some one come to me and tell me that performance is slowing or something than tell me the app is dead in the water. – JimmyJames Aug 28 '17 at 20:33
  • Are you quite certain that the index is not potentially going to look at the entire potential space of the numbers? A guy on our team did some testing and found that it didn't seem to matter for the standard B-tree index but what about some of the other more exotic indexing strategies? – JimmyJames Aug 28 '17 at 20:35
  • Not absolutely certain, but the OP's question implied a simple FK arraignment. – Mark Stewart Aug 29 '17 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.