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

On Jun 12, 10:49 pm, "gym dot scuba dot kennedy at gmail"
<kenned...@xxxxxxxxxxx> wrote:
<dana...@xxxxxxxxx> wrote in message

news:0df6618f-5a82-44c8-9a2e-2e535c33a6b8@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



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 --


.

Relevant Pages

  • Re: Trouble passing a numeric data type
    ... I did try using adDecimal but maybe it's the precision and scale ... >> numeric data type to my stored procedure, ...
    (microsoft.public.vb.database.ado)
  • Re: float storage
    ... information scale and precision I can't determine in advance (customer data ... can vary wildly) so I wanted to use the datatype that offers the maximum scale ... I used the oracle 9i docs to drive my decision. ...
    (comp.databases.oracle.misc)
  • Re: NUMBER(p) vs. INTEGER data type and CHAR(x) vs. VARCHAR2(x) in Oracle 10g and 9i - advantage
    ... scale) gives you an integer, constrained by a max of p digits. ... since 38 is the max precision? ... CHAR data type? ... There are numbers in the Oracle DB, the definitions of the others are types ...
    (comp.databases.oracle.server)
  • Re: syntax to add decimal column to access table using SQL?
    ... set the data type in SQL to numeric and the precision value to 10 and then ... the scale value to 3 ...
    (microsoft.public.access.gettingstarted)
  • Re: Regarding Numer(p,s)
    ... As per Oracle document in Number ... Number mean total number of digits is 7 and i can have 2 digits ... You can specify a scale that is greater than precision, ...
    (comp.databases.oracle.misc)