Re: DATA TYPES

From: Tim Smith <tssmith_at_netcom.com>
Date: Fri, 6 Jan 1995 02:00:36 GMT
Message-ID: <tssmithD1yo91.9o9_at_netcom.com>


rt_at_astro.ocis.temple.edu (rt_at_astro.ocis.temple.edu) writes:
>Which data type is most suitable for serial numbers that you expect to
>hit 100,000 and more in the course of at least 2 years. Integer is
>definately out for a column data type since it will max out and roll
>over at about 37600 something. You will get some interesting queries
>there! :-) Any suggestions?

If you are talking about Oracle, all numeric data are stored internally in the same BCD format. You don't save by using integer, since an integer column type is just NUMBER(n,0) (the exact value of n is immaterial, but the precision will well accomodate several orders of magnitude more than 100,000).

In any event, use NUMBER, and set the precision to however many digits you need. Looks like NUMBER(7,0) would work fine in your case.

To show this, do the following in SQL*Plus or SQL*DBA:

create table t1 (c1 integer, c2 number(4,0), c3 float, c4 number); insert into t1 values (123.4, 123.4, 123.4, 123.4);

and then:

select * from t1;

which gets you:

123 123 123.4 123.4

Of course this says nothing about the internal representation. So do:

select dump(c1), dump(c2), dump(c3), dump(c4) from t1;

to see the internal representation. You will see that the representation of all four is really the same, just that c1 and c2 have been truncated.

--Tim (tssmith_at_oracle.com) Received on Fri Jan 06 1995 - 03:00:36 CET

Original text of this message