Oracle: why should one specify the width of a number column?
VTR250
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!
Roy Hann
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
Philipp Post
This looks a bit oversized. The standard for this (ISO 3166 alpha-2)
is made up by two letters, so country_cd CHAR(2) would look more
appropriate. If you are using the numeric or the alpha-3 version of
ISO 3166 you would have CHAR(3)
http://www.iso.org/iso/english_country_names_and_code_elements
http://en.wikipedia.org/wiki/ISO_3166-1
brgds
Philipp Post
astaroth
> 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,
That's actually incorrect. Oracle stores numbers in variable length
format. Specified size is only a column constraint (which may or may not
be a good choice).
http://www.jlcomp.demon.co.uk/faq/num_size.html
--
astaroth
VTR250
er... the way I'm reading your post, since you say NUMBER type is in
fact a varying length data type on disk you must be picking at 3
digits and 4 digits requiring the same amount of space (by number(3)
or number(4) I mean a three or four-digit number respectively stored
in a table column).
The following information appears in the page you linked to, but it's
clearer on the Ask Tom page (http://asktom.oracle.com/pls/asktom/f?
p=100:11:0::::P11_QUESTION_ID:1856720300346322149)
<snip>
X Y VSIZE(X) VSIZE(Y)
---------- ---------- ---------- ----------
99 100 2 2
9999 10000 3 2
100 takes up less space than 9999 -- so it is correct: you really do
save 1 byte. If I'm reading your post wrongly, what specifically is
incorrect in the original statement? This is a digression, however,
because we're all in agreement that you don't need to save the byte,
but the size limit is a good idea because it helps stop junk data
getting in.
Thanks.
astaroth
> 100 takes up less space than 9999 -- so it is correct: you really do
> save 1 byte. If I'm reading your post wrongly, what specifically is
> incorrect in the original statement? This is a digression, however,
> because we're all in agreement that you don't need to save the byte, but
> the size limit is a good idea because it helps stop junk data getting
> in.
Two things:
100 takes up less space than 9999 - that's right, because 100 has less
significant digits.
And what I did disagree with:
>> > 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,
A NUMBER(4) for a year will take exactly the same amount of bytes of
storage than NUMBER! Precision and scale are only constraints that
prevent some values from being stored, but do not affect how much
space you need to store it.
9999 takes 3 bytes whether stored in NUMBER, NUMBER(4), NUMBER(10) or
NUMBER(12,2) - but Oracle will just reject this value for NUMBER(3)
column.
--
astaroth
VTR250
I understand: an number x takes requires the same number of bytes to
store in a column regardless of whether the column has been defined
with a size.
create table t ( x number, y number(4) );
insert into t values (999, 999);
select x, y, vsize(x), vsize(y) from t;
X Y VSIZE(X) VSIZE(Y)
---------- ---------- ---------- ----------
999 999 3 3
So my original comment "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"
is wrong.
Thanks for the correction.