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

From: Mark D Powell <>
Date: Fri, 13 Jun 2008 07:05:59 -0700 (PDT)
Message-ID: <>

On Jun 12, 10:49 pm, "gym dot scuba dot kennedy at gmail" <> wrote:
> <> 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- Hide quoted text -
> - Show quoted text -

From the 10g Concepts manual >>
When you specify numeric fields, it is a good idea to specify the precision and scale

Also with 10g Oracle has introduced native machine format data types into the database: BINARY_FLOAT and BINARY_DOUBLE. As far as I know a column declared as integer will still be stored as a number as in prior versions but that may be subject to change going forward though I suspect Oracle will provide a different name to the data type for backward compatiability.

HTH -- Mark D Powell -- Received on Fri Jun 13 2008 - 09:05:59 CDT

Original text of this message