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

From: VTR250 <>
Date: Mon, 7 Sep 2009 23:05:35 -0700 (PDT)
Message-ID: <>


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! Received on Tue Sep 08 2009 - 01:05:35 CDT

Original text of this message