Re: NUMBER(p) vs. INTEGER data type and CHAR(x) vs. VARCHAR2(x) in Oracle 10g and 9i - advantages and disadvantages

From: gym dot scuba dot kennedy at gmail <>
Date: Fri, 13 Jun 2008 02:49:25 GMT
Message-ID: <Vel4k.6914$LN.1339@trndny03>

<> wrote in message
> What are the main differences between NUMBER(p) and INTEGER for
> storing integer values? Is there any difference with space or
> performance?
> I understand using NUMBER(p) with a value for Precision only (no
> scale) gives you an integer, constrained by a max of p digits. Is
> INTEGER the same as NUMBER(38), since 38 is the max precision?
> I've also heard it said that Oracle Corp recommends, or once
> recommended, using the NUMBER type with no precision or scale
> specified; this way it accepts whatever type of number you throw at it
> exactly as it is. I can see how this would be bad, e.g. if you intend
> to hold only integers this would allow someone to enter floating point
> numbers. Is it only good to use NUMBER with no P + S when you expect
> floating point values, but don't know how many decimal places you
> might need?
> Also, these days, is there any real advantage to using something like
> CHAR(5) vs. VARCHAR2(5) in terms of storage and performance? Seems
> with CHAR, you'll always use 5 characters regardless of what you enter
> (zeros padded when < 5 chars). But with VARCHAR2(5), you'll only store
> as many characters as are entered.
> What I'm really after--is there any advantage these days to using the
> CHAR data type? Seems like VARCHAR2 offers more flexibility *if* the
> length of values in a column may increase or decrease in the future,
> where CHAR is best if one suspects there will always only be X
> characters for values in a field. So is it a stylistic thing or a
> domain thing in choosing one over the other?

There are numbers in the Oracle DB, the definitions of the others are types of number(p,s) so they are stored the same. The exception is numbers with IEEE precision (reduced precision from Oracle Numbers, so ieee operations will work the same on Oracle as other scientific programs.)

I can't think of a reason to use char. I use varchar2. Jim Received on Thu Jun 12 2008 - 21:49:25 CDT

Original text of this message