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.

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.

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 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.

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.