Oracle: why should one specify the width of a number column?
Date: Mon, 7 Sep 2009 23:05:35 -0700 (PDT)
Message-ID: <978cd204-2893-44af-80b4-5c89abc2252a_at_f20g2000prn.googlegroups.com>
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!