Re: NUMBER(p) vs. INTEGER data type and CHAR(x) vs. VARCHAR2(x) in Oracle 10g and 9i - advantages and disadvantages
Date: Fri, 13 Jun 2008 07:05:59 -0700 (PDT)
Message-ID: <a712306e-849f-449b-875e-7fb3946d060b@k37g2000hsf.googlegroups.com>
On Jun 12, 10:49 pm, "gym dot scuba dot kennedy at gmail"
<kenned..._at_verizon.net> wrote:
> <dana..._at_yahoo.com> wrote in message
>
> news:0df6618f-5a82-44c8-9a2e-2e535c33a6b8_at_25g2000hsx.googlegroups.com...
>
>
>
> > 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