Re: Oracle: why should one specify the width of a number column?

From: Roy Hann <specially_at_processed.almost.meat>
Date: Tue, 08 Sep 2009 03:59:03 -0500
Message-ID: <WOqdnXQEC5xKgTvXnZ2dnUVZ8gKdnZ2d_at_pipex.net>



VTR250 wrote:

> Hi,
>
> When defining numeric columns, for the last 10 years in Oracle I have
> NEVER specified the size for a number unless I am specifically
> required to do so (I think I've had a requirement to do it twice, but
> I can't find the example now).
>
> I have always supposed that the default size and range (and therefore
> the usual internal storage method) is probably going to be the 'best'
> choice. 'Best' is defined as least likely to cause software problems
> later on. Even though I know a NUMBER(4) for a year, or a NUMBER(3)
> for a person's age actually takes less bytes of storage than NUMBER, I
> learned early on in my career (Fortran/Mainframe) that playing funny
> games with non-standard data widths CAN cause problems and we don't
> need to save every last byte.
>
> A BA at my company has just mandated that all numbers in all tables
> must be sized at 10 digits, not just key fields. For example STAFF_ID
> NUMBER(10) or COUNTRY_CD NUMBER(10).
> The reason I have been given is they think the Oracle optimizer will
> trip up if numbers sizes do not match.
>
> Can anybody provide me with some insight on this? Should I have been
> sizing my number columns and not just take the default? Why?
>
> PS. I'm currently on a Solaris 64-bit system!
>
> Thanks!

Well first off we should all agree that the 10-digit number standard is eye-poppingly imbecilic. If the BA really thinks they have identified such a serious bug in Oracle they should raise an issue with tech support. They pay enough for it, so use it.

Thinking a little wider, there is no need to be concerned about conserving a few bytes per row. Storage is so close to free that you would need to be dealing with trillions of rows per table before the marginal storage cost could become an issue.

I think a much bigger concern is that the representation of the value should match its intended use as nearly perfectly as possible. I would, for example, question the wisdom of ever storing a value consisting of digits upon which it is meaningless to do arithmetic. Credit card numbers, numeric country codes, etc. can never be manipulated arithmetically so don't expose them to that risk. The values may be represented by decimal digits but they are not numbers in any sense, they are numeric strings.

In the case of values that really are numbers and properly subject to arithmetic, there are serious limits to how well we can represent them in any SQL DBMS. For instance, a weight will usually be represented by a number, and it makes sense to sum weights. But it would be an error to multiply two weights. On the other hand it would be sensible to multiply a weight by a count. It is silly to think SQL DBMSs will ever handle those important nuances, so know when to stop this line of reasoning or you will go mad.

For practical purposes, I would just suggest that you look at the (very complex) SQL rules about how precision and scale work when you do arithmetic on NUMBERs, and make sure that you choose suitable declarations to avoid nonsensical arithmetic results. I'd also suggest applying suitable check-constraints too.

-- 
Roy
Received on Tue Sep 08 2009 - 03:59:03 CDT

Original text of this message